DATA Step, Macro, Functions and more

Sum amount with multiple rows in to one row

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Sum amount with multiple rows in to one row

Hello,

I am trying to add up the AMOUNT AND INCREASE with multiple rows have the same ID.

 

data calc;

input ID AMOUNT INCREASE

cards;
8888   50              3.55
8889   1.10           0   
8889   2.99           2.22
8890   3.48         15
8891   34.08       1  
8891   144.87     4
8891   2.24        0.8
8892   0             0 
;

I like to add the AMOUNT AND INCREASE FOR EACH ID.

The result:

8888        50           3.55
8889          4.09      2.22
8890          3.48    15
8891      181.19      5.8
8892          0           0

 

Thank you.


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 22,818

Re: Sum amount with multiple rows in to one row

Summarizing data is straightforward with grouping variables. 

Here's an example of two ways to do this, with different output styles. 2 tables are created, want and want2 with different structures, I think you're looking for WANT1 perhaps?

 

*Create summary statistics for a dataset by a 'grouping' variable and store it in a dataset;

*Generate sample fake data;
data have;
	input ID          feature1         feature2         feature3;
	cards;
1               7.72               5.43              4.35
1               5.54               2.25              8.22 
1               4.43               6.75              2.22
1               3.22               3.21              7.31
2               6.72               2.86              6.11
2               5.89               4.25              5.25 
2               3.43               7.30              8.21
2               1.22               3.55              6.55

;
run;

*Create summary data;
proc means data=have noprint;
	by id;
	var feature1-feature3;
	output out=want median= var= mean= /autoname;
run;

*Show for display;
proc print data=want;
run;

*First done here:https://communities.sas.com/t5/General-SAS-Programming/Getting-creating-new-summary-variables-longitudinal-data/m-p/347940/highlight/false#M44842;
*Another way to present data is as follows;

proc means data=have stackods nway n min max mean median std p5 p95;
    by id;
    var feature1-feature3;
    ods output summary=want2;
run;

*Show for display;
proc print data=want2;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 22,818

Re: Sum amount with multiple rows in to one row

Summarizing data is straightforward with grouping variables. 

Here's an example of two ways to do this, with different output styles. 2 tables are created, want and want2 with different structures, I think you're looking for WANT1 perhaps?

 

*Create summary statistics for a dataset by a 'grouping' variable and store it in a dataset;

*Generate sample fake data;
data have;
	input ID          feature1         feature2         feature3;
	cards;
1               7.72               5.43              4.35
1               5.54               2.25              8.22 
1               4.43               6.75              2.22
1               3.22               3.21              7.31
2               6.72               2.86              6.11
2               5.89               4.25              5.25 
2               3.43               7.30              8.21
2               1.22               3.55              6.55

;
run;

*Create summary data;
proc means data=have noprint;
	by id;
	var feature1-feature3;
	output out=want median= var= mean= /autoname;
run;

*Show for display;
proc print data=want;
run;

*First done here:https://communities.sas.com/t5/General-SAS-Programming/Getting-creating-new-summary-variables-longitudinal-data/m-p/347940/highlight/false#M44842;
*Another way to present data is as follows;

proc means data=have stackods nway n min max mean median std p5 p95;
    by id;
    var feature1-feature3;
    ods output summary=want2;
run;

*Show for display;
proc print data=want2;
Contributor
Posts: 47

Re: Sum amount with multiple rows in to one row

Thank you so much.

PROC Star
Posts: 1,282

Re: Sum amount with multiple rows in to one row

data calc;

input ID AMOUNT INCREASE;

cards;
8888   50              3.55
8889   1.10           0   
8889   2.99           2.22
8890   3.48         15
8891   34.08       1  
8891   144.87     4
8891   2.24        0.8
8892   0             0 
;

proc sql;
create table want as
select id,sum(AMOUNT) as sum_amt, sum(increase) as sum_increase
from calc
group by id;
quit;
Contributor
Posts: 47

Re: Sum amount with multiple rows in to one row

Posted in reply to novinosrin

thank you so much. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 101 views
  • 2 likes
  • 3 in conversation