Hi everybody,
I have dataset have below with participants who were given three meds: drug A, drug B and drug C.
I want to get dataset want below, where if the participant was given either drug on a previous date,
DRUGA (or DRUGB or DRUGC) then DRUGA (or DRUGB or DRUGC) turns from . to 2.
If no drug was used on that given date or a prior date then DRUGA (or DRUGB or DRUGC)=0. Otherwise, if drug was used on given date but not on any prior date, then DRUGA(or DRUGB or DRUGC)=1.
I have figured out a way to do, with code below but I want to know if there's a shorter,
more efficient/elegant way of programming this.
Here are my have and want datasets, as well as the code I've come up with.
Thanks in advance for any suggestions!
data have;
length patid $25;
input patid $ event $ date:mmddyy. diff med DRUGA DRUGB DRUGC;
format date mmddyy.;
datalines;
P001 2 06/07/2017 1 1 . 1 .
P001 1 06/10/2017 . . . . .
P001 1 06/12/2017 . . . . .
P001 1 06/13/2017 . . . . .
P001 1 06/20/2017 . 1 . 1 .
P001 0 06/23/2017 . 1 . . 1
P001 0 06/24/2017 . 1 . . 1
P001 0 06/25/2017 . . . . .
P002 1 07/02/2019 . . . . .
P002 2 07/03/2019 1 1 . 1 .
P002 1 07/06/2019 . 1 1 . .
P002 1 07/10/2019 . . . . .
;
run;
data want;
length patid $25;
input patid $ event $ date:mmddyy. DRUGA DRUGB DRUGC;
format date mmddyy.;
datalines;
P001 1 06/10/2017 0 2 0
P001 1 06/12/2017 0 2 0
P001 1 06/13/2017 0 2 0
P001 1 06/20/2017 0 1 0
P001 0 06/23/2017 0 2 1
P001 0 06/24/2017 0 2 1
P001 0 06/25/2017 0 2 2
P002 1 07/02/2019 0 0 0
P002 1 07/06/2019 1 2 0
P002 1 07/10/2019 2 2 0
;
run;
data want;
set have;
by patid date;
DRUGA_orig=DRUGA;
DRUGB_orig=DRUGB;
DRUGC_orig=DRUGC;
retain DRUGA_imp DRUGB_imp DRUGC_imp;
if first.patid then do;
DRUGA=DRUGA_orig; DRUGA_imp=DRUGA_orig;
DRUGB=DRUGB_orig; DRUGB_imp=DRUGB_orig;
DRUGC=DRUGC_orig; DRUGC_imp=DRUGC_orig;
end;
array x1 DRUGA DRUGB DRUGC;
array x2 DRUGA_imp DRUGB_imp DRUGC_imp;
do i=1 to dim(x1);
if x1[i]=. then x1[i]=x2[i];
else x2[i]=x1[i];
end;
drop i;
*recode med indicator from 1 to 2 when drug taken on prior date;
*recode med indicator from . to 0 when no drug taken prior to, or on the same date;
array xdo1 DRUGA_orig DRUGB_orig DRUGC_orig;
array xdo2 DRUGA DRUGB DRUGC;
do j=1 to dim(xdo1);
if xdo2[j]=. then xdo2[j]=0;
if xdo2[j]=1 & xdo1[j]=. then xdo2[j]=xdo2[j]+1;
end;
drop j;
if diff=1 then delete; *delete EVID=2/diff=1 records;
run;
A few questions :
1) Can a patient receive more than 1 drug in 1 obs ?
2) Do you want to delete all obs from have where diff = 1?
1) Yes, a participant can receive more than 1 drug in 1 obs
2) Yes, the final dataset should only contain patid, evid, date and the drug indicators.
Thank you for your follow-up!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.