topic What is the most efficient way to add down per column per group? in SAS Programming

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

Hello_there — Wed, 09 Nov 2022 21:59:05 GMT

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:

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

Reeza — Wed, 09 Nov 2022 22:03:32 GMT

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:

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

Hello_there — Wed, 09 Nov 2022 22:05:13 GMT

This was helpful. Thanks!

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

ballardw — Wed, 09 Nov 2022 22:05:18 GMT

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.