Hi All,
Is there a simple way (datastep or sql) to associate multiple observations in a dataset with specific time points to an earlier time point in a one-per-id dataset, as long as the observation falls inbetween time point of interest? an example may explain it better...
I have a dataset with visits dates; and I have treatments with treatment dates. I'd like to associate each trt to the visit earlier as long as the trt was given in the period between the two consecutive visits...
data visits;
input visit $ visitdt mmddyy8.;
format visitdt mmddyy8.;
cards;
1 11/12/12
2 03/02/13
3 04/15/13
4 06/16/13
;
run;
data trt;
input trt $ trtDt mmddyy8.;
format trtDt mmddyy8.;
cards;
A 11/20/12
A 11/29/12
B 12/02/12
A 03/01/13
B 04/15/13
A 04/16/13
B 06/17/13
A 08/15/13
;
run;
I'm interested to this structure...
Visit visitdt trt trtDt
1 11/12/12 A 11/20/12
1 11/12/12 A 11/29/12
1 11/12/12 B 12/02/12
1 11/12/12 A 03/01/13
2 03/02/13 .
3 04/15/13 B 04/15/13
3 04/15/13 A 04/16/13
4 06/16/13 B 06/17/13
4 06/16/13 A 08/15/13
Any help is appreciated... thanks!
There are probably numerous ways. Here is one:
data visits;
set visits;
set visits(firstobs=2 keep=visitdt rename=(visitdt=next_visitdt))
visits(firstobs=1 drop=_all_);
if missing(next_visitdt) then next_visitdt='31dec9999'd;
run;
proc sql;
create table want as
select *
from visits left join trt
on visitdt<=trtdt<next_visitdt
order by visitdt,trtdt
;
quit;
There are probably numerous ways. Here is one:
data visits;
set visits;
set visits(firstobs=2 keep=visitdt rename=(visitdt=next_visitdt))
visits(firstobs=1 drop=_all_);
if missing(next_visitdt) then next_visitdt='31dec9999'd;
run;
proc sql;
create table want as
select *
from visits left join trt
on visitdt<=trtdt<next_visitdt
order by visitdt,trtdt
;
quit;
Thanks very much!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.