## Posted By

webonomic on 08/25/09

# Show all possible values in table regardless of whether or not the value exists

/ Published in: SAS

Here are two solutions when creating a table to show responses to a questionnaire where respondents rate items in various categories. In this case, the responses range from 0 to 5 (N/A, Very Poor, Poor, Average, Good, Very Good).

`/* Given the following Data: */ data test;input var1 var2 var3;datalines;. 2 .. 2 10 2 10 2 11 2 11 2 11 3 13 3 53 3 53 3 53 4 53 4 55 4 5;run;  /* Solution #1: Proc Tabulate */ data dummy;input var1 var2 var3;cards;0 0 01 1 12 2 23 3 34 4 45 5 5;run; proc format;	value var_fmt		0="N/A"		1="Very Poor"		2="Poor"		3="Average"		4="Good"		5="Very Good"		;run;quit; data joined;      set test(in=is_valid) dummy;      if is_valid then valid=1;run;  %macro doit;%DO I=1 %TO 3;proc tabulate data=joined format=8.;    class var&I;    var valid;    tables var&I, valid=' '*n=' ' 					/ rts=22 misstext='0';     title 'Title Here';    format var&I var_fmt.;    label var&I="Var&I Label Here";run; %END;%mend; %doit;   /* Solution #2: Proc Summary + Proc Freq Combo */ proc format;	value var_fmt		.="Missing"		0="N/A"		1="Very Poor"		2="Poor"		3="Average"		4="Good"		5="Very Good"		;run;quit; proc summary nway completetypes missing;   class var1--var3 / preloadfmt;   format var1--var3 var_fmt.;   output out=count(drop=_type_);run; proc freq data=count;   tables var1--var3 / list nocum;   format var1--var3 var_fmt.;   weight _freq_ / zeros;run;`