/ 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).
Expand |
Embed | Plain Text
/* 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 */ 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 */ 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;
Comments
Subscribe to comments
You need to login to post a comment.

Solution #2 is not CPU efficient. It works fine the small TEST dataset, but if you have many vars or observations, consider creating a macro do loop just like in solution #1.
When I ran the solution #2 against a dataset with 10 vars and 200 observations, it took about 8 minutes to run and created a dataset of around 6.8 million records! Ouch!