I have a dataset in long form that is mutliple rows per id, and each row is a prescription. It is possible to switch back and forth between medications. I would like to create a wide dataset with date intervals that indicate the duration that each person has a prescription for the drug in the order that they occur.
data have;
id | date | med |
1 | 4/30/2015 | 2 |
1 | 5/6/2015 | 2 |
1 | 8/31/2015 | 1 |
1 | 11/30/2015 | 1 |
1 | 3/8/2016 | 2 |
1 | 6/7/2016 | 2 |
1 | 9/20/2016 | 2 |
2 | 3/26/2015 | 2 |
2 | 8/25/2015 | 2 |
2 | 11/24/2015 | 3 |
2 | 2/23/2016 | 3 |
2 | 10/3/2016 | 2 |
3 | 4/7/2016 | 1 |
3 | 5/19/2016 | 1 |
data want
id | date_start | date_end | med |
1 | 4/30/2015 | 5/6/2015 | 2 |
1 | 8/31/2015 | 11/30/2015 | 1 |
1 | 3/8/2016 | 9/20/2016 | 2 |
2 | 3/26/2015 | 8/25/2015 | 2 |
2 | 11/24/2015 | 2/23/2016 | 3 |
2 | 10/3/2016 | 10/3/2016 | 2 |
3 | 4/7/2016 | 5/19/2016 | 1 |
This is a straightforward SET with a BY statement task:
data have;
input id date :mmddyy10. med ;
format date date9.;
datalines;
1 4/30/2015 2
1 5/6/2015 2
1 8/31/2015 1
1 11/30/2015 1
1 3/8/2016 2
1 6/7/2016 2
1 9/20/2016 2
2 3/26/2015 2
2 8/25/2015 2
2 11/24/2015 3
2 2/23/2016 3
2 10/3/2016 2
3 4/7/2016 1
3 5/19/2016 1
run;
data want (keep=id med startdate enddate);
set have ;
by id med notsorted;
if first.med then startdate=date;
retain startdate;
if last.med;
enddate=date;
format startdate enddate date9.;
run;
Note I use the NOTSORTED option in the BY statement. This tells SAS that medication records are physically grouped, but not necessarily in ascending or descending order.
data have;
input id date :mmddyy10. med ;
format date mmddyy10.;
datalines;
1 4/30/2015 2
1 5/6/2015 2
1 8/31/2015 1
1 11/30/2015 1
1 3/8/2016 2
1 6/7/2016 2
1 9/20/2016 2
2 3/26/2015 2
2 8/25/2015 2
2 11/24/2015 3
2 2/23/2016 3
2 10/3/2016 2
3 4/7/2016 1
3 5/19/2016 1
;
data temp;
set have;
by id med notsorted;
if first.med or last.med;
run;
proc transpose data=temp out=_temp(drop=_name_ rename=(col1=startdate col2=enddate));
by id med notsorted;
var date;
run;
data want;
set _temp;
if missing(enddate) then enddate=startdate;
run;
data have;
input id date :mmddyy10. med ;
format date mmddyy10.;
datalines;
1 4/30/2015 2
1 5/6/2015 2
1 8/31/2015 1
1 11/30/2015 1
1 3/8/2016 2
1 6/7/2016 2
1 9/20/2016 2
2 3/26/2015 2
2 8/25/2015 2
2 11/24/2015 3
2 2/23/2016 3
2 10/3/2016 2
3 4/7/2016 1
3 5/19/2016 1
;
data temp;
set have;
by id med notsorted;
if first.med then group+1;
run;
proc sql;
select distinct id,min(date) as start format=mmddyy10.,
max(date) as end format=mmddyy10., med
from temp
group by group;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.