topic Re: data step read calculated value of row in next row in SAS Programming

data step read calculated value of row in next row

sidpesar — Sat, 19 Jan 2019 19:42:59 GMT

Input Table

Desired output-Pred_lvl col

output table

But when I run Below code

data want;                                 
set Pred_level;                   
tes=lag4(pred_lvl);
if missing(pred_lvl) then pred_lvl = pred + tes; 
drop tes;
run;

I am getting an output as below

incorrect output table

Problem:

When I run my code it is only updating first 4 missing rows and remaining are still missing.

I need a code where

if pred_lvl is missing pred_lvl= pred + lag4(pred_lvl);

The desired out put should be when ever pred_lvl is missing it should update pred_lvl column by adding pred and pred_lvl[i-4]

any help is much appreciated

Re: data step read calculated value of row in next row

Andygray — Sat, 19 Jan 2019 19:37:02 GMT

Hi @sidpesar Can you attach your input file or something that is transferable rather than pics?

And of course, explain the requirement aka business logic

Re: data step read calculated value of row in next row

sidpesar — Sat, 19 Jan 2019 19:44:03 GMT

I added input csv file. Please let me know if you could figure it out. It will be very helpful

Re: data step read calculated value of row in next row

novinosrin — Sat, 19 Jan 2019 19:54:44 GMT

Are you looking to do this?

data want;                                 
set Pred_level;   
retain tes; 
tes=ifn(pred_lvl>.,lag4(pred_lvl),tes);
if missing(pred_lvl) then pred_lvl = pred + tes; 
drop tes;
run;

Re: data step read calculated value of row in next row

sidpesar — Sat, 19 Jan 2019 19:59:07 GMT

Thanks for quick reply the calulation is still wrong from 13th row again. Actual out put should be 0.06103576 but when I run below code I am getting 0.0612312338

Re: data step read calculated value of row in next row

novinosrin — Sat, 19 Jan 2019 19:59:53 GMT

Ok sorry I haven't tested. let me try

Re: data step read calculated value of row in next row

novinosrin — Sat, 19 Jan 2019 21:45:48 GMT

Hio @sidpesar Sorry for the delay as I had some personal stuff to do. The problem is the executed lag of source variable is not held in memory and so the real value missing is output from the 17 record onwards. The control of this can be dealt by assigning into a temp var from pred_lvl but I would leave that to MKeintz(lag specialist ) or somebody else to play with lag.

I prefer to control using array and to me this approach is so simple. See if this works until Mark the lag specialist chimes in

data want;
do _n_=1 by 1 until(last.modelid);
set have;
by modelid;
array t(9999);
if pred_lvl>. then t(_n_)=pred_lvl;
else do; pred_lvl=t(_n_-4)+pred;t(_n_)=pred_lvl;end;
output;
end;
drop t:;
run;

Re: data step read calculated value of row in next row

FreelanceReinh — Sat, 19 Jan 2019 22:33:08 GMT

Hi @sidpesar,

I think you can also use a slightly modified version of your code (change the order of the assignment and IF/THEN statements and replace LAG4 with LAG3), combined with the RETAIN statement that @novinosrin suggested:

data want;
set Pred_level;
retain tes;
if missing(pred_lvl) then pred_lvl = pred + tes;
tes=lag3(pred_lvl);
drop tes;
run;

Re: data step read calculated value of row in next row

novinosrin — Sat, 19 Jan 2019 22:53:46 GMT

slick!!!!!!!!!!!!!!!!!!!!Kudos!