Help using Base SAS procedures

How to program my question in SAS

Reply
New Contributor
Posts: 2

How to program my question in SAS


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!

Respected Advisor
Posts: 3,908

Re: How to program my question in SAS

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.

Super User
Posts: 17,963

Re: How to program my question in SAS

Look at the lag function

Trusted Advisor
Posts: 1,131

Re: How to program my question in SAS

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;

Thanks,
Jag
Super User
Posts: 17,963

Re: How to program my question in SAS

  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.

Occasional Contributor
Posts: 19

Re: How to program my question in SAS

could you make your question more clear?

Frequent Contributor
Posts: 115

Re: How to program my question in SAS

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

Super User
Posts: 9,691

Re: How to program my question in SAS


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

New Contributor
Posts: 2

Re: How to program my question in SAS

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

Ask a Question
Discussion stats
  • 8 replies
  • 294 views
  • 1 like
  • 7 in conversation