topic accumulating sum in SAS Programming

accumulating sum

twildone — Sat, 21 Mar 2020 21:40:20 GMT

Hi...I am trying to do a running total for each amount. The BegRevenue is the previous records EndRevenue and the current record's EndRevenue is the records BegRevenue plus the Show Amount. I can't seem to get this to work. Any suggestions....thanks.

data have;
    length ShowAmount 8 ;
    format ShowAmount Dollar20.2 ;
    informat ShowAmount Dollar20. ;
    infile datalines4 dlm='7F'x missover dsd;
    input ShowAmount: best32. ;
datalines4;
1550
-1550
1550
-1550
-1550
1550
1775
975
100
-1850
-900
-100
;;;;

data want(drop=_EndRevenue);
	set have;
	retain _EndRevenue;
		if _n_=1 then do;
			BegRevenue = 0;
			EndRevenue = ShowAmount;
			_EndRevenue = EndRevenue;
			end;
		else do;
			BegRevenue = _EndRevenue;
			EndRevenue = (BegRevenue+ShowAmount);
			BegRevenue = _EndRevenue;
			end;
run;

ShowAmount	BegRevenue	EndRevenue
$1,550.00	0	1550
-$1,550.00	1550	0
$1,550.00	1550	3100
-$1,550.00	1550	0
-$1,550.00	1550	0
$1,550.00	1550	3100
$1,775.00	1550	3325
$975.00	1550	2525
$100.00	1550	1650
-$1,850.00	1550	-300
-$900.00	1550	650
-$100.00	1550	1450

Re: accumulating sum

mkeintz — Sat, 21 Mar 2020 22:15:39 GMT

I presume you are showing the numbers you got, instead of what you want. Here is a program that generates what I understand to be your objective:

data have;
    length ShowAmount 8 ;
    format ShowAmount Dollar20.2 ;
    informat ShowAmount Dollar20. ;
    infile datalines4 dlm='7F'x missover dsd;
    input ShowAmount: best32. ;
datalines4;
1550
-1550
1550
-1550
-1550
1550
1775
975
100
-1850
-900
-100
;;;;

data want;
  set have;
  retain begrevenue 0;
  endrevenue=begrevenue+showamount;
  output;
  begrevenue=endrevenue;
run;

The "trick" here is to output the current record, and then update begrevenue in preparation for the next record.

Re: accumulating sum

twildone — Sat, 21 Mar 2020 22:38:04 GMT

Hi..Mkeintz….Yes you are right. That is what I was wanting...thanks for your help...