DATA Step, Macro, Functions and more

Transpose program

Reply
Contributor
Posts: 29

Transpose program

[ Edited ]

Hi All,

 

I have a below dataset with single variable x:-

 

x
a
a
a
b
b
c

 

I need the output as dataset with a,b,c, as variables as follows using a single proc step:-

a b c

3 2 1

Super User
Super User
Posts: 7,955

Re: Transpose program

Firstly, why do you need it one step?

If thats your data then you could do - though not ideal for many items:

proc sql;

  create table WANT as

  select  distinct

             (select count(*) from HAVE where X="a") as A,

             (select count(*) from HAVE where X="b") as B,

             (seelct count(*) from HAVE where X="c") as C

  from    SASHELP.CLASS;

quit;

 

 

 

Contributor
Posts: 29

Re: Transpose program

Thank you for the response.

Respected Advisor
Posts: 4,925

Re: Transpose program

A datastep solution:

 

data want;
set have end=done;
select (x);
    when ("a") a+1;
    when ("b") b+1;
    when ("c") c+1;
    otherwise;
    end;
if done then output;
drop x;
run;
PG
Respected Advisor
Posts: 4,925

Re: Transpose program

Another SQL solution:

 

proc sql;
create table want as
select sum(x="a") as A, sum(x="b") as B, sum(x="c") as C 
from have;
select * from want;
quit;
PG
Respected Advisor
Posts: 4,925

Re: Transpose program

But if you want a program that will adjust to any number of x values, you will need two steps

 


proc sql;
create table temp as 
select upcase(x) as x, count(*) as n
from have
group by calculated x;
quit;

proc transpose data=temp out=want(drop=_: );
var n;
id x;
run;

proc print data=want noobs; run; 
PG
Ask a Question
Discussion stats
  • 5 replies
  • 249 views
  • 0 likes
  • 3 in conversation