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!
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.
Look at the lag function
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;
could you make your question more clear?
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
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
Thank you! I like the programming idea. Very helpful!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.