topic Re: First and last from group within group - intervals of time in SAS Programming

First and last from group within group - intervals of time

soraiapedp — Wed, 12 Feb 2020 16:39:44 GMT

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 🙂

Re: First and last from group within group - intervals of time

Tom — Wed, 12 Feb 2020 16:44:29 GMT

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-twist/td-p/412951

Re: First and last from group within group - intervals of time

ed_sas_member — Wed, 12 Feb 2020 16:59:29 GMT

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;

Re: First and last from group within group - intervals of time

soraiapedp — Wed, 12 Feb 2020 17:15:11 GMT

Thanks a lot !

It worked !!!