Sometimes, we have to calculate most recent dosing date before start of a specified adverse event. This post will introduce how to fulfill the task by applying Do-While loop.
Sample data
First of all, let’s create sample test dataset AE and EX which will be used later. SAS dataset and code are presented as following. Let’s take subject 01-001 as an example, start date of AE is 22May2017. And this subject has already taken drug three times on 01May2017, 20May2017 and 10Jun2017, respectively. Just take a glance of the data intuitively, everyone can easily point out that the most recent dosing date before 22May2017 is 20May2017. But how can we get it calculated by SAS programming? Obviously, it is impossible to check every AE record and EX records manually.
Click here to hide/show code
data ae;
length subjid $6. aestdtc $9.;
input subjid $ aestdtc $ ;
datalines;
01-001 22May2017
01-002 23Mar2017
01-003 10Apr2017
;
run;
data ex;
length subjid $6. exstdtc $9.;
input subjid $ exstdtc $;
datalines;
01-001 01May2017
01-001 20May2017
01-001 10Jun2017
01-002 23Feb2017
01-002 15Mar2017
01-002 05Apr2017
01-003 25Feb2017
01-003 19Mar2017
01-003 09Apr2017
;
run;
data ae;
format astdt date9.;
set ae;
astdt = input(aestdtc,date9.);
run;
data ex;
format exstdt date9.;
set ex;
exstdt = input(exstdtc,date9.);
run;
Transpose EX dataset from vertical to horizontal structure
A new variable n was created and will be used as ID variable in PROC TRANSPOSE. Macro variable loop and loopd was created and will be used later.
Click here to hide/show code
proc sort data=ex; by subjid exstdt; run;
data ex;
set ex;
by subjid exstdt;
if first.subjid then n = 0;
n + 1;
run;
proc transpose data=ex out=ex1(drop=_name_) prefix=exdt;
by subjid;
id n;
var exstdt;
run;
*Create macro variable for array list;
ods exclude all;
ods output Variables = var(where=(num ne 1 ));
proc contents data = ex1;
run;
ods output close;
proc sql;
select variable into: loop separated by ‘ ‘ from var;
select “b.”||strip(variable) into: loopd separated by ‘, ‘ from var;
quit;
ods exclude none;
Merge AE and transposed EX data
Click here to hide/show code
proc sql;
create table ae_ex as select a.subjid, a.astdt, &loopd
from ae as a left join ex1 as b
on a.subjid = b.subjid;
quit;
Calculate Most Recent Dosing Date before Start date of an Adverse Event
Calculated latest dosing date before start of AE were stored in variable result_dt. And you can use this simple approach to solve similar question like latest dosing date before start of a Concomitant medication.
Here are the full code.
Click here to hide/show code
data ae;
length subjid $6. aestdtc $9.;
input subjid $ aestdtc $ ;
datalines;
01-001 22May2017
01-002 23Mar2017
01-003 10Apr2017
;
run;
data ex;
length subjid $6. exstdtc $9.;
input subjid $ exstdtc $;
datalines;
01-001 01May2017
01-001 20May2017
01-001 10Jun2017
01-002 23Feb2017
01-002 15Mar2017
01-002 05Apr2017
01-003 25Feb2017
01-003 19Mar2017
01-003 09Apr2017
;
run;
data ae;
format astdt date9.;
set ae;
astdt = input(aestdtc,date9.);
run;
data ex;
format exstdt date9.;
set ex;
exstdt = input(exstdtc,date9.);
run;
proc sort data=ex; by subjid exstdt; run;
data ex;
set ex;
by subjid exstdt;
if first.subjid then n = 0;
n + 1;
run;
proc transpose data=ex out=ex1(drop=_name_) prefix=exdt;
by subjid;
id n;
var exstdt;
run;
*Create macro variable for array list;
ods exclude all;
ods output Variables = var(where=(num ne 1 ));
proc contents data = ex1;
run;
ods output close;
proc sql;
select variable into: loop separated by ‘ ‘ from var;
select “b.”||strip(variable) into: loopd separated by ‘, ‘ from var;
quit;
ods exclude none;
%put &loop;
proc sql;
create table ae_ex as select a.subjid, a.astdt, &loopd
from ae as a left join ex1 as b
on a.subjid = b.subjid;
quit;
data ae_ex;
format result_dt date9.;
set ae_ex;
array exst(*) &loop;
i = 1;
do while (. < exst(i) <= astdt);
result_dt=exst(i);
i + 1;
if i > dim(exst) then leave;
end;
run;
