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 🙂
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...
Thanks a lot !
It worked !!!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.