%macro sdtm_ae;

*---------------------------------------------------------------*;
* AE.sas creates the SDTM AE dataset and saves it
* as permanent SAS datasets to the target libref.
*---------------------------------------------------------------*;

%common 

**** CREATE EMPTY DM DATASET CALLED EMPTY_AE;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=AE)
 

**** DERIVE THE MAJORITY OF SDTM AE VARIABLES;
options missing = ' ';
data ae;
  set EMPTY_AE
  source.adverse(rename=(aerel=_aerel aesev=_aesev));

    studyid = 'XYZ123';
    domain = 'AE';
    usubjid = left(uniqueid);
    aeterm = left(aetext);
    aedecod = left(prefterm);
    aebodsys = left(bodysys);
    aesev = put(_aesev,aesev_adverse_aesev.);
    aeacn = put(aeaction,acn_adverse_aeaction.);
    aerel = put(_aerel,aerel_adverse_aerel.);
    aeser = put(serious,$ny_adverse_serious.);
    aestdtc = put(aestart,yymmdd10.);
    aeendtc = put(aeend,yymmdd10.);
run;

 
proc sort
  data=ae;
    by usubjid;
run;

**** CREATE SDTM STUDYDAY VARIABLES;
data ae;
  merge ae(in=inae) target.dm(keep=usubjid rfstdtc);
    by usubjid;

    if inae;

    %make_sdtm_dy(date=aestdtc); 
    %make_sdtm_dy(date=aeendtc); 
run;


**** CREATE SEQ VARIABLE;
proc sort
  data=ae;
    by studyid usubjid aedecod aestdtc aeendtc;
run;

data ae;
  retain &AEKEEPSTRING;
  set ae(drop=aeseq);
    by studyid usubjid aedecod aestdtc aeendtc;

    if not (first.aeendtc and last.aeendtc) then
      put "WARN" "ING: key variables do not define an unique record. " usubjid=;

    retain aeseq;
    if first.usubjid then
      aeseq = 1;
    else
      aeseq = aeseq + 1;
		
    label aeseq = "Sequence Number";
run;



**** SORT AE ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=AE)


proc sort
  data=ae(keep = &AEKEEPSTRING)
  out=target.ae;
    by &AESORTSTRING;
run;
%mend sdtm_ae;


%macro sdtm_dm;

*---------------------------------------------------------------*;
* DM.sas creates the SDTM DM and SUPPDM datasets and saves them
* as permanent SAS datasets to the target libref.
*---------------------------------------------------------------*;
 
%common 

**** CREATE EMPTY DM DATASET CALLED EMPTY_DM;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=DM)
 
**** GET FIRST AND LAST DOSE DATE FOR RFSTDTC AND RFENDTC;
proc sort
  data=source.dosing(keep=subject startdt enddt)
  out=dosing;
    by subject startdt;
run;

**** FIRSTDOSE=FIRST DOSING AND LASTDOSE=LAST DOSING;
data dosing;
  set dosing;
    by subject;

    retain firstdose lastdose;

    if first.subject then
      do;
        firstdose = .;
        lastdose = .;
      end;

    firstdose = min(firstdose,startdt,enddt);
    lastdose = max(lastdose,startdt,enddt);

    if last.subject;
run; 

**** GET DEMOGRAPHICS DATA;
proc sort
  data=source.demographic
  out=demographic;
    by subject;
run;

**** MERGE DEMOGRAPHICS AND FIRST DOSE DATE;
data demog_dose;
  merge demographic
        dosing;
    by subject;
run;

**** DERIVE THE MAJORITY OF SDTM DM VARIABLES;
options missing = ' ';
data dm;
  set EMPTY_DM
    demog_dose(rename=(race=_race));

    studyid = 'XYZ123';
    domain = 'DM';
    usubjid = left(uniqueid);
    subjid = put(subject,3.); 
    rfstdtc = put(firstdose,yymmdd10.);  
    rfendtc = put(lastdose,yymmdd10.); 
    siteid = substr(subjid,1,1) || "00";
    brthdtc = put(dob,yymmdd10.);
    age = floor ((intck('month',dob,firstdose) - 
          (day(firstdose) < day(dob))) / 12);
    if age ne . then
        ageu = 'YEARS';
    sex = put(gender,sex_demographic_gender.);
    race = put(_race,race_demographic_race.);
    armcd = put(trt,armcd_demographic_trt.);
    arm = put(trt,arm_demographic_trt.);
    country = "USA"; 
run; 

**** DEFINE SUPPDM FOR OTHER RACE page 59 IG; 
**** CREATE EMPTY SUPPDM DATASET CALLED EMPTY_DM;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=SUPPDM)

data suppdm;
  set EMPTY_SUPPDM
      dm; 

    keep &SUPPDMKEEPSTRING;

    **** OUTPUT OTHER RACE AS A SUPPDM VALUE;
    if orace ne '' then
      do;
        rdomain = 'DM';
        qnam = 'RACEOTH';
        qlabel = 'Race, Other';
        qval = left(orace);
        qorig = 'CRF';
        output;
      end;

    **** OUTPUT RANDOMIZATION DATE AS SUPPDM VALUE;
    if randdt ne . then
      do;
        rdomain = 'DM';
        qnam = 'RANDDTC';
        qlabel = 'Randomization Date';
        qval = left(put(randdt,yymmdd10.));
        qorig = 'CRF';
        output;
      end;
run;


**** SORT DM ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=DM)

proc sort
  data=dm(keep = &DMKEEPSTRING)
  out=target.dm;
    by &DMSORTSTRING;
run;


**** SORT SUPPDM ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=SUPPDM)

proc sort
  data=suppdm
  out=target.suppdm;
    by &SUPPDMSORTSTRING;
run;
%mend sdtm_dm;


%macro stdm_ex;
*---------------------------------------------------------------*;
* EX.sas creates the SDTM EX dataset and saves it
* as a permanent SAS datasets to the target libref.
*---------------------------------------------------------------*;
 
%common 

**** CREATE EMPTY EX DATASET CALLED EMPTY_EX;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=EX)
 

**** DERIVE THE MAJORITY OF SDTM EX VARIABLES;
options missing = ' ';
data ex;
  set EMPTY_EX
      source.dosing;

    studyid = 'XYZ123';
    domain = 'EX';
    usubjid = left(uniqueid);
    exdose = dailydose;
    exdostot = dailydose;
    exdosu = 'mg';
    exdosfrm = 'TABLET, COATED';
    %make_dtc_date(dtcdate=exstdtc, year=startyy, month=startmm, day=startdd)
    %make_dtc_date(dtcdate=exendtc, year=endyy, month=endmm, day=enddd)
run;
 
proc sort
  data=ex;
    by usubjid;
run;

**** CREATE SDTM STUDYDAY VARIABLES AND INSERT EXTRT;
data ex;
  merge ex(in=inex) target.dm(keep=usubjid rfstdtc arm);
    by usubjid;

    if inex;

    %make_sdtm_dy(date=exstdtc); 
    %make_sdtm_dy(date=exendtc); 

    **** in this simplistic case all subjects received the treatment they were randomized to;
    extrt = arm;
run;


**** CREATE SEQ VARIABLE;
proc sort
  data=ex;
    by studyid usubjid extrt exstdtc;
run;

data ex;
  retain &EXKEEPSTRING;
  set ex(drop=exseq);
    by studyid usubjid extrt exstdtc;

    if not (first.exstdtc and last.exstdtc) then
      put "WARN" "ING: key variables do not define an unique record. " usubjid=;

    retain exseq;
    if first.usubjid then
      exseq = 1;
    else
      exseq = exseq + 1;
		
    label exseq = "Sequence Number";
run;


**** SORT EX ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=EX)

proc sort
  data=ex(keep = &EXKEEPSTRING)
  out=target.ex;
    by &EXSORTSTRING;
run;
%mend stdm_ex;

%macro stdm_lb;
*---------------------------------------------------------------*;
* LB.sas creates the SDTM LB dataset and saves it
* as a permanent SAS datasets to the target libref.
*---------------------------------------------------------------*;
 
%common 

**** CREATE EMPTY DM DATASET CALLED EMPTY_LB;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=LB)
 

**** DERIVE THE MAJORITY OF SDTM LB VARIABLES;
options missing = ' ';
data lb;
  set EMPTY_LB
      source.labs; 

    studyid = 'XYZ123';
    domain = 'LB';
    usubjid = left(uniqueid);
    lbcat = put(labcat,$lbcat_labs_labcat.);
    lbtest = put(labtest,$lbtest_labs_labtest.);
    lbtestcd = put(labtest,$lbtestcd_labs_labtest.);
    lborres = left(put(nresult,best.));
    lborresu = left(colunits);
    lbornrlo = left(put(lownorm,best.));
    lbornrhi = left(put(highnorm,best.));

    **** create standardized results;
    lbstresc = lborres;
    lbstresn = nresult;
    lbstresu = lborresu;
    lbstnrlo = lownorm;
    lbstnrhi = highnorm;

    if lbstnrlo ne . and lbstresn ne . and 
       round(lbstresn,.0000001) < round(lbstnrlo,.0000001) then
      lbnrind = 'LOW';
    else if lbstnrhi ne . and lbstresn ne . and 
       round(lbstresn,.0000001) > round(lbstnrhi,.0000001) then
      lbnrind = 'HIGH';
    else if lbstnrhi ne . and lbstresn ne . then
      lbnrind = 'NORMAL';

    visitnum = month;
    visit = put(month,visit_labs_month.);
    if visit = 'Baseline' then
      lbblfl = 'Y';
	else
	  lbblfl = ' ';

    lbdtc = put(labdate,yymmdd10.); 
run;

 
proc sort
  data=lb;
    by usubjid;
run;

**** CREATE SDTM STUDYDAY VARIABLES;
data lb;
  merge lb(in=inlb) target.dm(keep=usubjid rfstdtc);
    by usubjid;

    if inlb;

    %make_sdtm_dy(date=lbdtc) 
run;


**** CREATE SEQ VARIABLE;
proc sort
  data=lb;
    by studyid usubjid lbcat lbtestcd visitnum;
run;

data lb;
  retain &LBKEEPSTRING;
  set lb(drop=lbseq);
    by studyid usubjid lbcat lbtestcd visitnum; 

    if not (first.visitnum and last.visitnum) then
      put "WARN" "ING: key variables do not define an unique record. " usubjid=;

    retain lbseq;
    if first.usubjid then
      lbseq = 1;
    else
      lbseq = lbseq + 1;
		
    label lbseq = "Sequence Number";
run;


**** SORT LB ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=LB)

proc sort
  data=lb(keep = &LBKEEPSTRING)
  out=target.lb;
    by &LBSORTSTRING;
run;
%mend stdm_lb;


%macro stdm_tdm;
*---------------------------------------------------------------*;
* TDM.sas creates the SDTM TA, TE, TI, TS, and TV datasets and 
* saves them as a permanent SAS datasets to the target libref.
*---------------------------------------------------------------*;

%common
 
**** CREATE EMPTY TA DATASET CALLED EMPTY_TA;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TA)

proc import 
  datafile="C:\path\trialdesign.xlsx"
  out=ta 
  dbms=excelcs
  replace;
  sheet='TA';
run;

**** SET EMPTY DOMAIN WITH ACTUAL DATA;
data ta;
  set EMPTY_TA
      ta;
run;

**** SORT DOMAIN ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TA)

proc sort
  data=ta(keep = &TAKEEPSTRING)
  out=target.ta;
    by &TASORTSTRING;
run;



**** CREATE EMPTY TE DATASET CALLED EMPTY_TE;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TE)

proc import 
  datafile="C:\path\trialdesign.xlsx"
  out=te
  dbms=excelcs
  replace;
  sheet='TE';
run;

**** SET EMPTY DOMAIN WITH ACTUAL DATA;
data te;
  set EMPTY_TE
      te;
run;

**** SORT DOMAIN ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TE)

proc sort
  data=te(keep = &TEKEEPSTRING)
  out=target.te;
    by &TESORTSTRING;
run;



**** CREATE EMPTY TI DATASET CALLED EMPTY_TI;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TI)

proc import 
  datafile="C:\path\trialdesign.xlsx"
  out=ti 
  dbms=excelcs
  replace;
  sheet='TI';
run;

**** SET EMPTY DOMAIN WITH ACTUAL DATA;
data ti;
  set EMPTY_TI
      ti;
run;

**** SORT DOMAIN ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TI)

proc sort
  data=ti(keep = &TIKEEPSTRING)
  out=target.ti;
    by &TISORTSTRING;
run;



**** CREATE EMPTY TS DATASET CALLED EMPTY_TS;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TS)

proc import 
  datafile="C:\path\trialdesign.xlsx"
  out=ts 
  dbms=excelcs
  replace;
  sheet='TS';
run;

**** SET EMPTY DOMAIN WITH ACTUAL DATA;
data ts;
  set EMPTY_TS
      ts;
run;

**** SORT DOMAIN ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TS)

proc sort
  data=ts(keep = &TSKEEPSTRING)
  out=target.ts;
    by &TSSORTSTRING;
run;



**** CREATE EMPTY TV DATASET CALLED EMPTY_TV;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TV)

proc import 
  datafile="C:\path\trialdesign.xlsx"
  out=tv 
  dbms=excelcs
  replace;
  sheet='TV';
run;

**** SET EMPTY DOMAIN WITH ACTUAL DATA;
data tv;
  set EMPTY_TV
      tv;
run;

**** SORT DOMAIN ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=TV)

proc sort
  data=tv(keep = &TVKEEPSTRING)
  out=target.tv;
    by &TVSORTSTRING;
run;
%mend sdtm_tdm;


%macro sdtm_xp;
*---------------------------------------------------------------*;
* XP.sas creates the SDTM XP dataset and saves it
* as a permanent SAS datasets to the target libref.
*---------------------------------------------------------------*;

%common 

**** CREATE EMPTY DM DATASET CALLED EMPTY_XP;
%make_empty_dataset(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=XP)
 
proc format;
  value pain
    0='None'
    1='Mild'
    2='Moderate'
    3='Severe';
run;

**** DERIVE THE MAJORITY OF SDTM XP VARIABLES;
options missing = ' ';
data xp;
  set EMPTY_XP
      source.pain; 

    studyid = 'XYZ123';
    domain = 'XP';
    usubjid = left(uniqueid);

    xptest = 'Pain Score';
    xptestcd = 'XPPAIN';

    **** transpose pain data;
    array dates {3} randomizedt month3dt month6dt;
    array scores {3} painbase pain3mo pain6mo;

    do i = 1 to 3;
      visitnum = i - 1;
      visit = put(visitnum,visit_labs_month.);
      if scores{i} ne . then
        do;
          xporres = left(put(scores{i},pain.));
          xpdtc = put(dates{i},yymmdd10.);
          output;
        end;
    end;
run;

 
proc sort
  data=xp;
    by usubjid;
run;

**** CREATE SDTM STUDYDAY VARIABLES;
data xp;
  merge xp(in=inxp) target.dm(keep=usubjid rfstdtc);
    by usubjid;

    if inxp;

    %make_sdtm_dy(date=xpdtc) 
run;


**** CREATE SEQ VARIABLE;
proc sort
  data=xp;
    by studyid usubjid xptestcd visitnum;
run;


data xp;
  retain &XPKEEPSTRING;
  set xp(drop=xpseq);
    by studyid usubjid xptestcd visitnum; 

    if not (first.visitnum and last.visitnum) then
      put "WARN" "ING: key variables do not define an unique record. " usubjid=;

    retain xpseq;
    if first.usubjid then
      xpseq = 1;
    else
      xpseq = xpseq + 1;
		
    label xpseq = "Sequence Number";
run;


**** SORT XP ACCORDING TO METADATA AND SAVE PERMANENT DATASET;
%make_sort_order(metadatafile=C:\path\SDTM_METADATA.xlsx,dataset=XP)


proc sort
  data=xp(keep = &XPKEEPSTRING)
  out=target.xp;
    by &XPSORTSTRING;
run;

%mend sdtm_xp;