topic Re: restructuring two-way tables with variable on left and statistic along top in SAS Procedures

restructuring two-way tables with variable on left and statistic along top

donevans — Fri, 18 Sep 2009 18:07:01 GMT

Is there a way to restructure a two-way table so that the variable name is on the left and statistic along the top?

Example:
------ |------- Group1 ---------| |------------- Group 2 -----------|
------ n mean median max n mean median max
var1
var2
var3

Re: restructuring two-way tables with variable on left and statistic along top

sbb — Fri, 18 Sep 2009 18:21:10 GMT

Have you looked at PROC TABULATE?

Scott Barry
SBBWorks, Inc.

Re: restructuring two-way tables with variable on left and statistic along top

donevans — Fri, 18 Sep 2009 18:35:59 GMT

Yes, I use proc tabulate to get the following result. It's easier to compare if all of the statistics were listed across the top instead of under each of the variable names.
-------- |--- Group1 -----| |----- Group2------|
var1
n
mean
median
max
var2
n
mean
median
max
var3
n

Re: restructuring two-way tables with variable on left and statistic along top

sbb — Fri, 18 Sep 2009 18:44:09 GMT

I'd recommend searching the SAS support http://support.sas.com/ website for examples based on technical and conference topic-related references. Google advanced search (using the parameter site:sas.com) or the support site's SEARCH facility are very helpful with finding these opportunities, as well as the SAS-hosted product documentation which includes examples.

Scott Barry
SBBWorks, Inc.

Re: restructuring two-way tables with variable on left and statistic along top

Cynthia_sas — Sat, 19 Sep 2009 02:53:48 GMT

Hi:
It may be as easy as moving your statistics from the row dimension to the column dimension, as shown in the program below.

The first PROC TABULATE shows the statistics nested in the row dimension; while the second PROC TABULATE shows the statistics nested in the column dimension.

cynthia
[pre]

** make some data;
data prdsale;
set sashelp.prdsale;
if division = 'CONSUMER' then group = 'Group 1';
else group = 'Group 2';
run;

ods html file='c:\temp\tabvars.html' style=sasweb;
proc tabulate data=prdsale;
title 'One Way';
class group;
var actual predict;
table actual*(n mean median max) predict*(n mean median max),
group=' ';
run;

proc tabulate data=prdsale;
title 'Another Way';
class group;
var actual predict;
table actual predict,
group=' '*(n mean median max);
run;
ods html close;
[/pre]

Re: restructuring two-way tables with variable on left and statistic along top

donevans — Tue, 22 Sep 2009 12:50:30 GMT

Thanks. This worked. I had added an extra "all" between the "predict" variable and comma, so I was trying to run stats on a stat. Needless to say, SAS did not like that.

Re: restructuring two-way tables with variable on left and statistic along top

donevans — Wed, 23 Sep 2009 13:31:57 GMT

Do you know how to do the same with text where the yes/no answer is stored in the varx?
------|--Group1-------|-----|--Group2-------|
----- |--no--| |--yes--|-----|--no--| |--yes--|
var1
var2

Re: restructuring two-way tables with variable on left and statistic along top

Cynthia_sas — Wed, 23 Sep 2009 15:02:55 GMT

Hi:
What is a VARX??? Do you have 2 variables -- GROUP1 with values of YES and NO and then GROUP2 with values of YES and NO or do you have 1 variable with values of either GROUP1 or GROUP2???

In the situation you describe, would the variables going down the rows still be numeric variables (what you show as VAR1 and VAR2)? Or would they be category variables (like COUNTRY or REGION for example)??

It might help to see a little sample of data. For example, I can envision at least 2 possible ways that your data could be structured:

Possibility A:
[pre]
VARA VARB GROUP1 GROUP2 NVAR1 NVAR2
xxx yyy yes no 111 221
xxx yyy no no 112 222
xxx yyy no yes 113 223
aaa bbb yes yes 114 224
aaa bbb no no 115 225
[/pre]

and
Possibility B:
[pre]
VARA VARB GROUP RESP NVAR
xxx yyy GROUP1 yes 111
xxx yyy GROUP2 no 221
xxx yyy GROUP1 no 112
xxx yyy GROUP2 no 222
xxx yyy GROUP1 no 113
xxx yyy GROUP2 yes 223
aaa bbb GROUP1 yes 114
aaa bbb GROUP2 yes 224
aaa bbb GROUP1 no 115
aaa bbb GROUP2 no 225
[/pre]

If you look at SASHELP.PRDSALE, you will see that it has numeric variables ACTUAL and PREDICT and then the DIVISION variable has values of CONSUMER or EDUCATION and the REGION variable has the values of EAST or WEST. COUNTRY has 3 possible values. The example program below shows the difference between using ACTUAL and PREDICT in the ROW dimension (table 1) versus using all category variables in the ROW and COL dimensions (table 2) versus using a numeric variable nested with the category variables in the COL dimension (table 3).

If you look at SASHELP.PRDSALE and compare the structure of its data to the structure of your data, you may get some ideas of how to plug your variables into this sample program.

cynthia

[pre]
ods html file='c:\temp\catvars.html' style=sasweb;

proc tabulate data=sashelp.prdsale;
title '1) Sum of Numeric Vars In the Cells';
class division region;
var actual predict;
table actual predict,
division region all;
run;

proc tabulate data=sashelp.prdsale;
title '2) Count of Category Variables in the Cells';
class division region country;
table country all,
division region all;
run;

proc tabulate data=sashelp.prdsale;
title '3) Sum of Actual in the Cells';
class division region country;
var actual;
table country all,
actual*(division region all);
run;
ods html close;
[/pre]

Re: restructuring two-way tables with variable on left and statistic along top

donevans — Wed, 23 Sep 2009 17:09:57 GMT

My data looks something like this (1st row are the variable names):

group---var1---var2---var3---var4......
group1--no----yes----yes---no
group2--yes--no-----yes----yes

I want to do something like a frequency count (like proc freq, but transposed--frequencies and all possible response only) where the name of the variable is on the left side and there are three columns (yes,no,blank) with the count for each response to the right of the variable name.
The table examples you provided will list the variable name (on the 1st row) along the left as well as the possible responses in the 2nd(blank), 3rd(no) and 4th(yes) rows.
Once I understand how to do this "one-way" table, I want to add the "group" to the top for the "two-way" dimension.

------|--Group1-------|-----|--Group2-------|
----- |--no--| |--yes--|-----|--no--| |--yes--|
var1
var2
var3
var4

Re: restructuring two-way tables with variable on left and statistic along top

Cynthia_sas — Wed, 23 Sep 2009 18:57:45 GMT

Hi:
It would be easier to do what you want if your data were in a different structure. something like this:
[pre]
group id varname varval

group1 A var1 no
group1 A var2 yes
group1 A var3 yes
group1 A var4 no
group2 A var1 yes
group2 A var2 no
group2 A var3 yes
group2 A var4 yes
group1 B var1
group1 B var2 yes
group1 B var3 yes
group1 B var4 no
group2 B var1 yes
group2 B var2
group2 B var3 yes
group2 B var4 no
group1 C var1 no
group1 C var2 no
group1 C var3
group1 C var4 no
group2 C var1 yes
group2 C var2 no
group2 C var3 yes
group2 C var4
[/pre]

(Note that this fake data also has an ID variable, which your data may or may not have.)

Once the data are in this structure, you could use a PROC TABULATE like this:
[pre]
ods listing close;
ods html file='newgrp.html' style=sasweb;
options missing = 0;
proc tabulate data=newgrp f=comma7.;
title '4) Using VARNAME and VARVAL';
class group varname varval / missing;
table varname=' ' all,
group=' '*varval=' ' all;
keylabel n=' ';
run;
ods html close;
[/pre]

So, how do you get from your current structure to the new structure? I'd use either a data step program and array processing or proc transpose. I think the data step approach is probably simpler to understand. That transformation and the entire program is shown below.

cynthia
[pre]
data testgrp;
infile datalines dlm='-';
input group $ var1 $ var2 $ var3 $ var4 $ id $;
return;
datalines;
group1--no--yes--yes--no--A
group2--yes--no--yes--yes--A
group1--.--yes--yes--no--B
group2--yes--.--yes--no--B
group1--no--no--.--no--C
group2--yes--no--yes--.--C
;
run;

options nocenter;

ods listing;
proc print data=testgrp;
title 'current structure';
run;

data newgrp;
set testgrp;
array vn $3 var1-var4;
do i = 1 to 4 by 1;
varname = cat('var',put(i,1.0));
varval = vn(i);
output;
end;
drop var1-var4 i;
run;

proc print data=newgrp noobs;
title 'new structure';
run;

ods listing close;
ods html file='newgrp.html' style=sasweb;
options missing = 0;
proc tabulate data=newgrp f=comma7.;
title '4) Using VARNAME and VARVAL';
class group varname varval / missing;
table varname=' ' all,
group=' '*varval=' ' all;
keylabel n=' ';
run;
ods html close;
[/pre]