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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

4 REPLIES 4
Reeza
Super User

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;

venkatnaveen
Obsidian | Level 7

Thanks a lot Reeza.I wll try the second one.

AnnaBrown
Community Manager

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

venkatnaveen
Obsidian | Level 7

I wil surely tell.Its one of the question which my friend have asked.Ill ask him about the result.

sas-innovate-2024.png

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.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 932 views
  • 3 likes
  • 3 in conversation