Desktop productivity for business analysts and programmers

How to turn consecutive dates in one period?

Reply
Occasional Contributor
Posts: 5

How to turn consecutive dates in one period?

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!

Super User
Posts: 6,933

Re: How to turn consecutive dates in one period?

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_)

 

Occasional Contributor
Posts: 5

Re: How to turn consecutive dates in one period?

Posted in reply to Astounding

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 Smiley Wink

Sorry if it wasn't very clear

 

Super User
Posts: 6,933

Re: How to turn consecutive dates in one period?

[ Edited ]

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;

 

Occasional Contributor
Posts: 5

Re: How to turn consecutive dates in one period?

Posted in reply to Astounding

This is exactly what I wanted!

Thanks again!

Ask a Question
Discussion stats
  • 4 replies
  • 172 views
  • 0 likes
  • 2 in conversation