topic Re: time varying exposure with unevenly spaced time intervals in SAS Data Management

time varying exposure with unevenly spaced time intervals

psh23 — Thu, 01 Mar 2018 02:02:31 GMT

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

Re: time varying exposure with unevenly spaced time intervals

mkeintz — Thu, 01 Mar 2018 03:34:46 GMT

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.

Re: time varying exposure with unevenly spaced time intervals

novinosrin — Thu, 01 Mar 2018 03:37:28 GMT

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;

Re: time varying exposure with unevenly spaced time intervals

Ksharp — Thu, 01 Mar 2018 12:31:59 GMT

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;