topic Re: Cross Tab with multiple columns but a common row variable in SAS Procedures

Cross Tab with multiple columns but a common row variable

DanielQuay — Tue, 05 Jun 2018 19:04:30 GMT

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?

Re: Cross Tab with multiple columns but a common row variable

novinosrin — Tue, 05 Jun 2018 19:06:23 GMT

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

Re: Cross Tab with multiple columns but a common row variable

PaigeMiller — Tue, 05 Jun 2018 19:13:20 GMT

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

Re: Cross Tab with multiple columns but a common row variable

DanielQuay — Tue, 05 Jun 2018 19:12:41 GMT

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

Re: Cross Tab with multiple columns but a common row variable

Reeza — Tue, 05 Jun 2018 19:14:25 GMT

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.

Re: Cross Tab with multiple columns but a common row variable

DanielQuay — Tue, 05 Jun 2018 19:15:08 GMT

Yes, all binary

Re: Cross Tab with multiple columns but a common row variable

PaigeMiller — Tue, 05 Jun 2018 19:15:49 GMT

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?

Re: Cross Tab with multiple columns but a common row variable

DanielQuay — Tue, 05 Jun 2018 19:16:51 GMT

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.

Re: Cross Tab with multiple columns but a common row variable

PaigeMiller — Tue, 05 Jun 2018 19:20:26 GMT

@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.

Re: Cross Tab with multiple columns but a common row variable

DanielQuay — Tue, 05 Jun 2018 19:24:18 GMT

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	1	0	0
1	0	0	0
0	1	0	0
1	0	0	0
1	0	0	0
1	0	0	0
1	0	0	0
1	0	0	0
0	0	1	0
1	0	0	0
1	0	0	0
1	0	0	0
1	0	0	0
1	0	0	0
1	0	0	0
0	1	0	0
0	0	1	0
1	0	0	0
1	0	0	0
0	1	0	0
0	0	0	1
1	0	0	0
1	0	0	0
1	0	0	0
0	0	1	0
1	0	0	0
1	0	0	0
1	0	0	0
1	0	0	0
0	1	0	0
1	0	0	0
1	0	0	0
1	0	0	0
1	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

5.22

26.09

18.75

14.78

73.91

20.48

20.00

6.96

17.78

25.00

32.17

82.22

44.58

39.13

6.96

38.10

25.00

11.30

61.90

15.66

18.26

3.48

30.77

12.50

7.83

69.23

10.84

11.30

5.22

46.15

18.75

6.09

53.85

8.43

11.30

27.83

72.17

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4BPopCat F4B(F4B)0 1 Total12345Total

15.65

78.26

18.37

4.35

21.74

29.41

20.00

36.52

93.33

42.86

2.61

6.67

17.65

39.13

13.91

76.19

16.33

4.35

23.81

29.41

18.26

9.57

84.62

11.22

1.74

15.38

11.76

11.30

9.57

84.62

11.22

1.74

15.38

11.76

11.30

85.22

14.78

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4CPopCat F4C(F4C)0 1 Total12345Total

20.00

100.00

22.12

0.00

20.00

34.78

88.89

38.46

4.35

11.11

45.45

39.13

16.52

90.48

18.27

1.74

9.52

18.18

18.26

9.57

84.62

10.58

1.74

15.38

18.18

11.30

9.57

84.62

10.58

1.74

15.38

18.18

11.30

104

90.43

9.57

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4DPopCat F4D(F4D)0 1 Total12345Total

19.13

95.65

19.30

0.87

4.35

100.00

20.00

39.13

100.00

39.47

0.00

39.13

18.26

100.00

18.42

0.00

18.26

11.30

100.00

11.40

0.00

11.30

100.00

11.40

0.00

11.30

114

99.13

0.87

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4EPopCat F4E(F4E)0 1 Total12345Total

20.00

100.00

20.18

0.00

20.00

39.13

100.00

39.47

0.00

39.13

17.39

95.24

17.54

0.87

4.76

100.00

18.26

11.30

100.00

11.40

0.00

11.30

100.00

11.40

0.00

11.30

114

99.13

0.87

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4FPopCat F4F(F4F)0 Total12345Total

20.00

100.00

20.00

39.13

100.00

39.13

18.26

100.00

18.26

11.30

100.00

11.30

100.00

11.30

115

100.00

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4GPopCat F4G(F4G)0 Total12345Total

20.00

100.00

20.00

39.13

100.00

39.13

18.26

100.00

18.26

11.30

100.00

11.30

100.00

11.30

115

100.00

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4HPopCat F4H(F4H)0 Total12345Total

20.00

100.00

20.00

39.13

100.00

39.13

18.26

100.00

18.26

11.30

100.00

11.30

100.00

11.30

115

100.00

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4IPopCat F4I(F4I)0 Total12345Total

20.00

100.00

20.00

39.13

100.00

39.13

18.26

100.00

18.26

11.30

100.00

11.30

100.00

11.30

115

100.00

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4JPopCat F4J(F4J)0 Total12345Total

20.00

100.00

20.00

39.13

100.00

39.13

18.26

100.00

18.26

11.30

100.00

11.30

100.00

11.30

115

100.00

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4KPopCat F4K(F4K)0 Total12345Total

20.00

100.00

20.00

39.13

100.00

39.13

18.26

100.00

18.26

11.30

100.00

11.30

100.00

11.30

115

100.00

115

100.00

FrequencyPercentRow PctCol Pct

Table of PopCat by F4LPopCat F4L(F4L)0 1 Total12345Total

20.00

100.00

20.35

0.00

20.00

39.13

100.00

39.82

0.00

39.13

18.26

100.00

18.58

0.00

18.26

11.30

100.00

11.50

0.00

11.30

9.57

84.62

9.73

1.74

15.38

100.00

11.30

113

98.26

1.74

115

100.00

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

Re: Cross Tab with multiple columns but a common row variable

Astounding — Tue, 05 Jun 2018 19:46:40 GMT

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.

Re: Cross Tab with multiple columns but a common row variable

Reeza — Tue, 05 Jun 2018 19:56:07 GMT

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.

Re: Cross Tab with multiple columns but a common row variable

DanielQuay — Tue, 05 Jun 2018 20:00:56 GMT

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

Column Data

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

Row Data

1
2
4
4
5
4
3
2
1
3
2

Re: Cross Tab with multiple columns but a common row variable

Reeza — Tue, 05 Jun 2018 20:03:45 GMT

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.

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

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

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

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

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

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