topic SAS BASE comparing different variable using LAG function in New SAS User

SAS BASE comparing different variable using LAG function

gabras — Fri, 25 Jan 2019 11:01:29 GMT

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

Re: SAS BASE comparing different variable using LAG function

RW9 — Fri, 25 Jan 2019 11:11:55 GMT

You cannot change values in a lagged column at the same time as checking them. To do what you are asking, use a retained variable, e.g.:

data want;
  set have;
  retain lst_periodo_dal;
  by id;
  if first.id then do;
    lst_periodo_dal=periodo_dal;
  end;
  else do;
    if periodo_dal < lst_periodo_dal then periodo_dal = lst_periodo_dal;
    lst_periodo_dal = periodo_dal;
  end;
run;

In future, please post test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

And use the code window to show code - its the {i} above post.

Also, if your data already contains a lagged value in al_prec, why do you need lag at all? Just compare periodo_dal < al_prec then periodo_dal = al_prec?

Re: SAS BASE comparing different variable using LAG function

gabras — Fri, 25 Jan 2019 12:08:43 GMT

Thanks @RW9 for the answer.

When the script go to the second if:
if period_Dal ge period_Al then period_al = period_Dal ;

The next comparison must be made with the period_Al just replaced in the previous if.

Re: SAS BASE comparing different variable using LAG function

RW9 — Fri, 25 Jan 2019 13:45:44 GMT

As shown above, just use a retained variable, and change the value of the retained value as needed. You could update it in a the first if, then use that in the second.