topic Transpose program in SAS Programming

Transpose program

Chikku — Fri, 09 Oct 2015 09:47:52 GMT

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

Re: Transpose program

RW9 — Fri, 09 Oct 2015 09:59:31 GMT

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;

Re: Transpose program

Chikku — Fri, 09 Oct 2015 10:55:25 GMT

Thank you for the response.

Re: Transpose program

PGStats — Fri, 09 Oct 2015 15:30:18 GMT

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;

Re: Transpose program

PGStats — Fri, 09 Oct 2015 15:34:18 GMT

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;

Re: Transpose program

PGStats — Fri, 09 Oct 2015 15:44:36 GMT

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;