topic Re: Proc Freq to display 0 frequency in SAS Programming

Proc Freq to display 0 frequency

Charlie — Sat, 21 Sep 2019 18:05:42 GMT

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

Re: Proc Freq to display 0 frequency

Reeza — Sat, 21 Sep 2019 18:56:53 GMT

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

Re: Proc Freq to display 0 frequency

Ksharp — Sun, 22 Sep 2019 12:40:54 GMT

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;

Re: Proc Freq to display 0 frequency

PhilC — Tue, 24 Sep 2019 18:17:10 GMT

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

Re: Proc Freq to display 0 frequency

Charlie — Wed, 25 Sep 2019 02:04:54 GMT

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

Re: Proc Freq to display 0 frequency

Charlie — Wed, 25 Sep 2019 02:08:04 GMT

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.