topic Subtracting from previous row value in SAS Programming

Subtracting from previous row value

Mei121 — Tue, 29 Oct 2019 18:48:50 GMT

Hi, I am trying to create a waterfall in which I need subtract a value of a var1 in previous row from value of var2 of the current row.

For example

Id var1 var2 result
1 50 50 .
2 45 5 50-5= 45
3 30 15 45-15=30
4 20 10 30-10=20

Is it possible to do so in proc report... If so then how?
Thanks in advance.

Re: Subtracting from previous row value

PaigeMiller — Tue, 29 Oct 2019 18:50:40 GMT

Easy in a DATA step using the LAG() function.

I'm not sure it can be done in PROC REPORT, but you can always do the math in the DATA step and then use PROC REPORT on the results.

Re: Subtracting from previous row value

hashman — Wed, 30 Oct 2019 05:58:03 GMT

@Mei121:

Create a view:

data have ;                         
  input Id var1 var2 ;              
  cards ;                           
1 50 50                             
2 45  5                             
3 30 15                             
4 20 10                             
;                                   
run ;                               
                                    
data v / view = v ;                 
  set have ;                        
  _n_ = lag (var1) ;                
  if nmiss (_n_) then result = _n_ ;
  else result = _n_ - var2 ;        
run ;

and then run your proc REPORT against the view V as input.

Kind regards

Paul D.