topic How to turn consecutive dates in one period? in SAS Enterprise Guide

How to turn consecutive dates in one period?

Davoz7 — Wed, 07 Feb 2018 12:52:39 GMT

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!

Re: How to turn consecutive dates in one period?

Astounding — Wed, 07 Feb 2018 14:03:34 GMT

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

Re: How to turn consecutive dates in one period?

Davoz7 — Wed, 07 Feb 2018 14:25:08 GMT

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

Re: How to turn consecutive dates in one period?

Astounding — Wed, 07 Feb 2018 14:36:21 GMT

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;

Re: How to turn consecutive dates in one period?

Davoz7 — Wed, 07 Feb 2018 14:50:25 GMT

This is exactly what I wanted!

Thanks again!