Previous Page | Next Page

The SQL Procedure

Example 15: Counting Missing Values with a SAS Macro


Procedure feature:

COUNT function

Table: SURVEY

This example uses a SAS macro to create columns. The SAS macro is not explained here. See SAS Macro Language: Reference for information on SAS macros.


Input Table

 Note about code
data survey;
  input id $ diet $ exer $ hours xwk educ;
  datalines;
1001 yes yes 1 3 1
1002 no  yes 1 4 2
1003 no  no  . . .n
1004 yes yes 2 3 .x
1005 no  yes 2 3 .x
1006 yes yes 2 4 .x
1007 no  yes .5 3 .
1008 no  no  . .  .
;

Program

 Note about code
options nodate pageno=1 linesize=80 pagesize=60;
 Note about code
 %macro countm(col);
    count(&col) "Valid Responses for &col",
 Note about code
   nmiss(&col) "Missing or NOT VALID Responses for &col",
 Note about code
   count(case
            when &col=.n  then "count me"
            end) "Coded as NO ANSWER for &col",
   count(case
            when &col=.x  then "count me"
            end) "Coded as NOT VALID answers for &col",
   count(case
            when &col=.  then "count me"
            end) "Data Entry Errors for &col"
%mend;
 Note about code
proc sql;
   title 'Counts for Each Type of Missing Response';
   select count(*)  "Total No. of Rows",
          %countm(educ)
      from survey;

Output: Listing

                    Counts for Each Type of Missing Response                   1

                                 Missing            Coded as
                                  or NOT  Coded as       NOT      Data
             Total      Valid      VALID        NO     VALID     Entry
            No. of  Responses  Responses    ANSWER   answers    Errors
              Rows   for educ   for educ  for educ  for educ  for educ
          ------------------------------------------------------------
                 8          2          6         1         3         2

Previous Page | Next Page | Top of Page