BookmarkSubscribeRSS Feed
LauraZhu
Calcite | Level 5


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!

8 REPLIES 8
Patrick
Opal | Level 21

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.

Reeza
Super User

Look at the lag function

Jagadishkatam
Amethyst | Level 16

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
Reeza
Super User

  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.

viveklanka
Fluorite | Level 6

could you make your question more clear?

naveen_srini
Quartz | Level 8

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

Ksharp
Super User

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

LauraZhu
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 8 replies
  • 1055 views
  • 1 like
  • 7 in conversation