BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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
chang_y_chung_hotmail_com
Obsidian | Level 7
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]
deleted_user
Not applicable
It works perfetly. THank you very much.
May I Ask you? What is doOutput?

Thank you again.

Diego
deleted_user
Not applicable
Ops nevermind. I figured it out. Silly quesiton!

Thank you again.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 723 views
  • 0 likes
  • 3 in conversation