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

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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; 

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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; 
Lars45
Fluorite | Level 6
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.
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
novinosrin
Tourmaline | Level 20

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;
novinosrin
Tourmaline | Level 20
proc summary data=have nway ;
class column1;
var column2;
output out=want(drop=_:) sum=sum;
run;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 6619 views
  • 7 likes
  • 4 in conversation