i have a dataset like this
data test;
input name$ rank;
cards;
a 1
b 2
c 3
a 4
b 5
c 6
run;
i want o/p as three columns a,b,c and their rank sum like below through SQL only.
a b c
5 7 9g
This can be done, though it would be much easier using proc means or other SAS code.
The SQL is manual so it's not really scalable at all so if you have more than 3 or 4 groups it will be tedious.
/*solution requested*/
proc sql;
create table want as
select
sum(rank*(name='a')) as a,
sum(rank*(name='b')) as b,
sum(rank*(name='c')) as c
from test;
quit;
/*recommended solution*/
proc sql;
create table want_temp as
select name, sum(rank) as total
from test
group by name;
quit;
proc transpose data=want_temp out=want2 (drop=_name_);
id name;
var total;
run;
This can be done, though it would be much easier using proc means or other SAS code.
The SQL is manual so it's not really scalable at all so if you have more than 3 or 4 groups it will be tedious.
/*solution requested*/
proc sql;
create table want as
select
sum(rank*(name='a')) as a,
sum(rank*(name='b')) as b,
sum(rank*(name='c')) as c
from test;
quit;
/*recommended solution*/
proc sql;
create table want_temp as
select name, sum(rank) as total
from test
group by name;
quit;
proc transpose data=want_temp out=want2 (drop=_name_);
id name;
var total;
run;
Thanks a lot Reeza.I wll try the second one.
Yes, thanks Reeza!
venkatnaveen, let us know how it worked for you as other community members may be in the same boat.
Anna
Join us for SAS Community Trivia
SAS Bowl XXIX, The SAS Hackathon
Wednesday, March 8, 2023, at 10 AM ET | #SASBowl
I wil surely tell.Its one of the question which my friend have asked.Ill ask him about the result.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.