To obtain the following using output using SQL

[venkatnaveen]

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

[Reeza · Accepted solution]

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]

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

[AnnaBrown]

Yes, thanks Reeza!

venkatnaveen, let us know how it worked for you as other community members may be in the same boat.

Anna

[venkatnaveen]

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