First and last from group within group - intervals of time

[soraiapedp]

Hey guys!

I need ur help please.

So i have a tabel like the one in attachment. 

ANLAGE         OPERAND         AB              BIS

0010010527   RT_EA_TSOC   20181126   20181221
0010010527   RT_EA_TSOC   20181222   20190414
0010010527   RT_EA_TSOC   20190415   20190511
0010010527   RT_EA_TSOC   20190610   20190811
0010010527   RT_EA_TSOC   20190901   20191001
0010010527   RT_EA_TSOC   20191002   20191225
0010010527   RT_EA_TSOC   20200101   99991231
0010010588   RT_EA_TSOC   20181126   20181221
0010010588   RT_EA_TSOC   20181229   20190414
0010010588   RT_EA_TSOC   20190415   99991231

For each ANLAGE i want to obtain the time interval in just one, instead of divided by 2/3/... rows. EG.: for the anlage 0010010527 the first, second and third rows could be in just on time interval, because the AB of the second is right next to the BIS of the first one, and the AB of the third is right next to the BIS of the second.

I know this could be confusing, but for you to understand what i would like to obtain, here's the result to be:

ANLAGE         OPERAND         AB              BIS            FINAL_AB   FINAL_BIS

0010010527   RT_EA_TSOC   20181126   20181221   20181126    20190511
0010010527   RT_EA_TSOC   20181222   20190414   20181126    20190511
0010010527   RT_EA_TSOC   20190415   20190511   20181126    20190511
0010010527   RT_EA_TSOC   20190610   20190811   20190610    20190811
0010010527   RT_EA_TSOC   20190901   20191001   20190901    20191225
0010010527   RT_EA_TSOC   20191002   20191225   20190901    20191225
0010010527   RT_EA_TSOC   20200101   99991231   20200101    99991231
0010010588   RT_EA_TSOC   20181126   20181221   20181126    20181221
0010010588   RT_EA_TSOC   20181229   20190414   20181229    99991231
0010010588   RT_EA_TSOC   20190415   99991231    20181229   99991231

HELP PLS 🙂

[Tom]

This has been asked many times.

Here is link to one example:  https://communities.sas.com/t5/SAS-Programming/Collapsing-Dates-across-rows-of-observations-with-a-t...

[soraiapedp]

Thanks a lot ! 

It worked !!!

[ed_sas_member]

Hi @soraiapedp 

Here is an attempt to achieve this:

data have;
	input ANLAGE OPERAND $ AB:YYMMDD10. BIS:YYMMDD10.;
	format AB BIS YYMMDD10.;
	datalines;
0010010527   RT_EA_TSOC   20181126   20181221
0010010527   RT_EA_TSOC   20181222   20190414
0010010527   RT_EA_TSOC   20190415   20190511
0010010527   RT_EA_TSOC   20190610   20190811
0010010527   RT_EA_TSOC   20190901   20191001
0010010527   RT_EA_TSOC   20191002   20191225
0010010527   RT_EA_TSOC   20200101   99991231
0010010588   RT_EA_TSOC   20181126   20181221
0010010588   RT_EA_TSOC   20181229   20190414
0010010588   RT_EA_TSOC   20190415   99991231
;
run;

data have_flag;
	set have;
	by ANLAGE;
	format _lag_BIS YYMMDD10.;
	_lag_BIS = lag(BIS) + 1;
	if first.ANLAGE then call missing(_lag_BIS);
	if AB ne _lag_BIS then count+1;
run;

proc sql;
	create table want as
	select ANLAGE, OPERAND, AB, BIS,
		   min(AB) as FINAL_AB format=YYMMDD10.,
		   max (BIS) as FINAL_BIS format=YYMMDD10.
	from have_flag
	group by ANLAGE, count
	order by ANLAGE, AB;
run;