Example: Do a cross tabulation of colorn*leveln?
Colorn is represented as follows:
1- Red
2- Orange
3- Yellow
4-Green
5- Blue
Leveln is represented as:
1-low
2-Medium
3-High
Here is the data set:
obs colorn leveln 1 1 1 2 4 3 3 3 3
I have to make a table with counts that looks like this:
low med high
red x (xx.x) x (xx.x) x (xx.x)
orange x (xx.x) x (xx.x) x (xx.x)
yellow x (xx.x) x (xx.x) x (xx.x)
green x (xx.x) x (xx.x) x (xx.x)
blue x (xx.x) x (xx.x) x (xx.x)
That means that there have to be zeros represented.
I tried using a proc freq with an out statement, but i'm not able to pull the cells with missing values. Is there a way to do that?
I tried using this, but it doesn't work for what i'm trying to achieve.
proc freq data=data;
table colorn*leveln/ out=cnt;
run;
Thanks
@Hello_there wrote:
Hi thanks for your reply. I'm still a novice when it comes to programming, but does using PROC REPORT or TABULATE produce a sas data set?
Both procedures can create datasets, unfortunately they won't be in the expected shape. Have a look at proc summary, the 6th example has all you need: https://documentation.sas.com/doc/de/pgmsascdc/9.4_3.5/proc/n1eseaucwkzo18n1nph31op5kkdt.htm#n1eseau...
EDIT: Well, except for not being able to create the data you want 😞 I should have read your post fully.
So you don't want to produce that report you showed? Instead you just want to make a dataset?
Why does it matter if the zeros are not in the dataset if you not making the report?
If you want to make a report why not use PROC TABULATE so that you can use the PRELOADFMT option of the CLASS statement?
proc format ;
value color
1= Red
2= Orange
3= Yellow
4= Green
5= Blue
;
value level
1=Low
2=Medium
3=High
;
run;
data have;
input colorn leveln;
format colorn color. leveln level.;
cards;
1 1
4 3
3 3
;
options missing='0';
proc tabulate data=have order=data;
class colorn leveln / preloadfmt ;
tables colorn,leveln*(N PCTN) / printmiss ;
run;
One way is use a different procedure such as Proc Tabulate or Proc Report to do the summary using the Preloadfmt option where you create formats to display the text regardless of the presence of the value.
Proc format; value colorn 1 = 'Red' 2 = "Orange" 3 = "Yellow" 4 = "Green" 5 = "Blue" ; value leveln 1 = "Low" 2 = "Medium" 3 = "High" ; run; data have; input colorn leveln; datalines; 1 1 4 3 3 3 1 2 1 3 ; proc tabulate data=have; class colorn leveln / preloadfmt; format colorn colorn. leveln leveln.; table colorn=' ', leveln=' '*(n rowpctn) /printmiss misstext='0'; run;
May get you started. You didn't show which goes in the () so I picked one of the percentages as an example.
Preloadfmt requires use of other options such as Printmiss and/or order=data (on the class statement(s);
Note that there are some other potential data issues. Note the the data step creating Have data. That is how to display the data that you have (or want when you want a data set).
Hi thanks for your reply. I'm still a novice when it comes to programming, but does using PROC REPORT or TABULATE produce a sas data set?
@Hello_there wrote:
Hi thanks for your reply. I'm still a novice when it comes to programming, but does using PROC REPORT or TABULATE produce a sas data set?
Both procedures can create datasets, unfortunately they won't be in the expected shape. Have a look at proc summary, the 6th example has all you need: https://documentation.sas.com/doc/de/pgmsascdc/9.4_3.5/proc/n1eseaucwkzo18n1nph31op5kkdt.htm#n1eseau...
EDIT: Well, except for not being able to create the data you want 😞 I should have read your post fully.
You can use ODS OUTPUT to redirect the output to a dataset.
data have; input colorn leveln; format colorn leveln ; cards; 1 1 4 3 3 3 ; data all; do colorn=1 to 5; do leveln=1 to 3; output; end; end; run; data want; set have(in=ina) all; w=ina; run; proc freq data=want; table colorn*leveln/ out=cnt; weight w/zero; run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.