BookmarkSubscribeRSS Feed
Davoz7
Calcite | Level 5

Capture.PNG

Above is an extract of the data I need to work with.
When the same PERNR has consecutive periods with the same ZH2RCRM1 code, I want all that information in just 1 line.

For example PERNR 01687600 has 3 consecutive periods with the same ZH2RCRM1 code (5210).

What I want is to turn those 3 records into one:

01687600     0001     13OCT2014    31DEC9999   5210

I just started in SAS so I have no idea of doing this.

Thanks in advance!

4 REPLIES 4
Astounding
PROC Star

You do have one more decision to make ... how to treat SUBTY.  In your sample data, it is constant.  But what if it were to change?  What value should be used?  Should different values signal different groupings?  At any rate, here is one approach that assumes your data is sorted as indicated in your sample data.  It also assumes your date variables are actual SAS date values (not character strings ... a PROC CONTENTS will reveal that much):

 

proc summary data=have;

   var begda endda;

   id subty;

   by pernr zzh2rcrm1 notsorted;

   output out=want (drop=_type_) max(endda) = endda  min(begda) = begda;

run;

 

This program also adds a variable named _FREQ_ that indicates how many observations were collapsed in order to form the resulting observation.  If you don't want that in the output, add it to the drop list:

 

(drop = _type_ _freq_)

 

Davoz7
Calcite | Level 5

Capture1.PNG

Thank you very much for your reply. It helped a lot!
For the SUBTY variable: it remains constant throughout the whole dataset
The date variables are indeed SAS date values.

 

Just one more thing. I noticed that I also have PERNRs who have some dates that are consecutive than a 'standalone' period and then again some consecutive dates as you can see in the picture above (PERNR 09625800)

For that PERNR my output should be:

09625800   0001   01JUN2012   31OCT2012  5210
09625800   0001   17JUN2013   31OCT2013  5210
09625800   0001   01JUL2014    30SEP2014  5210
09625800   0001   01JUL2015    24JAN2016  5210

instead of:

09625800   0001   01JUN2012    24JAN2016 5210

In general: I can't 'lose' any dates in my output 😉

Sorry if it wasn't very clear

 

Astounding
PROC Star

For that situation, you will need to construct your own grouping variable before running PROC SUMMARY.  For example:

 

data temp;

set have;

by pernr zzh2rcrm1 notsorted;

prior_end = lag(endda);

if first.zzh2crm1 or begda ne prior_end + 1 then group + 1;

drop prior_end;

run;

 

proc summary data=temp;

   var begda endda;

   id subty;

   by pernr zzh2rcrm1 group notsorted;

   output out=want (drop=_type_ group) max(endda) = endda  min(begda) = begda;

run;

 

Davoz7
Calcite | Level 5

This is exactly what I wanted!

Thanks 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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 2164 views
  • 0 likes
  • 2 in conversation