topic Re: Aggregate rows based on groups in New SAS User

Aggregate rows based on groups

Lars45 — Mon, 12 Aug 2019 09:25:41 GMT

Hello SAS Community,

I'm doing my first steps in SAS at the moment and i've gote a problem which i can't solve...

I have a table like the following example:

Column1 - Column2

A - 500

B - 580

A - 911

B - 419

B - 165

So all i want to do is to create a new SAS Table with aggregated rows for all "A"s and "B"s.

Thanks for your help!

Re: Aggregate rows based on groups

PeterClemmensen — Mon, 12 Aug 2019 09:33:29 GMT

Welcome to the SAS Community 🙂

Is this what you are after?

data have;
input Column1 $ Column2;
datalines;
A 500
B 580
A 911
B 419
B 165
;

proc sql;
    create table want as
    select Column1, sum(Column2) as sum
    from have
    group by Column1;
quit;

Re: Aggregate rows based on groups

Lars45 — Mon, 12 Aug 2019 09:44:43 GMT

Thanks for the quick answer!
This might be a solutions, but I was actually looking for a way without writing code myself.
I should have been more specific....
I will try to use your solution, but with that one I have to figure out some more things before I can tell if it works.

Re: Aggregate rows based on groups

Jagadishkatam — Mon, 12 Aug 2019 09:57:48 GMT

Alternatively via data step

data have;
input Column1$ Column2;
cards;
A 500
B 580
A 911
B 419
B 165
;

proc sort data=have;
by column1;
run;

data want;
set have;
by column1;
retain sum;
if first.column1 then sum=column2;
else sum+column2;
if last.column1;
run;

Re: Aggregate rows based on groups

novinosrin — Mon, 12 Aug 2019 11:55:24 GMT

FWIW


data have;
input Column1 $ Column2;
datalines;
A 500
B 580
A 911
B 419
B 165
;
data _null_;
if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("Column1") ;
   h.definedata ("Column1","sum") ;
   h.definedone () ;
end;
do until(z);
 set have end=z;
 if h.find()= 0 then sum=sum(sum,column2);
 else sum=column2;
 h.replace();
end;
h.output(dataset:'want');
stop;
run;

Re: Aggregate rows based on groups

novinosrin — Mon, 12 Aug 2019 12:02:54 GMT

proc summary data=have nway ;
class column1;
var column2;
output out=want(drop=_:) sum=sum;
run;