I have a dataset like this:
PatientID | DayDrugTaken |
1 | 1 |
1 | 2 |
1 | 3 |
2 | 1 |
2 | 3 |
2 | 4 |
I would like to know how many continuous days in a row each patient took a drug. I used the following code
data new;
set old;
retain firstday;
by PatientId;
if first.PatientId then firstday= DayDrugTaken;
if last.PatientId then lastday= DayDrugTaken;
duration=(lastday-firstday)+1;
run;
which created a table like this:
PatientID | DayDrugTaken | FirstDay | LastDay | Duration |
1 | 1 | 1 | . | . |
1 | 2 | 1 | . | . |
1 | 3 | 1 | 3 | 3 |
2 | 1 | 1 | ||
2 | 3 | 1 | ||
2 | 4 | 1 | 4 | 4 |
For patient 1, I have what I want in the “Duration” variable, 3 days. But for patient 2, I would like to have the Duration=2 because he only took the drug on days 3 and 4 continuously. Does anyone have any advice about how to calculate this in SAS?
Thank you so much in advance.
data want;
set have;
by PatientID DayDrugTaken notsorted;
dif=DayDrugTaken-lag(DayDrugTaken);
if first.PatientID or dif>1 then grp+1;
run;
proc sql;
create table final as
select PatientID, max(day) as continuous_days from (
select PatientID,grp,count(DayDrugTaken) as day
from want
group by PatientID,grp)
group by PatientID;
quit;
The DIF() function is useful here
What do you want for output if daydrugtake has values like: 1, 3,4, 6,7,8, 10,11
In that case, I would like the duration=3 because the patient took drugs continuously for 3 days (6, 7, and 😎
Thanks.
data want;
set have;
by PatientID DayDrugTaken notsorted;
dif=DayDrugTaken-lag(DayDrugTaken);
if first.PatientID or dif>1 then grp+1;
run;
proc sql;
create table final as
select PatientID, max(day) as continuous_days from (
select PatientID,grp,count(DayDrugTaken) as day
from want
group by PatientID,grp)
group by PatientID;
quit;
Worked perfectly, I can't thank you enough!
Seems to me there is an one-step solution:
data want;
set have;
by PatientID DayDrugTaken ;
retain max_ct; | ct+(dif(DayDrugTaken)=1); |
if first.PatientID or
dif(DayDrugTaken)>1 then ct=1;
max_ct=max(max_ct,ct);
if last.patientid then do; output;max_ct=1;end;
drop ct;
run;
The approach can be reduced because the output is at ID level- then the DOW loop is effective
DATA persistency ;
KEEP patientID persistence ;
DO pd= 1 BY 1 UNTIL( last.patientID ) ;
SET old ;
BY patientID ;
difd = DIF( dayDrugTaken) ;
IF pd EQ 1 OR difd NE 1 THEN ct= 1 ;
ELSE ct=ct+1 ;
persistence = MAX( persistence, ct );
END;
RUN ;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.