BookmarkSubscribeRSS Feed
aishajennifer1
Calcite | Level 5

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

 

IDmethodstart_date_timestop_date_timestatic_date_time
111/1/19 11:13 AM 1/11/2019 15:12
121/1/19 12:15 PM 1/11/2019 15:12
111/1/19 4:11 PM 1/11/2019 15:12
111/2/19 6:11 PM 1/11/2019 15:12
121/2/19 8:33 PM 1/11/2019 15:12
111/3/19 12:12 PM1/4/2019  21:12:00 PM1/11/2019 15:12
211/1/19 11:13 AM 1/11/2019 15:12
221/1/19 12:15 PM 1/11/2019 15:12
211/1/19 4:11 PM 1/11/2019 15:12
211/2/19 6:11 PM 1/11/2019 15:12
221/2/19 8:33 PM 1/11/2019 15:12
211/3/19 12:12 PM 1/11/2019 15:12

 

New dataset

 

IDmethodstart_date_timestop_date_timestatic_date_timenew_stop_date
111/1/19 11:13 AM 1/11/2019 15:12 
121/1/19 12:15 PM 1/11/2019 15:121/2/19 8:33 PM
111/1/19 4:11 PM 1/11/2019 15:12 
111/2/19 6:11 PM 1/11/2019 15:12 
121/2/19 8:33 PM 1/11/2019 15:121/4/2019  21:12:00 PM
111/3/19 12:12 PM1/4/2019  21:12:00 PM1/11/2019 15:12 
211/1/19 11:13 AM 1/11/2019 15:12 
221/1/19 12:15 PM 1/11/2019 15:121/2/19 8:33 PM
211/1/19 4:11 PM 1/11/2019 15:12 
211/2/19 6:11 PM 1/11/2019 15:12 
221/2/19 8:33 PM 1/11/2019 15:121/11/2019 15:12
211/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!!

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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 . .

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 361 views
  • 0 likes
  • 3 in conversation