Hi
I am junior with SAS and I would love your help.
I am dealing with a patient treatment dataset. Patients (patient) are receiving treatment (antibiotic) with a start date (start) and end dates (end) for each treatment. There are some gaps between the start and end dates but where such an interruption occurs and a patent re-starts the same antibiotic that has stopped within 5 days, this interruption should be treated as if the first treatment was continuous.
Eg have;
Patient |
Antibiotic |
start |
End |
1 |
cipro |
16APR2018 |
21APR2018 |
1 |
cipro |
23APR2018 |
29APR2018 |
2 |
Cipro,flagyl |
16APR2018 |
16APR2018 |
2 |
Cipro,flagyl |
23APR2018 |
29APR2018 |
2 |
cipro |
30JUN2018 |
29JUL2018 |
Want
Patient |
Antibiotic |
start |
End |
1 |
cipro |
16APR2018 |
29APR2018 |
2 |
Cipro,flagyl |
16APR2018 |
29APR2018 |
2 |
cipro |
30JUN2018 |
29JUL2018 |
Thanks a lot
Dathan
data have;
length antibiotic $200;
input patient antibiotic $ start:date9. end:date9.;
format start end date9.;
cards;
1 cipro 16APR2018 21APR2018
1 cipro 23APR2018 29APR2018
2 Cipro+flagyl 16APR2018 19APR2018
2 Cipro+flagyl 23APR2018 29APR2018
2 cipro 30JUN2018 29JUL2018
;
run;
data temp;
set have;
do date=start to end;
output;
end;
format date date9.;
drop start end;
run;
proc sort data=temp nodupkey;by patient antibiotic date;run;
data temp1;
set temp;
by patient antibiotic;
if first.antibiotic or dif(date)>5 then group+1;
run;
proc summary data=temp1 ;
by group patient antibiotic;
var date;
output out=want min=start max=end;
run;
Please post the data in usable form. Similar questions are quite often asked and answered, it may be faster to use one of the existing solution and adept it to your needs, than waiting for a tailor-made solution.
Thanks Andreas_lds for offering to help;
Below is the data in usable form ;
data have;
length antibiotic $200;
input patient antibiotic $ start:date9. end:date9.;
format start end date9.;
cards;
1 cipro 16APR2018 21APR2018
1 cipro 23APR2018 29APR2018
2 Cipro+flagyl 16APR2018 16APR2018
2 Cipro+flagyl 23APR2018 29APR2018
2 cipro 30JUN2018 29JUL2018
;
run;
data want;
length antibiotic $200;
input patient antibiotic $ start:date9. end:date9.;
format start end date9.;
cards;
1 cipro 16APR2018 29APR2018
2 Cipro+flagyl 16APR2018 29APR2018
2 cipro 30JUN2018 29JUL2018
;
run;
Why are 3rd and 4th obs combined? Between April, 16th and April, 23rd are more than five days, or have i misinterpreted your logic?
Oops my mistake! I have twicked the data "have".
data have;
length antibiotic $200;
input patient antibiotic $ start:date9. end:date9.;
format start end date9.;
cards;
1 cipro 16APR2018 21APR2018
1 cipro 23APR2018 29APR2018
2 Cipro+flagyl 16APR2018 19APR2018
2 Cipro+flagyl 23APR2018 29APR2018
2 cipro 30JUN2018 29JUL2018
;
run;
data want;
length antibiotic $200;
input patient antibiotic $ start:date9. end:date9.;
format start end date9.;
cards;
1 cipro 16APR2018 29APR2018
2 Cipro+flagyl 16APR2018 29APR2018
2 cipro 30JUN2018 29JUL2018
;
run;
This is not the most beautiful or elegant solution:
proc sort data=work.have out=work.sorted;
by Patient Antibiotic Start;
run;
data work.want;
set work.sorted;
by Patient Antibiotic;
retain lastStart lastEnd;
if first.Antibiotic then do;
call missing(lastStart, lastEnd);
end;
else do;
if lastStart <= start <= (lastEnd + 5) then do;
lastEnd = end;
end;
else do;
savedStart = start;
savedEnd = end;
start = lastStart;
end = lastEnd;
output;
start = savedStart;
end = lastEnd;
end;
end;
lastStart = Start;
lastEnd = End;
if last.Antibiotic then do;
output;
end;
/*format last: saved: date9.;*/
drop last: saved:;
run;
data have;
length antibiotic $200;
input patient antibiotic $ start:date9. end:date9.;
format start end date9.;
cards;
1 cipro 16APR2018 21APR2018
1 cipro 23APR2018 29APR2018
2 Cipro+flagyl 16APR2018 19APR2018
2 Cipro+flagyl 23APR2018 29APR2018
2 cipro 30JUN2018 29JUL2018
;
run;
data temp;
set have;
do date=start to end;
output;
end;
format date date9.;
drop start end;
run;
proc sort data=temp nodupkey;by patient antibiotic date;run;
data temp1;
set temp;
by patient antibiotic;
if first.antibiotic or dif(date)>5 then group+1;
run;
proc summary data=temp1 ;
by group patient antibiotic;
var date;
output out=want min=start max=end;
run;
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.