topic How to do an Iterative sum when we have multiple columns ? in SAS Programming

How to do an Iterative sum when we have multiple columns ?

Midi — Wed, 30 Oct 2019 18:35:00 GMT

Hello Everyone , I have This DataSet :

Users	Spendings_M1	Spendings_M2	Spendings_M3
12353	30	100	10
12353	10	200	20
12353	20	300	30
12353	40	400	40
12353	30	500	50
14562	10	300	70
14562	20	400	80
14562	50	500	90
14562	60	600	100
14562	70	700	110

And What I Want To Do , is to make an iterative sum for each user ,i.e:

like for user_1 i want , for the first line , the first spending , for the second column , i want , to sum of spending between line 1 and line 2 , for the third column , i want the sum of spending in line1 , line2 , line 3 .....ect , like my output would look like this :

Users	Spendings_M1	Spendings_M2	Spendings_M3	TOT_SPEN_M1	TOT_SPEN_M2	TOT_SPEN_M3
12353	30	100	10	30	100	10
12353	10	200	20	40	300	30
12353	20	300	30	60	600	60
12353	40	400	40	100	1000	100
12353	30	500	50	130	1500	150
14562	10	300	70	10	300	70
14562	20	400	80	30	700	150
14562	50	500	90	80	1200	240
14562	60	600	100	140	1800	340
14562	70	700	110	210	2500	440

Any suggestions on how to do that , would be much appreciated , thank you.

Re: How to do an Iterative sum when we have multiple columns ?

novinosrin — Wed, 30 Oct 2019 18:47:42 GMT

Hi @Midi

data have;
input Users	Spendings_M1	Spendings_M2	 	Spendings_M3	;
lines; 
12353	30	100	 	10	 
12353	10	200	 	20	 
12353	20	300	 	30	 
12353	40	400	 	40	 
12353	30	500	 	50	 
14562	10	300	 	70	 
14562	20	400	 	80	 
14562	50	500	 	90	 
14562	60	600	 	100	 
14562	70	700	 	110	 
;

data want;
 do until(last.users);
  set have;
  by users;
  array TOT_SPEN_M(3);
  array t Spendings_M:;
  do over t;
   TOT_SPEN_M(_i_)=sum(TOT_SPEN_M(_i_),t);
  end;
  output;
 end;
run;

Re: How to do an Iterative sum when we have multiple columns ?

r_behata — Wed, 30 Oct 2019 19:29:11 GMT

data want;
	set have;
	by users;
	retain TOT_SPEN_M1 TOT_SPEN_M2 TOT_SPEN_M3 0;

	if first.users then
		do;
			TOT_SPEN_M1=Spendings_M1;
			TOT_SPEN_M2=Spendings_M2;
			TOT_SPEN_M3=Spendings_M3;
	end;
	else
		do;
			TOT_SPEN_M1+Spendings_M1;
			TOT_SPEN_M2+Spendings_M2;
			TOT_SPEN_M3+Spendings_M3;
		end;
run;