Hello. I have complex dataset that I am unsure on how to begin.
For example - there are multiple rows per subject stratified by "method" 1 or 2.
- If a subject has a "method=2" then I would like to take the next record of method = 2 instance for the same subject start date and make that as an end date, and repeat. If there is no more method =2 & (stop date IS available for method =1, i would like to make that as new_stop_date time OR if stop_date is NOT available for last method = 1 then I would like to assign static_date_time = new_stop_date_time.
Sample dataset
ID | method | start_date_time | stop_date_time | static_date_time |
1 | 1 | 1/1/19 11:13 AM | 1/11/2019 15:12 | |
1 | 2 | 1/1/19 12:15 PM | 1/11/2019 15:12 | |
1 | 1 | 1/1/19 4:11 PM | 1/11/2019 15:12 | |
1 | 1 | 1/2/19 6:11 PM | 1/11/2019 15:12 | |
1 | 2 | 1/2/19 8:33 PM | 1/11/2019 15:12 | |
1 | 1 | 1/3/19 12:12 PM | 1/4/2019 21:12:00 PM | 1/11/2019 15:12 |
2 | 1 | 1/1/19 11:13 AM | 1/11/2019 15:12 | |
2 | 2 | 1/1/19 12:15 PM | 1/11/2019 15:12 | |
2 | 1 | 1/1/19 4:11 PM | 1/11/2019 15:12 | |
2 | 1 | 1/2/19 6:11 PM | 1/11/2019 15:12 | |
2 | 2 | 1/2/19 8:33 PM | 1/11/2019 15:12 | |
2 | 1 | 1/3/19 12:12 PM | 1/11/2019 15:12 |
New dataset
ID | method | start_date_time | stop_date_time | static_date_time | new_stop_date |
1 | 1 | 1/1/19 11:13 AM | 1/11/2019 15:12 | ||
1 | 2 | 1/1/19 12:15 PM | 1/11/2019 15:12 | 1/2/19 8:33 PM | |
1 | 1 | 1/1/19 4:11 PM | 1/11/2019 15:12 | ||
1 | 1 | 1/2/19 6:11 PM | 1/11/2019 15:12 | ||
1 | 2 | 1/2/19 8:33 PM | 1/11/2019 15:12 | 1/4/2019 21:12:00 PM | |
1 | 1 | 1/3/19 12:12 PM | 1/4/2019 21:12:00 PM | 1/11/2019 15:12 | |
2 | 1 | 1/1/19 11:13 AM | 1/11/2019 15:12 | ||
2 | 2 | 1/1/19 12:15 PM | 1/11/2019 15:12 | 1/2/19 8:33 PM | |
2 | 1 | 1/1/19 4:11 PM | 1/11/2019 15:12 | ||
2 | 1 | 1/2/19 6:11 PM | 1/11/2019 15:12 | ||
2 | 2 | 1/2/19 8:33 PM | 1/11/2019 15:12 | 1/11/2019 15:12 | |
2 | 1 | 1/3/19 12:12 PM | 1/11/2019 15:12 |
I tried to use proc sql max stop date time per patient to join but I think I am doing it completely wrong
Thanks so much for your help in advance!!
data want;
if _n_=1 then do;
dcl hash H () ;
h.definekey ("start_date_time") ;
h.definedata ("new_stop_date") ;
h.definedone () ;
end;
do _n_=1 by 1 until(last.id);
set have ;
by id;
if stop_date_time then _s=stop_date_time;
if method=2 then do;
_iorc_+1;
_k=lag(start_date_time);
if _iorc_>1 then h.add(key:_k,data:start_date_time);
end;
end;
do _n_=1 to _n_;
set have;
if method ne 2 then call missing(new_stop_date);
else if h.find() ne 0 then new_stop_date=ifn(_s,_s,static_date_time);
output;
end;
call missing(_iorc_);
h.clear();
format new_stop_date datetime20.;
drop _:;
run;
proc print noobs;run;
ID | method | start_date_time | static_date_time | stop_date_time | new_stop_date |
---|---|---|---|---|---|
1 | 1 | 01JAN19:11:13:00 | 11JAN19:15:12:00 | . | . |
1 | 2 | 01JAN19:12:15:00 | 11JAN19:15:12:00 | . | 02JAN2019:20:33:00 |
1 | 1 | 01JAN19:16:11:00 | 11JAN19:15:12:00 | . | . |
1 | 1 | 02JAN19:18:11:00 | 11JAN19:15:12:00 | . | . |
1 | 2 | 02JAN19:20:33:00 | 11JAN19:15:12:00 | . | 04JAN2019:21:12:00 |
1 | 1 | 03JAN19:12:12:00 | 11JAN19:15:12:00 | 04JAN2019:21:12:00 | . |
2 | 1 | 01JAN19:11:13:00 | 11JAN19:15:12:00 | . | . |
2 | 2 | 01JAN19:12:15:00 | 11JAN19:15:12:00 | . | 02JAN2019:20:33:00 |
2 | 1 | 01JAN19:16:11:00 | 11JAN19:15:12:00 | . | . |
2 | 1 | 02JAN19:18:11:00 | 11JAN19:15:12:00 | . | . |
2 | 2 | 02JAN19:20:33:00 | 11JAN19:15:12:00 | . | 11JAN2019:15:12:00 |
2 | 1 | 03JAN19:12:12:00 | 11JAN19:15:12:00 | . | . |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: