topic Re: How to program my question in SAS in SAS Procedures

How to program my question in SAS

LauraZhu — Thu, 26 Mar 2015 20:34:46 GMT

Hello all,

I illustrated my question in the following example. Could you please help me to generate the desired output in SAS ? Basically, we would like to expand the service period and ignore any gaps within 30 days.

ID FROM_DT THRU_DT

10 02/03/2014 02/25/2014

10 03/01/2014 04/02/2014

10 04/03/2014 05/05/2014

10 07/07/2014 07/30/2014

10 08/20/2014 08/25/2014

11 03/02/2013 05/03/2013

11 05/08/2013 05/20/2013

Desired output should look like this:

ID FROM_DT THRU_DT

10 02/03/2014 05/05/2014

10 07/07/2014 08/25/2014

11 03/02/2013 05/20/2013

Thank you very much!

Re: How to program my question in SAS

Patrick — Thu, 26 Mar 2015 21:38:25 GMT

What have you done so far? Which bit can't you solve? Post the code you already have even if it isn't fully working yet.

Re: How to program my question in SAS

Reeza — Fri, 27 Mar 2015 00:22:56 GMT

Look at the lag function

Re: How to program my question in SAS

Jagadishkatam — Fri, 27 Mar 2015 04:11:15 GMT

Please try

data have;

input ID FROM_DT :mmddyy10. THRU_DT :mmddyy10.;

subs=THRU_DT- FROM_DT;

format FROM_DT THRU_DT date9.;

cards;

10 02/03/2014 02/25/2014

10 03/01/2014 04/02/2014

10 04/03/2014 05/05/2014

10 07/07/2014 07/30/2014

10 08/20/2014 08/25/2014

11 03/02/2013 05/03/2013

11 05/08/2013 05/20/2013

;

proc sort data=have;

by id;

where subs > 30;

run;

Re: How to program my question in SAS

Reeza — Fri, 27 Mar 2015 05:10:22 GMT

I think you need to compare the from date to the previous thru date for the 30 day duration. It's the continuous enrolment problem.

Re: How to program my question in SAS

viveklanka — Fri, 27 Mar 2015 08:03:11 GMT

could you make your question more clear?

Re: How to program my question in SAS

naveen_srini — Fri, 27 Mar 2015 08:40:17 GMT

Hi @LauraZhu, The following does what you want:

data have;
input (ID FROM_DT THRU_DT) ($2. 2*:mmddyy10.) ;
format FROM_DT THRU_DT mmddyy10.;
datalines;
10     02/03/2014     02/25/2014
10     03/01/2014     04/02/2014
10     04/03/2014     05/05/2014
10     07/07/2014     07/30/2014
10     08/20/2014     08/25/2014
11     03/02/2013     05/03/2013
11     05/08/2013     05/20/2013
;

data want(rename=(newfrm_dt=FROM_DT newthru_dt=THRU_DT));
set have;
by id;
retain holddate;
k=lag(thru_dt);

if first.id then
holddate=from_dt;

if not first.id and from_dt-k>30 then
do;
   newfrm_dt=holddate;
   newthru_dt=k;
   holddate=from_dt;
   output;
end;

if last.id then
do;
   newfrm_dt=holddate;
   newthru_dt=thru_dt;
   output;
end;

keep id newfrm_dt newthru_dt;
format newfrm_dt newthru_dt mmddyy10.;
run;

Regards,

Naveen Srinivasan

L&T Infotech

Re: How to program my question in SAS

Ksharp — Fri, 27 Mar 2015 14:13:17 GMT


data have;
input ID $ (FROM_DT THRU_DT) (: mmddyy10.) ;
format FROM_DT THRU_DT mmddyy10.;
datalines;
10     02/03/2014     02/25/2014
10     03/01/2014     04/02/2014
10     04/03/2014     05/05/2014
10     07/07/2014     07/30/2014
10     08/20/2014     08/25/2014
11     03/02/2013     05/03/2013
11     05/08/2013     05/20/2013
;
run;
data temp;
 set have;
 do date=FROM_DT to THRU_DT;
  output;
 end;
 format date mmddyy10.;
 drop FROM_DT THRU_DT ;
run;
data temp;
 set temp;
 if id ne lag(id) or dif(date) gt 30 then n+1;
run;
data want;
 set temp;
 by n;
 retain FROM_DT;
 if first.n then FROM_DT=date;
 if last.n then do;THRU_DT=date;output;end;
 format FROM_DT THRU_DT mmddyy10.;
 drop date n;
run;

Xia Keshan

Re: How to program my question in SAS

LauraZhu — Mon, 30 Mar 2015 20:23:38 GMT

Thank you! I like the programming idea. Very helpful!