Goodmorning everyone,
The problem I have to solve is to update a column based on some checks and changes made on the previous line and on the line itself.
Below is the starting table "Data_from".
ROW | ID | Periodo_Dal | Periodo_Al | Dal_Prec | Al_Prec | periodo_dal_new | periodo_al_new |
1 | A | 19JUN2017 | 31DEC2017 | . | . | 19JUN2017 | 31DEC2017 |
2 | A | 07NOV2017 | 31DEC2017 | 19JUN2017 | 31DEC2017 | 07NOV2017 | 31DEC2017 |
3 | A | 01JAN2018 | 31MAY2018 | 07NOV2017 | 31DEC2017 | 01JAN2018 | 31MAY2018 |
4 | A | 01JAN2018 | 30SEP2018 | 01JAN2018 | 31MAY2018 | 01JAN2018 | 30SEP2018 |
5 | A | 02JAN2018 | 26APR2018 | 01JAN2018 | 30SEP2018 | 02JAN2018 | 26APR2018 |
6 | A | 26MAR2018 | 24MAY2018 | 02JAN2018 | 26APR2018 | 26MAR2018 | 24MAY2018 |
7 | A | 04APR2018 | 15MAY2018 | 26MAR2018 | 24MAY2018 | 04APR2018 | 15MAY2018 |
8 | A | 03MAY2018 | 29JUN2018 | 04APR2018 | 15MAY2018 | 03MAY2018 | 29JUN2018 |
9 | A | 04MAY2018 | 26AUG2018 | 03MAY2018 | 29JUN2018 | 04MAY2018 | 26AUG2018 |
10 | A | 08MAY2018 | 18JUN2018 | 04MAY2018 | 26AUG2018 | 08MAY2018 | 18JUN2018 |
11 | A | 02JUN2018 | 31DEC2018 | 08MAY2018 | 18JUN2018 | 02JUN2018 | 31DEC2018 |
12 | A | 08JUL2018 | 22JUL2018 | 02JUN2018 | 31DEC2018 | 08JUL2018 | 22JUL2018 |
What I have to do is:
compare Periodo_Dal with Al_Prec (lag (Periodo_Al))
if Period_Dal <Al_Prec then replace the Period_Dal with Al_Prec.
if Period_Dal> Periodo_Al, then replace Periodo_Al with Period_Dal.
Reiterate this way for all the lines.
I used this code but it does not work.
data Data_from;
set Data_to;
if periodo_dal <= al_prec then do;
periodo_dal =al_prec;
if periodo_dal ge periodo_al then
periodo_al=periodo_dal;
end;
Dal_Prec=lag1(Periodo_Dal);
Al_Prec=lag1(Periodo_Al);
run;
The result is the following "Data_to":
ROW | ID | Periodo_Dal | Periodo_Al | Dal_Prec | Al_Prec | periodo_dal_new | periodo_al_new |
1 | A | 19JUN2017 | 31DEC2017 | . | . | 19JUN2017 | 31DEC2017 |
2 | A | 31DEC2017 | 31DEC2017 | . | . | 07NOV2017 | 31DEC2017 |
3 | A | 01JAN2018 | 31MAY2018 | 31DEC2017 | 31DEC2017 | 01JAN2018 | 31MAY2018 |
4 | A | 31MAY2018 | 30SEP2018 | 01JAN2018 | 31MAY2018 | 01JAN2018 | 30SEP2018 |
5 | A | 30SEP2018 | 30SEP2018 | 31MAY2018 | 30SEP2018 | 02JAN2018 | 26APR2018 |
6 | A | 26APR2018 | 24MAY2018 | 30SEP2018 | 30SEP2018 | 26MAR2018 | 24MAY2018 |
7 | A | 24MAY2018 | 24MAY2018 | 26APR2018 | 24MAY2018 | 04APR2018 | 15MAY2018 |
8 | A | 15MAY2018 | 29JUN2018 | 24MAY2018 | 24MAY2018 | 03MAY2018 | 29JUN2018 |
9 | A | 29JUN2018 | 26AUG2018 | 15MAY2018 | 29JUN2018 | 04MAY2018 | 26AUG2018 |
10 | A | 26AUG2018 | 26AUG2018 | 29JUN2018 | 26AUG2018 | 08MAY2018 | 18JUN2018 |
11 | A | 18JUN2018 | 31DEC2018 | 26AUG2018 | 26AUG2018 | 02JUN2018 | 31DEC2018 |
12 | A | 31DEC2018 | 31DEC2018 | 18JUN2018 | 31DEC2018 | 08JUL2018 | 22JUL2018 |
As you can see it stops working on line 6.
In Periodo_Dal the value 26APR2018 is reported instead of 30SEP2018. In fact 26APR2018 is the value reported in Data_from table.
Do you have any ideas?
Thank you