BookmarkSubscribeRSS Feed
gabras
Pyrite | Level 9


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".

ROWIDPeriodo_DalPeriodo_AlDal_PrecAl_Precperiodo_dal_newperiodo_al_new
1A19JUN201731DEC2017..19JUN201731DEC2017
2A07NOV201731DEC201719JUN201731DEC201707NOV201731DEC2017
3A01JAN201831MAY201807NOV201731DEC201701JAN201831MAY2018
4A01JAN201830SEP201801JAN201831MAY201801JAN201830SEP2018
5A02JAN201826APR201801JAN201830SEP201802JAN201826APR2018
6A26MAR201824MAY201802JAN201826APR201826MAR201824MAY2018
7A04APR201815MAY201826MAR201824MAY201804APR201815MAY2018
8A03MAY201829JUN201804APR201815MAY201803MAY201829JUN2018
9A04MAY201826AUG201803MAY201829JUN201804MAY201826AUG2018
10A08MAY201818JUN201804MAY201826AUG201808MAY201818JUN2018
11A02JUN201831DEC201808MAY201818JUN201802JUN201831DEC2018
12A08JUL201822JUL201802JUN201831DEC201808JUL201822JUL2018


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":

ROWIDPeriodo_DalPeriodo_AlDal_PrecAl_Precperiodo_dal_newperiodo_al_new
1A19JUN201731DEC2017..19JUN201731DEC2017
2A31DEC201731DEC2017..07NOV201731DEC2017
3A01JAN201831MAY201831DEC201731DEC201701JAN201831MAY2018
4A31MAY201830SEP201801JAN201831MAY201801JAN201830SEP2018
5A30SEP201830SEP201831MAY201830SEP201802JAN201826APR2018
6A26APR201824MAY201830SEP201830SEP201826MAR201824MAY2018
7A24MAY201824MAY201826APR201824MAY201804APR201815MAY2018
8A15MAY201829JUN201824MAY201824MAY201803MAY201829JUN2018
9A29JUN201826AUG201815MAY201829JUN201804MAY201826AUG2018
10A26AUG201826AUG201829JUN201826AUG201808MAY201818JUN2018
11A18JUN201831DEC201826AUG201826AUG201802JUN201831DEC2018
12A31DEC201831DEC201818JUN201831DEC201808JUL201822JUL2018


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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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-dat...

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?

gabras
Pyrite | Level 9

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.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 840 views
  • 0 likes
  • 2 in conversation