BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
loving_apples
Calcite | Level 5

Good afternoon,

I may have a problem to generate new data.   As we can see that after Jan-01-2014, Rev is 0. And I would like to fill Rev after Jan-01-2014 by calculating: Rev=lag2(Rev)*Exp(Num). However, there is no data for Rev after Jan-01-2014, so SAS cannot calculate Rev based on the lagged Rev. I tried Retain but it didn't work:(

NumRevDate
345454Jan-01-2013
4354353Feb-01-2013
725425Mar-01-2013
3254235Jan-01-2014
8Feb-01-2014
9Mar-01-2014
12Jan-01-2015
45Feb-01-2015lag
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Yes, that would be a problem.  LAG works on the original values when the LAG function is invoked, rather than the subsequent values after manipulating the data.

Here's a workaround, creating your own stream of values that mimics what the LAG function would do but after manipulating the data.

data want;

  back2 = back1;

  back1 = rev;

  retain back1;

  set have;

  if rev=. then rev=back2 * exp(num);

  * optionally, once the program is working, drop back1 back2;

run;

Good luck.

View solution in original post

2 REPLIES 2
Astounding
PROC Star

Yes, that would be a problem.  LAG works on the original values when the LAG function is invoked, rather than the subsequent values after manipulating the data.

Here's a workaround, creating your own stream of values that mimics what the LAG function would do but after manipulating the data.

data want;

  back2 = back1;

  back1 = rev;

  retain back1;

  set have;

  if rev=. then rev=back2 * exp(num);

  * optionally, once the program is working, drop back1 back2;

run;

Good luck.

loving_apples
Calcite | Level 5

Great thanks Astounding, but what if I need lag4 instead of lag2, what I shall change the code?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 882 views
  • 1 like
  • 2 in conversation