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

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6742 views
  • 7 likes
  • 4 in conversation