Cross Tab with multiple columns but a common row variable

[DanielQuay]

Hello,

So this should be simple enough, I suspect it's a matrix, just not really good at those yet.

proc freq data=dataset;

tables ROW*COLA

       ROW*COLB

       ROW*COLC

       ROW*COLD

       ROW*COLE

       ROW*COLF

       ROW*COLG

       ROW*COLH

       ROW*COLI;

run;

this will get me an answer that I'm needing, I just have to compile all of the separate tables generated into a single table separately from SAS right now.  My question is, is there a cleaner or more efficient way to do this so that I get all of the Columns displayed on a single grid rather than having to do that manually after the fact?

[novinosrin]

Hi @DanielQuay  Can you please post a sample of your Input data and a sample of your expect output?  Thank you!

[DanielQuay]

Sure.

Row=1 2 3 4 5

COLA= 0 1

COLB= 0 1

COLC= 0 1

COLD= 0 1

COLE= 0 1

COLF= 0 1

Desired output:

Row  COLA     COLB     COLC     COLD     COLE     COLF

1            27           0             0            1            0           11

2            0            1            15          27            0           11

3            10          45           0            1             0             1

4            33          0             3            1             0             6

5            20          0             0            17           0              1

[Reeza]

All 0/1 in your columns?

Can you please confirm your input data structure?

There are a few ways to give you exactly what you want in one step I suspect, but it depends on your input data.

[DanielQuay]

Yes, all binary

[PaigeMiller]

Before, you had PROC FREQ, which gives you frequencies for COLA and COLB etc, I assume frequencies of more than one level.

Now you have a desired output where you have only a single number in each cell. How do you go from frequencies of more than one level, to a single number in each cell?

[PaigeMiller]

PROC REPORT can give you these counts in a single table.

[DanielQuay]

Unsure, that's where I'm trying to get to.  Though in truth, I can handle having percentages and such as well as the rest of the info available in a proc freq step.  I'm researching proc report now.

[PaigeMiller]

---

@DanielQuay wrote:

Unsure, that's where I'm trying to get to.  Though in truth, I can handle having percentages and such as well as the rest of the info available in a proc freq step.  I'm researching proc report now.

---

But this doesn't answer my question.

What is the math that yields a 27 in that top left cell?

@DanielQuay, the folks here have done a lot of consulting in this forum and elsewhere, and we are going to be asking a lot of questions until you clearly and completely define the problem. You would save yourself (and save us) a lot of time by giving us a clear and complete statement of the problem, and at least show us a representative portion of the data.

[DanielQuay]

A part of the problem is I might not be understanding what you're asking so I will try again.

My data are laid out

1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
0	0	0	0	0	0	0	0	0	0	0	1
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0

When I run the frequency procedure one at a time I get this output. 
The FREQ Procedure

FrequencyPercentRow PctCol Pct

Table of PopCat by F4APopCat F4A(F4A)0 1 Total12345Total 6 5.22 26.09 18.75 17 14.78 73.91 20.48 23 20.00     8 6.96 17.78 25.00 37 32.17 82.22 44.58 45 39.13     8 6.96 38.10 25.00 13 11.30 61.90 15.66 21 18.26     4 3.48 30.77 12.50 9 7.83 69.23 10.84 13 11.30     6 5.22 46.15 18.75 7 6.09 53.85 8.43 13 11.30     32 27.83 83 72.17 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4BPopCat F4B(F4B)0 1 Total12345Total 18 15.65 78.26 18.37 5 4.35 21.74 29.41 23 20.00     42 36.52 93.33 42.86 3 2.61 6.67 17.65 45 39.13     16 13.91 76.19 16.33 5 4.35 23.81 29.41 21 18.26     11 9.57 84.62 11.22 2 1.74 15.38 11.76 13 11.30     11 9.57 84.62 11.22 2 1.74 15.38 11.76 13 11.30     98 85.22 17 14.78 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4CPopCat F4C(F4C)0 1 Total12345Total 23 20.00 100.00 22.12 0 0.00 0.00 0.00 23 20.00     40 34.78 88.89 38.46 5 4.35 11.11 45.45 45 39.13     19 16.52 90.48 18.27 2 1.74 9.52 18.18 21 18.26     11 9.57 84.62 10.58 2 1.74 15.38 18.18 13 11.30     11 9.57 84.62 10.58 2 1.74 15.38 18.18 13 11.30     104 90.43 11 9.57 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4DPopCat F4D(F4D)0 1 Total12345Total 22 19.13 95.65 19.30 1 0.87 4.35 100.00 23 20.00     45 39.13 100.00 39.47 0 0.00 0.00 0.00 45 39.13     21 18.26 100.00 18.42 0 0.00 0.00 0.00 21 18.26     13 11.30 100.00 11.40 0 0.00 0.00 0.00 13 11.30     13 11.30 100.00 11.40 0 0.00 0.00 0.00 13 11.30     114 99.13 1 0.87 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4EPopCat F4E(F4E)0 1 Total12345Total 23 20.00 100.00 20.18 0 0.00 0.00 0.00 23 20.00     45 39.13 100.00 39.47 0 0.00 0.00 0.00 45 39.13     20 17.39 95.24 17.54 1 0.87 4.76 100.00 21 18.26     13 11.30 100.00 11.40 0 0.00 0.00 0.00 13 11.30     13 11.30 100.00 11.40 0 0.00 0.00 0.00 13 11.30     114 99.13 1 0.87 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4FPopCat F4F(F4F)0 Total12345Total 23 20.00 100.00 20.00 23 20.00     45 39.13 100.00 39.13 45 39.13     21 18.26 100.00 18.26 21 18.26     13 11.30 100.00 11.30 13 11.30     13 11.30 100.00 11.30 13 11.30     115 100.00 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4GPopCat F4G(F4G)0 Total12345Total 23 20.00 100.00 20.00 23 20.00     45 39.13 100.00 39.13 45 39.13     21 18.26 100.00 18.26 21 18.26     13 11.30 100.00 11.30 13 11.30     13 11.30 100.00 11.30 13 11.30     115 100.00 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4HPopCat F4H(F4H)0 Total12345Total 23 20.00 100.00 20.00 23 20.00     45 39.13 100.00 39.13 45 39.13     21 18.26 100.00 18.26 21 18.26     13 11.30 100.00 11.30 13 11.30     13 11.30 100.00 11.30 13 11.30     115 100.00 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4IPopCat F4I(F4I)0 Total12345Total 23 20.00 100.00 20.00 23 20.00     45 39.13 100.00 39.13 45 39.13     21 18.26 100.00 18.26 21 18.26     13 11.30 100.00 11.30 13 11.30     13 11.30 100.00 11.30 13 11.30     115 100.00 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4JPopCat F4J(F4J)0 Total12345Total 23 20.00 100.00 20.00 23 20.00     45 39.13 100.00 39.13 45 39.13     21 18.26 100.00 18.26 21 18.26     13 11.30 100.00 11.30 13 11.30     13 11.30 100.00 11.30 13 11.30     115 100.00 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4KPopCat F4K(F4K)0 Total12345Total 23 20.00 100.00 20.00 23 20.00     45 39.13 100.00 39.13 45 39.13     21 18.26 100.00 18.26 21 18.26     13 11.30 100.00 11.30 13 11.30     13 11.30 100.00 11.30 13 11.30     115 100.00 115 100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4LPopCat F4L(F4L)0 1 Total12345Total 23 20.00 100.00 20.35 0 0.00 0.00 0.00 23 20.00     45 39.13 100.00 39.82 0 0.00 0.00 0.00 45 39.13     21 18.26 100.00 18.58 0 0.00 0.00 0.00 21 18.26     13 11.30 100.00 11.50 0 0.00 0.00 0.00 13 11.30     11 9.57 84.62 9.73 2 1.74 15.38 100.00 13 11.30     113 98.26 2 1.74 115 100.00

The output I am wanting have the same information but in a single table:

[Reeza]

Where does ROW come in then? I feel like that demo data is not reflective. We only need a few records, say 10 but it needs to be as close as possible to your data structure.

[DanielQuay]

Alright, this time I know what I did wrong in my reply.  Sorry. 

Column Data

1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	1	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0
0	0	1	0	0	0	0	0	0	0	0	0
1	0	0	0	0	0	0	0	0	0	0	0

 Row Data

1
2
4
4
5
4
3
2
1
3
2

[Reeza]

Ok, this is what you're trying to do then:

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

Rather than mean you want the sum though:

proc means data=have noprint nway;
class row;
var var1-var10; *list your variables here;
output out=want sum= /autoname;
run;

Try this code and look at the want data set and see how close it gets you for now.

[Astounding · Accepted solution]

Based on all your posts taken as a group, I think this would do it:

proc tabulate data=have;

class row;

var cola colb colc cold cole colf colg colh coli;

tables row, (cola colb colc cold cole colf colg colh coli) * sum=' ';

run;

It seems like the SUM is what you are after here.  If you also want percent, when you have a binary variable you can just ask for the mean.