How would i be able to represent these values in PROC FREQ if they are not in the data set?

[Hello_there]

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

[Tom]

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;

[Hello_there]

Hi thanks for your reply. I need to make it a data set and not a report because what i'm working on is an on-going study that gets re-run constantly and i'm trying to make the data set according to a mock.

[ballardw]

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

[Hello_there]

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? 

[andreas_lds · Accepted solution]

---

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

[Hello_there]

Thanks! PROC SUMMARY is exactly what i needed.

[Kurt_Bremser]

You can use ODS OUTPUT to redirect the output to a dataset.

[Ksharp]

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;