SAS and VBA (7): calculate running total

This is a simple routine for a lot of reporting work. Many people tend to do it in Excel by dragging the range after entering the formula in the 1st cell. However, coding in VBA and SAS will usually do it in more prompt and safe way.

VBA

VBA’s unique R1C1 formula is pretty handy once we get to know the rule. The 1st cell at the F column has different R1C1 formula than the cells below.

Sub Rt()
'   Find the last row
    FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("F1").Value = "Running total of Weight"
    Range("F2").FormulaR1C1 = "=RC[-1]"
    Range("F3:F" & FinalRow).FormulaR1C1 = "=RC[-1] + R[-1]C"
End Sub

SAS

It is incredibly easy to do the job in SAS. One line of code — that is all! Obviously SAS beats VBA’s three lines in this demo here.

data want;
   set sashelp.class;
   label total_weight = "Running total of Weight"; 
   total_weight + weight;
run;

Post navigation