« Review of SAS Course: Mining Textual Data Using SAS Text Miner for SAS9 | Main | Duct tape does everything »

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

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 1
0 2 1
0 2 1
1 2 1
1 2 1
1 3 1
3 3 5
3 3 5
3 3 5
3 4 5
3 4 5
5 4 5
;
run;

Solution #1: Proc Tabulate (http://support.sas.com/techsup/technote/ts278.html)

data dummy;
input var1 var2 var3;
cards;
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 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 (http://groups.google.ca/group/comp.soft-sys.sas/browse_thread/thread/eba82d9c5c37bb71?hl=en)

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;

Code snippet here.

Posted on Tuesday, August 25, 2009 by Registered CommenterJared in , , , | CommentsPost a Comment

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>