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?
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.
Hi @DanielQuay Can you please post a sample of your Input data and a sample of your expect output? Thank you!
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
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.
Yes, all binary
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?
PROC REPORT can give you these counts in a single table.
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.
@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.
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 |
FrequencyPercentRow PctCol Pct | Table of PopCat by F4APopCat F4A(F4A)0 1 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4BPopCat F4B(F4B)0 1 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4CPopCat F4C(F4C)0 1 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4DPopCat F4D(F4D)0 1 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4EPopCat F4E(F4E)0 1 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4FPopCat F4F(F4F)0 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4GPopCat F4G(F4G)0 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4HPopCat F4H(F4H)0 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4IPopCat F4I(F4I)0 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4JPopCat F4J(F4J)0 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4KPopCat F4K(F4K)0 Total12345Total
|
FrequencyPercentRow PctCol Pct | Table of PopCat by F4LPopCat F4L(F4L)0 1 Total12345Total
|
The output I am wanting have the same information but in a single table:
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 |
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.