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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.