Help using Base SAS procedures

Cross Tab with multiple columns but a common row variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Cross Tab with multiple columns but a common row variable

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?


Accepted Solutions
Solution
‎06-05-2018 05:11 PM
Super User
Posts: 6,776

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

Posted in reply to DanielQuay

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.

View solution in original post


All Replies
PROC Star
Posts: 1,809

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

Posted in reply to DanielQuay

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

Occasional Contributor
Posts: 10

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

Posted in reply to novinosrin

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

Super User
Posts: 23,726

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

Posted in reply to DanielQuay

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.

Occasional Contributor
Posts: 10

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

Yes, all binary

Respected Advisor
Posts: 3,022

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

[ Edited ]
Posted in reply to DanielQuay

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?

 

 

--
Paige Miller
Respected Advisor
Posts: 3,022

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

[ Edited ]
Posted in reply to DanielQuay

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

--
Paige Miller
Occasional Contributor
Posts: 10

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

Posted in reply to PaigeMiller

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.

Respected Advisor
Posts: 3,022

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

[ Edited ]
Posted in reply to DanielQuay

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

--
Paige Miller
Occasional Contributor
Posts: 10

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

Posted in reply to PaigeMiller
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
100000000000
010000000000
100000000000
010000000000
100000000000
100000000000
100000000000
100000000000
100000000000
001000000000
100000000000
100000000000
100000000000
100000000000
100000000000
100000000000
010000000000
001000000000
100000000000
100000000000
010000000000
000000000001
100000000000
100000000000
100000000000
001000000000
100000000000
100000000000
100000000000
100000000000
010000000000
100000000000
100000000000
100000000000
100000000000

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:

 

 

Super User
Posts: 23,726

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

Posted in reply to DanielQuay
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.
Occasional Contributor
Posts: 10

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

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

 

Column Data

100000000000
010000000000
100000000000
010000000000
100000000000
100000000000
100000000000
100000000000
100000000000
001000000000
100000000000

 Row Data

1
2
4
4
5
4
3
2
1
3
2
Super User
Posts: 23,726

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

Posted in reply to DanielQuay

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.

Solution
‎06-05-2018 05:11 PM
Super User
Posts: 6,776

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

Posted in reply to DanielQuay

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.

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 193 views
  • 0 likes
  • 5 in conversation