DATA Step, Macro, Functions and more

Rolling up

Reply
N/A
Posts: 0

Rolling up

my input datasets looks like this:

ID begindate enddate days month
1 20030108 20030110 2 01
1 20030115 20030118 4 01
1 20030129 20030131 3 01
1 20030201 20030205 5 01

I wuold like the following output

ID begindate enddate
1 20030108 20030110
1 20030115 20030118
1 20030129 20030205

How do I roll up dates?

Thank you.

Diego
Super Contributor
Super Contributor
Posts: 3,174

Re: Rolling up

Posted in reply to deleted_user
You have PROC SQL or PROC MEANS / SUMMARY to use - with MIN/MAX/SUM options to assign/derive output variables. Check the SAS DOC for these options - also there is much "free" information on the SAS support http://support.sas.com/ website, including SAS-hosted DOC and supplemental technical / conference topic-related references.

Also, you need to identify the "break" or "group by" column/variable - it's not obvious from the data-example provided.

Scott Barry
SBBWorks, Inc.
N/A
Posts: 0

Re: Rolling up

I just need to roll up contigious dates in different months. In the example the last 3 days of january and the first five days of february need to be combined so that the begin date would be that jan 29 and the end date would be Feb 5th. That is all I need to do. I wuold have three records instead of 4.

Diego
Regular Contributor
Posts: 241

Re: Rolling up

Posted in reply to deleted_user
Here is one way. hth.
[pre]
/* test data */
data one;
input id (begindate enddate) (:anydtdte.);
format begindate enddate yymmdd10.;
cards;
1 2003-01-08 2003-01-10
1 2003-01-15 2003-01-18
1 2003-01-29 2003-01-31
1 2003-02-01 2003-02-05
2 2003-11-29 2003-11-30
2 2003-12-01 2003-12-02
2 2003-12-03 2003-12-04
2 2003-12-05 2003-12-06
2 2004-01-01 2004-01-01
2 2004-01-02 2004-01-03
;
run;

/* collapse adjacent spells into one within id. assumed no overlaps. */
proc sort data=one;
by id begindate enddate;
run;

data two;
call missing(b);
do until (last.id);
set one end=end;
by id;
if end then do; link doOutput; stop; end;
set one(firstobs=2 keep=begindate rename=(begindate=nextBegindate));
if last.id or enddate + 1 ^= nextBegindate then link doOutput;
else if missing(b) then b = begindate;
end;
return;
doOutput:
if not missing(b) then do;
begindate = b;
call missing(b);
end;
output;
keep id begindate enddate;
return;
run;

proc print data=two noobs;
run;
/* on lst
id begindate enddate
1 2003-01-08 2003-01-10
1 2003-01-15 2003-01-18
1 2003-01-29 2003-02-05
2 2003-11-29 2003-12-06
2 2004-01-01 2004-01-03
*/
[/pre]
N/A
Posts: 0

Re: Rolling up

Posted in reply to chang_y_chung_hotmail_com
It works perfetly. THank you very much.
May I Ask you? What is doOutput?

Thank you again.

Diego
N/A
Posts: 0

Re: Rolling up

Posted in reply to deleted_user
Ops nevermind. I figured it out. Silly quesiton!

Thank you again.
Ask a Question
Discussion stats
  • 5 replies
  • 196 views
  • 0 likes
  • 3 in conversation