topic Dataset - by group and times in SAS Programming

Dataset - by group and times

aishajennifer1 — Thu, 26 Dec 2019 13:46:05 GMT

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

Re: Dataset - by group and times

Rick_SAS — Thu, 26 Dec 2019 14:32:13 GMT

Do you know how to use the FIRST.Variable indicator variable in the DATA step to perform a BY-group analysis?

Re: Dataset - by group and times

novinosrin — Thu, 26 Dec 2019 18:17:12 GMT


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