data step read calculated value of row in next row

[sidpesar]

Input Table

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

[Andygray]

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

[sidpesar]

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

[novinosrin]

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;

[sidpesar]

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

[novinosrin]

Ok sorry I haven't tested. let me try

[novinosrin]

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;

[FreelanceReinh · Accepted solution]

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;

[novinosrin]

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