## To obtain the following using output using SQL

Solved
Frequent Contributor
Posts: 89

# To obtain the following using output using SQL

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

Accepted Solutions
Solution
‎11-29-2014 01:18 PM
Super User
Posts: 21,936

## Re: To obtain the following using output using SQL

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;

All Replies
Solution
‎11-29-2014 01:18 PM
Super User
Posts: 21,936

## Re: To obtain the following using output using SQL

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;

Frequent Contributor
Posts: 89

## Re: To obtain the following using output using SQL

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

Community Manager
Posts: 639

## Re: To obtain the following using output using SQL

Yes, thanks Reeza!

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

Anna

Frequent Contributor
Posts: 89