topic Re: Rolling up in SAS Programming

Rolling up

deleted_user — Fri, 17 Sep 2010 14:54:27 GMT

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

Re: Rolling up

sbb — Fri, 17 Sep 2010 15:10:19 GMT

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.

Re: Rolling up

deleted_user — Fri, 17 Sep 2010 15:30:21 GMT

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

Re: Rolling up

chang_y_chung_hotmail_com — Fri, 17 Sep 2010 21:33:43 GMT

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]

Re: Rolling up

deleted_user — Sat, 18 Sep 2010 03:15:35 GMT

It works perfetly. THank you very much.
May I Ask you? What is doOutput?

Thank you again.

Diego

Re: Rolling up

deleted_user — Sat, 18 Sep 2010 03:17:44 GMT

Ops nevermind. I figured it out. Silly quesiton!

Thank you again.