topic Re: To obtain the following using output using SQL in SAS Data Management

To obtain the following using output using SQL

venkatnaveen — Sat, 29 Nov 2014 17:48:26 GMT

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

Re: To obtain the following using output using SQL

Reeza — Sat, 29 Nov 2014 18:18:37 GMT

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;

Re: To obtain the following using output using SQL

venkatnaveen — Sun, 30 Nov 2014 03:59:56 GMT

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

Re: To obtain the following using output using SQL

AnnaBrown — Mon, 01 Dec 2014 17:44:20 GMT

Yes, thanks Reeza!

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

Anna

Re: To obtain the following using output using SQL

venkatnaveen — Tue, 02 Dec 2014 08:06:21 GMT

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