BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Midi
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
r_behata
Barite | Level 11
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;

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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;
r_behata
Barite | Level 11
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 375 views
  • 2 likes
  • 3 in conversation