SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

To obtain the following using output using SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

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: 17,959

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;

View solution in original post


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

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: 486

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

Re: To obtain the following using output using SQL

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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