Transpose program

[Chikku]

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

[RW9]

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;

[Chikku]

Thank you for the response.

[PGStats]

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;

[PGStats]

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;

[PGStats]

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;