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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.