Notes

%MACRO FA;

/*Clean up section*/
proc datasets lib=work kill nolist memtype = data;
quit;

dm 'clear log; clear output;';
dm 'odsresults; clear;';

OPTIONS NOFMTERR SYMBOLGEN MPRINT MLOGIC VALIDVARNAME=UPCASE;

%let program = &SYSMACRONAME;


Proc format;
value faorres
0 = "N"
1 = "Y";
run;


%macro sort(dsn = , by = );
proc sort data = &dsn ;
by &by;
run;
%mend sort;

/*Scheduled Visits Mapping*/
%macro visits;
length visit $60;
Select(strip(foldername));
when ('Screening') do; visit = 'Screening'; visitnum = 1; visitdy = -1; end;
when ('Day 1') do; visit = 'Day 1'; visitnum = 2; visitdy = 1; end;
when ('Day 2') do; visit = 'Day 2'; visitnum = 3; visitdy = 2; end;
when ('Day 3') do; visit = 'Day 3'; visitnum = 4; visitdy = 3; end;
when ('Day 4') do; visit = 'Day 4'; visitnum = 5; visitdy = 4; end;
when ('Day 5') do; visit = 'Day 5'; visitnum = 6; visitdy = 5; end;
when ('Day 6') do; visit = 'Day 6'; visitnum = 7; visitdy = 6; end;
when ('Day 7') do; visit = 'Day 7'; visitnum = 8; visitdy = 7; end;
when ('Day 10') do; visit = 'Day 10'; visitnum = 9; visitdy = 10; end;
when ('EOT') do; visit = 'EOT'; visitnum = 10; visitdy = .; end;
when ('PTE') do; visit = 'PTE'; visitnum = 11; visitdy = .; end;
when ('FUP') do; visit = 'Follow-up'; visitnum = 12; visitdy = .; end;
otherwise do; visitnum =.; end;
end;
%mend visits;

/** This is the macro to be used outside the data step **/
%MACRO UNSCH (INDST=,OUTDST=,DTC=); /*DTC = date variable in IS8601DT. format*/
/*STEP 1*/
data raw_unsch raw_sch ;/*Seperating scheduled and unscheduled visits in INPUT dataset*/
set &INDST;
_dtc = input(&dtc,??is8601da.);
if strip(visit) = '' then output raw_unsch;
else output raw_sch;
run;
/*STEP 2*/
proc sort data = raw_unsch out=vis_u ; /*Sorting Unscheduled visits if visit is missing*/
by usubjid _dtc;
where visit = '';
run;
/*STEP 3*/
data derv_unsch; /*Getting Unscheduled visits from libderv library*/
set sdtm.unscheduled;
_dtc = input(dttm,??is8601da.);
drop dttm;
run;

proc sort data = derv_unsch;
by usubjid _dtc;
run;
/*STEP 4*/
data vis_; /*Merging unscheduled visits from unscheduled visits form INPUT dataset and LIBDERV dataset*/
merge vis_u(in=a )
derv_unsch(in=b rename = (visit=visit1 visitnum=visitnum1));
by usubjid _dtc;
if a;
if strip(visit) = '' then do;
if visit = '' then VISIT = VISIT1;
if VISITNUM= . then VISITNUM = VISITNUM1;
end;
run;
/*STEP 5*/
proc sort data=vis_ out = vis_1;
by usubjid _dtc;
run;

proc sort data=raw_sch;
by usubjid _dtc;
run;
/*STEP 6*/
data &OUTDST; /*Concatenating scheduled visits from input dataset from step 1 and derived unscheduled visits from step 4*/
set raw_sch(in=a) vis_1(in=b);
by usubjid _dtc;
run;

%MEND UNSCH;

proc sort data = raw.cr out=cr;
by SUBJECT CRDAT CRDAT_C CRDTC CRTIM FOLDERNAME;
run;


Data cr_1;
LENGTH fatest FACAT faobj faorres FATESTCD $100;
set cr;

FACAT=DATAPAGENAME;
if crcat ne '' then do;
faorres=crcat;
fatestcd='TYPE';
fatest='Type';
faobj='ASSESSMENT TYPE';
output;
end;


if crorres1_cv in ('Y' 'N') then do; FATESTCD = 'OCCUR';
fatest='Occurrence';
faobj = 'INFILTRATE' ;
FAORRES = crorres1_cv;
output;end;

if crpaloc1 in (0 1) then do;
fatestcd='LOCATION';
fatest ='Location' ; end;

if crorres1_cv='Y' and fatestcd='LOCATION' then do;
FAGRPID="1";

if crpaloc1 = 1 then do;
FAORRES = 'RIGHT UPPER LOBE';
output;
end;
if crpaloc2 = 1 then do;FAORRES = 'RIGHT MIDDLE LOBE';
output;
end;
if crpaloc3 = 1 then do; FAORRES = 'RIGHT LOWER LOBE';
output;
end;
if crpaloc4 = 1 then do; FAORRES = 'LEFT UPPER LOBE';
output;
end;
if crpaloc5 = 1 then do;FAORRES = 'LINGULA';
output;
end;
if crpaloc6 = 1 then do; FAORRES = 'LEFT LOWER LOBE';
output;
end;
end;


if crorres2_cv in ('Y' 'N') then do; FATESTCD = 'OCCUR';
fatest='Occurrence';
faobj = 'PLEURAL EFFUSION' ;
FAORRES = crorres2_cv;
output;end;

if crpeloc1 in (0 1) then do;
fatestcd='LOCATION';
fatest ='Location' ; end;


if crorres2_cv='Y' and fatestcd ='LOCATION' then do;
FAGRPID="2";

if crpeloc1 = 1 then do;FAORRES = 'RIGHT LUNG';
output;
end;
if crpeloc2 = 1 then do;FAORRES = 'LEFT LUNG';
output;
end;
end;
RUN;

proc sort data=cr_1 nodupkey;
by _all_;
run;

data cr_2;
set cr_1;
length FADTC $20;
source="CR";

FASTRESC = strip(FAORRES);
if indexc(upcase(strip(FASTRESC)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+`-={}|[]:;<>?,/ ") = 0 then do;
FASTRESN = input(strip(FASTRESC),8.);
end;

if not missing(CRTIM) and length(strip(CRTIM)) = 4 then CRTIM = "0"||strip(CRTIM);
if not missing(CRDAT) and not missing(CRTIM) then FADTC = strip(put(datepart(CRDAT), ??yymmdd10.)) || 'T' ||compress(CRTIM||':00');
if missing(CRDAT) and not missing(CRTIM) then FADTC = '-----T' || compress(CRTIM||':00');
if not missing(CRDAT) and missing(CRTIM) then FADTC = strip(put(datepart(CRDAT), ??yymmdd10.));
IF MISSING(FADTC) and not missing(CRDTC) then FADTC=CRDTC;
run;

data ss;
set raw.ss;
drop RECORDID DATAPAGEID RECORDDATE INSTANCEREPEATNUMBER INSTANCEID;
run;
proc sort data=ss nodupkey out=ss1 dupout=dup;* Removed duplicates because of repeats in unsch records for the subject 372-5011, loged in DM log 01/20/2017 this will be removed once data issue is fixed;
by _all_;
run;

/*updated FAORRES/FASTRESC in "ss' dataset as per SPEC updates on 20-Jun-2016*/
data ss_1;
set ss1;
LENGTH fatest FACAT faobj faorres $200. FATESTCD $100;
facat=DATAPAGENAME;
if ssperf_cv='N' then do;
faorres ='';
fatestcd ='FAALL';
FASTAT='NOT DONE';
fatest =datapagename;
faobj =upcase(datapagename);
output;
end;

if ssperf_cv='Y' then do;
/* faorres =put(ssorres1_cv,1.0);*/
faorres =tranwrd(compress(strip((ssorres1)),collate(128,255)),"subjects","subject's");
fatestcd ='SCORE';
fatest ='Score';faobj='COUGH' ;
fastresn =ssorres1_cv;
output;

/* faorres =put(ssorres2_cv,1.0); */
faorres =tranwrd(compress(strip((ssorres3)),collate(128,255)),"subjects","subject's");
fatestcd ='SCORE';fatest='Score';
faobj ='PLEURITIC CHEST PAIN';
fastresn =ssorres3_cv;

output;
/* faorres =put(ssorres3_cv,1.0);*/
faorres =tranwrd(compress(strip((ssorres2)),collate(128,255)),"subjects","subject's");
fatestcd ='SCORE';fatest='Score';
faobj ='DYSPNEA';
fastresn =ssorres2_cv;
output;

/*faorres =put(ssorres4_cv,1.0); */
faorres =tranwrd(compress(strip((ssorres4)),collate(128,255)),"subjects","subject's");
fatestcd ='SCORE';fatest='Score';
faobj ='PHLEGM/SPUTUM PRODUCTION';
fastresn =ssorres4_cv;
output;
end;
run;

data ss_2;
set ss_1;
length FADTC $20;
FASTRESC = strip(FAORRES);
/*if indexc(upcase(strip(FASTRESC)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+`-={}|[]:;<>?,/ ") = 0 then do;*/
/**/
/*FASTRESN = input(strip(FASTRESC),8.);*/
/*end;*/

if not missing(SSTIM) and length(strip(SSTIM)) = 4 then SSTIM = "0"||strip(SSTIM);
if not missing(SSDAT) and not missing(SSTIM) then FADTC = strip(put(datepart(SSDAT), ??yymmdd10.)) || 'T' || compress(SSTIM||':00');
if missing(SSDAT) and not missing(SSTIM) then FADTC = '-----T' || compress(SSTIM||':00');
if not missing(SSDAT) and missing(SSTIM) then FADTC = strip(put(datepart(SSDAT), ??yymmdd10.));
IF MISSING(FADTC) and not missing(SSDTC) then FADTC=SSDTC;
RUN;

data ps_1;
set raw.ps;
LENGTH fatest FACAT fascat faobj faorres FATESTCD $100;

facat=DATAPAGENAME;

if not missing(psams_cv) then do ;
faorres =psams_cv ;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PRESENCE OF ALTERED MENTAL STATUS';
fascat ='PHYSICAL EXAM FINDINGS';
output;
end;
if not missing(pspulse_cv)then do;
faorres =pspulse_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PRESENCE OF PULSE > 125/MINUTE';
fascat ='PHYSICAL EXAM FINDINGS';
output;
end;
if not missing(psrr_cv)then do;
faorres =psrr_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PRESENCE OF RESPIRATORY RATE > 30/MINUTE';
fascat ='PHYSICAL EXAM FINDINGS';
output;
end;
if not missing(pssbp_cv)then do;
faorres =pssbp_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PRESENCE OF SYSTOLIC BLOOD PRESSURE < 90 MMHG';
fascat ='PHYSICAL EXAM FINDINGS';
output;
end;
if not missing(pstemp_cv)then do;
faorres =pstemp_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PRESENCE OF TEMPERATURE < 35°C (95°F) OR > 40°C (104°F)';
fascat ='PHYSICAL EXAM FINDINGS';
output;
end;
if not missing(psnd_cv)then do;
faorres =psnd_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='HISTORY OF NEOPLASTIC DISEASE';
fascat ='COMORBIDITY';
output;
end;
if not missing(pschf_cv)then do;
faorres =pschf_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='HISTORY OF CONGESTIVE HEART FAILURE';
fascat ='COMORBIDITY';
output;
end;
if not missing(pscd_cv)then do;
faorres =pscd_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='HISTORY OF CEREBROVASCULAR DISEASE';
fascat ='COMORBIDITY';
output;
end;
if not missing(psrd_cv)then do;
faorres =psrd_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='HISTORY OF RENAL DISEASE';
fascat ='COMORBIDITY';
output;
end;
if not missing(psld_cv)then do;
faorres =psld_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='HISTORY OF LIVER DISEASE';
fascat ='COMORBIDITY';
output;
end;
if not missing(psage) then do;
faorres =put(psage,3.0);
fatestcd='AGE';
fatest ='Age';
faobj ='AGE AT TIME OF CONSENT';
fascat ='DEMOGRAPHICS';
output;
end;
if not missing(pssex) then do;
faorres =pssex;
fatestcd='GENDER';
fatest ='Gender';
faobj ='GENDER';
fascat ='DEMOGRAPHICS';
output;
end;
if not missing(psnhome_cv)then do;
faorres =psnhome_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='IS THE SUBJECT A NURSING HOME RESIDENT';
fascat ='DEMOGRAPHICS';
output;
end;
if not missing(psaph_cv)then do;
faorres =psaph_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='ARTERIAL PH < 7.35 (FROM ABG)';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(psbun_cv)then do;
faorres =psbun_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='BLOOD UREA NITROGEN (BUN) > 30 MG/DL (9 MMOL/L)';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(psna_cv)then do;
faorres =psna_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='SODIUM < 130 MMOL/L';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(psglu_cv)then do;
faorres =psglu_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='GLUCOSE > 250 MG/DL (14 MMOL/L)';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(pshem_cv)then do;
faorres =pshem_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='HEMATOCRIT < 30 %';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(psppao2_cv)then do;
faorres =psppao2_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PARTIAL PRESSURE OF ARTERIAL OXYGEN < 60 MMHG (FROM ABG)';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(pspe_cv)then do;
faorres =pspe_cv;
fatestcd='OCCUR';
fatest ='Occurrence';
faobj ='PLEURAL EFFUSION';
fascat ='LAB AND RADIOGRAPHIC FINDINGS';
output;
end;
if not missing(sitepsc)then do;
faorres =put(sitepsc,4.0);
fatestcd='SCORE';
fatest ='Score';
faobj ='SITE PORT SCORE (DERIVED)';
fascat ='';
output;
end;
if not missing(iwrspsc)then do;
faorres =put(iwrspsc,4.0);
fatestcd='SCORE';
fatest ='Score';
faobj ='IWRS PORT SCORE (INTEGRATED FROM IWRS)';
fascat ='';
output;
end;
if not missing(psrc)then do;
faorres =psrc;
fatestcd='CLASS';
fatest ='Class';
faobj ='RISK CLASS (INTEGRATED FROM IWRS)';
fascat ='';
output;
end;
run;

Proc sort data = raw.visit out = vs(keep = SUBJECT INSTANCENAME VISDTC) ;
by SUBJECT INSTANCENAME;
where not missing(VISDTC);
run;
Proc sort data = PS_1;
by SUBJECT INSTANCENAME;
run;

Data PS_1;
merge PS_1(in=a) VS(in=b);
by SUBJECT INSTANCENAME;
if a;
run;

data ps_2(drop = VISDTC);
set ps_1;
FASTRESC = strip(FAORRES);
if indexc(upcase(strip(FASTRESC)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ~!@#$%^&*()_+`-={}|[]:;<>?,/ ") = 0 then do;

FASTRESN = input(strip(FASTRESC),8.);
end;
if not missing(VISDTC) then FADTC = strip(VISDTC);
RUN;


%sort(dsn = cr_2, by = SUBJECT FOLDERNAME);
%sort(dsn = ss_2, by = SUBJECT FOLDERNAME);
%sort(dsn = ps_2, by = SUBJECT FOLDERNAME);

data all;
Length FACAT FAOBJ FAORRES $200. FASTRESC $200. FATEST $200.;
set cr_2 ss_2 ps_2;
FAORRES=strip(FAORRES);
if FAORRES="." then faorres=" ";
if FASTRESC="." then FASTRESC=" ";
drop studyid;
run;

run;
Proc sort data = all;
by SUBJECT INSTANCENAME;
run;

Proc sort data = vs;
by SUBJECT INSTANCENAME VISDTC;
run;
data yy;
set vs;
by SUBJECT INSTANCENAME VISDTC;
if first.VISDTC ;
run;

Proc sort data = YY;
by SUBJECT INSTANCENAME;
run;

Data all(rename =(FADTC1 = FADTC) drop = visdtc);
merge all(in=a)YY (where = (VISDTC ne " ") in=b);
by SUBJECT INSTANCENAME;
if a;

if missing(FADTC) and not missing(VISDTC) then FADTC1 = strip(VISDTC);
else FADTC1 = FADTC;
Drop FADTC;
run;

proc sql;
create table loctest as
select a.*, b.VISDAT,visdtc
from all as a
left join
(select subject, visdat,VISDTC from raw.visit where FOLDERNAME = "Screening") as b
on a.subject=b.subject
order by a.subject;
quit;

proc sort data=loctest out=loc1;
by SUBJECT FOLDERNAME;
run;
data loctest;
length subjid $10;
set loc1;
subjid=strip(subject);

run;
proc sort data = SDTM.DM out = dm(keep = subjid usubjid rfstdtc rfxstdtc rfendtc rfxendtc);
by SUBJID;
run;


data fa_dm;
length USUBJID $50 date $10;
merge loctest(in = a) dm(in = b);
by SUBJID;
if a and b;
domain = 'FA';

if substr(fadtc,1,1) ne '-' and not missing(fadtc) and length(fadtc) >= 10 then do;
STDT = input(substr(FADTC,1,10), ??yymmdd10.);
date = strip(substr(FADTC,1,10));
end;
if not missing(RFXSTDTC) then RFXST = input(substr(RFXSTDTC,1,10), ??yymmdd10.);
if not missing(RFSTDTC) then RFST = input(substr(RFSTDTC,1,10), ??yymmdd10.);
if not missing(RFENDTC) then RFEN = input(RFENDTC, yymmdd10.);
if not missing(RFXENDTC) then RFXEN = input(substr(RFXENDTC,1,10), yymmdd10.);
if STDT ne . and RFXST ne . then FADY = (STDT - RFXST) + (STDT >= RFXST);
if STDT ne . and RFXST eq . and RFST ne . then FADY = (STDT - RFST) + (STDT >= RFST);

if RFXST = . then EPOCH = 'SCREENING';
if STDT ne . and RFXST ne . and STDT < RFXST then EPOCH='SCREENING';
if STDT ne . and RFXST ne . and RFXEN ne . and RFXST <= STDT <= RFXEN then EPOCH='TREATMENT';
if STDT ne . and RFXST ne . and RFXEN eq . and STDT >= RFXST then EPOCH='TREATMENT';
if STDT ne . and RFXEN ne . and STDT > RFXEN then EPOCH='FOLLOW-UP';

%visits;
run;

%UNSCH (INDST=fa_dm, OUTDST= visits, DTC=date);

data visits;
length faorresu fastresu $40.;
set visits;
if FAORRES=' ' then FASTAT='NOT DONE';
studyid="PTK0796CABP1200";
FACAT=upcase(FACAT);
FAORRESU=" ";
FASTRESU=FAORRESU;
if FATESTCD = "AGE" then do;
FAORRESU = "Years";
FASTRESU = "Years";
end;
if indexc(fadtc,'UN') > 10 then FADTC = strip(substr(fadtc,1,10));;
run;

proc sort data = visits out=visi1 nodupkey; *********removed duplicate to match the dataset---maitri 3mar2017;
/* by STUDYID USUBJID FAOBJ faorres FACAT FASCAT FATESTCD FATEST FADTC VISITNUM ;*/
/* by STUDYID USUBJID FACAT FASCAT FAOBJ FATESTCD FATEST FADTC VISITNUM;*/
by STUDYID USUBJID FACAT FASCAT FAOBJ FATESTCD FATEST FADTC VISITNUM FAORRES;


run;

data final;
/*(keep=studyid domain usubjid faseq FAGRPID facat fascat fatestcd fatest faorres faorresu FASTRESC FASTRESN FASTRESU faobj */
/*FASTAT VISITNUM VISIT VISITDY EPOCH FADTC FADY);*/
set visi1;
by STUDYID USUBJID FACAT FASCAT FAOBJ FATESTCD FATEST FADTC VISITNUM FAORRES ;
/*by STUDYID USUBJID FACAT FASCAT FAOBJ FATESTCD FATEST FADTC VISITNUM;*/
/*by STUDYID USUBJID FAOBJ FACAT FASCAT FATESTCD FATEST FADTC VISITNUM;*/

if first.usubjid then FASEQ = 1;
else FASEQ+1;
format _all_;
informat _all_;
run;


%Attrib(SHEET =FA, DATASET=final, OUTPUT=SDTM.FA);



dm log "log; file '&study_path&out&out_Logs&program &sysdate9..log' replace " log;

%MEND FA;