SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

time varying exposure with unevenly spaced time intervals

Reply
Contributor
Posts: 29

time varying exposure with unevenly spaced time intervals

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; 

iddatemed
14/30/20152
15/6/20152
18/31/20151
111/30/20151
13/8/20162
16/7/20162
19/20/20162
23/26/20152
28/25/20152
211/24/20153
22/23/20163
210/3/20162
34/7/20161
35/19/20161

 

data want

iddate_startdate_endmed
14/30/20155/6/20152
18/31/201511/30/20151
13/8/20169/20/20162
23/26/20158/25/20152
211/24/20152/23/20163
210/3/201610/3/20162
34/7/20165/19/20161
Highlighted
Trusted Advisor
Posts: 1,337

Re: time varying exposure with unevenly spaced time intervals

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.

PROC Star
Posts: 1,784

Re: time varying exposure with unevenly spaced time intervals

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;
Super User
Posts: 10,770

Re: time varying exposure with unevenly spaced time intervals

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;
Ask a Question
Discussion stats
  • 3 replies
  • 267 views
  • 3 likes
  • 4 in conversation