BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DanielQuay
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

13 REPLIES 13
novinosrin
Tourmaline | Level 20

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

DanielQuay
Quartz | Level 8

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
Super User

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.

PaigeMiller
Diamond | Level 26

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
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
DanielQuay
Quartz | Level 8

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
Diamond | Level 26

@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
DanielQuay
Quartz | Level 8
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:

 

 

Reeza
Super User
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
Quartz | Level 8

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
Reeza
Super User

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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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