Help using Base SAS procedures

How to create a 2 by 2 table

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

How to create a 2 by 2 table

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


Accepted Solutions
Solution
‎02-06-2012 10:51 AM
Respected Advisor
Posts: 3,124

How to create a 2 by 2 table

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

View solution in original post


All Replies
Solution
‎02-06-2012 10:51 AM
Respected Advisor
Posts: 3,124

How to create a 2 by 2 table

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

Contributor
Posts: 52

How to create a 2 by 2 table

This is excellent and neat. Thanks a lot.

SAS Super FREQ
Posts: 8,743

Re: How to create a 2 by 2 table

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 177 views
  • 0 likes
  • 3 in conversation