Posted By

webonomic on 08/25/09


Tagged

values proc Summary tabulate nonexistent


Versions (?)

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


 / Published in: SAS
 

URL: http://jaredprins.squarespace.com/blog/2009/8/25/show-all-possible-values-in-table-regardless-of-whether-or-n.html

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).

  1. /* Given the following Data: */
  2.  
  3. data test;
  4. input var1 var2 var3;
  5. datalines;
  6. . 2 .
  7. . 2 1
  8. 0 2 1
  9. 0 2 1
  10. 1 2 1
  11. 1 2 1
  12. 1 3 1
  13. 3 3 5
  14. 3 3 5
  15. 3 3 5
  16. 3 4 5
  17. 3 4 5
  18. 5 4 5
  19. ;
  20. run;
  21.  
  22.  
  23. /* Solution #1: Proc Tabulate */
  24.  
  25. data dummy;
  26. input var1 var2 var3;
  27. cards;
  28. 0 0 0
  29. 1 1 1
  30. 2 2 2
  31. 3 3 3
  32. 4 4 4
  33. 5 5 5
  34. ;
  35. run;
  36.  
  37. proc format;
  38. value var_fmt
  39. 0="N/A"
  40. 1="Very Poor"
  41. 2="Poor"
  42. 3="Average"
  43. 4="Good"
  44. 5="Very Good"
  45. ;
  46. run;
  47. quit;
  48.  
  49. data joined;
  50. set test(in=is_valid) dummy;
  51. if is_valid then valid=1;
  52. run;
  53.  
  54. %macro doit;
  55. %DO I=1 %TO 3;
  56. proc tabulate data=joined format=8.;
  57. class var&I;
  58. var valid;
  59. tables var&I, valid=' '*n=' '
  60. / rts=22 misstext='0';
  61.  
  62. title 'Title Here';
  63. format var&I var_fmt.;
  64. label var&I="Var&I Label Here";
  65. run;
  66. %END;
  67. %mend;
  68.  
  69. %doit;
  70.  
  71.  
  72.  
  73. /* Solution #2: Proc Summary + Proc Freq Combo */
  74.  
  75. proc format;
  76. value var_fmt
  77. .="Missing"
  78. 0="N/A"
  79. 1="Very Poor"
  80. 2="Poor"
  81. 3="Average"
  82. 4="Good"
  83. 5="Very Good"
  84. ;
  85. run;
  86. quit;
  87.  
  88. proc summary nway completetypes missing;
  89. class var1--var3 / preloadfmt;
  90. format var1--var3 var_fmt.;
  91. output out=count(drop=_type_);
  92. run;
  93.  
  94. proc freq data=count;
  95. tables var1--var3 / list nocum;
  96. format var1--var3 var_fmt.;
  97. weight _freq_ / zeros;
  98. run;

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: webonomic on August 25, 2009

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!

You need to login to post a comment.