Proc Freq to display 0 frequency

[Charlie]

Dear all

I am trying to create a cross table to see how food test change, I used following code:

data raw;
  input person firsttest $ secondtest $;
  datalines;
  1 Good Good
  2 Poor Good
  3 Good Bad
  4 Poor Bad
  5 Poor Poor
;
run;

proc freq data=raw;
  table firsttest*secondtest/nocol norow nopercent;
run;

due to results in firttest dose not have "Bad" result, therefore, it shows:

Table of firsttest by secondtest
	secondtest
firsttest	Bad	Good	Poor	Total
Good	1	1	0	2
Poor	1	1	1	3
Total	2	2	1	5

I am wondering how to display a full result as following table:

Table of firsttest by secondtest
	secondtest
firsttest	Bad	Good	Poor	Total
Bad	0	0	0	0
Good	1	1	0	2
Poor	1	1	1	3
Total	2	2	1	5

I also tried to use prco tabulate to do it, but it failed.

Appreciate the help.

Charlie

[Reeza]

PRELOADFMT is usually one way. Basically, if it's not in the table you have to tell SAS in some manner that it needs to exist, otherwise it can't know that you want to add something that doesn't exist in the data. 

/*This demonstrates how to ensure all levels are in your report
using the preloadfmt option*/


data raw;
  input person firsttest $ secondtest $;
  datalines;
  1 Good Good
  2 Poor Good
  3 Good Bad
  4 Poor Bad
  5 Poor Poor
;
run;

proc format;
value $ test_values 
   "Good" = "Good"
   "Poor" = "Poor"
   "Bad" = "Bad";
 run;
 

proc tabulate data=raw;
class firsttest secondtest / preloadfmt;
table firsttest, secondtest / misstext='0' printmiss;
format firsttest secondtest $test_values.;
run;

---

@Charlie wrote:

Dear all

I am trying to create a cross table to see how food test change, I used following code:

data raw;
  input person firsttest $ secondtest $;
  datalines;
  1 Good Good
  2 Poor Good
  3 Good Bad
  4 Poor Bad
  5 Poor Poor
;
run;

proc freq data=raw;
  table firsttest*secondtest/nocol norow nopercent;
run;

due to results in firttest dose not have "Bad" result, therefore, it shows:

Table of firsttest by secondtest
	secondtest
firsttest	Bad	Good	Poor	Total
Good	1	1	0	2
Poor	1	1	1	3
Total	2	2	1	5

I am wondering how to display a full result as following table:

Table of firsttest by secondtest
	secondtest
firsttest	Bad	Good	Poor	Total
Bad	0	0	0	0
Good	1	1	0	2
Poor	1	1	1	3
Total	2	2	1	5

I also tried to use prco tabulate to do it, but it failed.

Appreciate the help.

Charlie

---

[Charlie]

Thank you for your reply, I am not so familiar with proc tabulate and learn from your reply, I will do more homework on it.

[Ksharp · Accepted solution]

data raw;
  input person firsttest $ secondtest $;
  datalines;
  1 Good Good
  2 Poor Good
  3 Good Bad
  4 Poor Bad
  5 Poor Poor
;
run;
data have;
 set raw end=last;
 w=1;output;
 if last then do;w=0;firsttest='Bad';output;end;
run;
 

proc freq data=have;
  table firsttest*secondtest/nocol norow nopercent;
  weight w/zero;
run;

[Charlie]

This is exactly what I want, thank you a lot.

[PhilC]

It would be a great thing if we could allow the original poster to mark more than one response as a solution.