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
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;
Thank you for the response.
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.