Hi,
This is an interesting question. I have a sample data like below:
x y freq
------------------------
F High 3
M Low 2
F Low 4
M High 1
What I want to achieve is to use some approach to transform the above table into the format as:
x High Low
F 3 4
M 1 2
That is, the values of variable y have been transposed to become the new variable names.
I hope to learn from you. Thanks.
Ruth
Here you go:
data work.data;
input x $ y $ gender $;
cards;
1 1 F
1 2 M
2 1 F
2 2 F
1 2 M
;
proc sql;
create table want as
select *,count
;
data have;
infile cards;
input x $ y $ freq;
cards;
F High 3
M Low 2
F Low 4
M High 1
;
proc sort data=have;
by x;
run;
proc transpose data=have out=want (drop=_name_);
by x;
id y;
var freq;
run;
proc print;run;
Regards,
Haikuo
Here you go:
data work.data;
input x $ y $ gender $;
cards;
1 1 F
1 2 M
2 1 F
2 2 F
1 2 M
;
proc sql;
create table want as
select *,count
;
data have;
infile cards;
input x $ y $ freq;
cards;
F High 3
M Low 2
F Low 4
M High 1
;
proc sort data=have;
by x;
run;
proc transpose data=have out=want (drop=_name_);
by x;
id y;
var freq;
run;
proc print;run;
Regards,
Haikuo
This is excellent and neat. Thanks a lot.
Hi:
And, in addition to PROC TRANSPOSE, which makes an output dataset, you can also use PROC TABULATE or PROC REPORT to generate your desired results in report form.
cynthia
ods listing close;
ods html file='c:\temp\rep_tab.html';
proc tabulate data=yourdata f=6.;
title '1) TABULATE';
class x y;
var freq;
tables x,y*freq=' ';
keylabel sum=' ';
run;
proc report data=yourdata nowd;
title '2) REPORT';
column x freq,y;
define x /group style(column)=Header;
define y / across;
define freq / sum ' ';
run;
ods html close;
title;
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.