How to create a 2 by 2 table

[Ruth]

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

[Haikuo · Accepted solution]

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

[Ruth]

This is excellent and neat. Thanks a lot.

[Cynthia_sas]

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;