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.
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.
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.