What is the most efficient way to add down per column per group?

[Hello_there]

Hello, I'm trying to add down the column per group and merge that total back into the original data set. What is the most efficient way to do this?

Thanks

Note: The variable MAINCAT contains the groups that i need the totals for.

data have;
infile datalines dsd dlm=",";
	input maincat $ subcat $ count1;
datalines;
Red, Red1, 32
Red, Red2, 17
Red, Red3, 7
Orange, Orange1, 22
Orange, Orange2, 11
Orange, Orange3, 6
Yellow, Yellow1, 14
Yellow, Yellow2, 12
;
run;

This is the desired output:

[Reeza · Accepted solution]

SQL is the easiest method to program

proc sql;
create table want as 
select *, sum(count1) as want
from have
group by maincat
order by 1, 2;
quit;

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

---

@Hello_there wrote:

Hello, I'm trying to add down the column per group and merge that total back into the original data set. What is the most efficient way to do this?

Thanks

Note: The variable MAINCAT contains the groups that i need the totals for.

data have;
infile datalines dsd dlm=",";
	input maincat $ subcat $ count1;
datalines;
Red, Red1, 32
Red, Red2, 17
Red, Red3, 7
Orange, Orange1, 22
Orange, Orange2, 11
Orange, Orange3, 6
Yellow, Yellow1, 14
Yellow, Yellow2, 12
;
run;

This is the desired output:

---

[Hello_there]

This was helpful. Thanks!

[ballardw]

Define the measures you use to determine "most efficient".

One way, if you don't mind the order of records changing a bit:

Proc sql;
   create table want as
   select maincat, subcat, count1, sum(count1) as want
   from have
   group by maincat
   order by maincat, subcat
   ;
run;

Otherwise use proc summary to total the values and "merge" back using a Merge statement though you are going to run into some order issues requiring sorting before the Merge and the order will end up similar to the above.