BookmarkSubscribeRSS Feed
Chikku
Calcite | Level 5

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

Thank you for the response.

PGStats
Opal | Level 21

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
PGStats
Opal | Level 21

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
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1019 views
  • 0 likes
  • 3 in conversation