Count the different responses to each question in a set of questions from a survey. Consolidate this information into a report. Each observation in the data set contains one participant’s responses to all the survey questions.
Town Survey Results Number of Participants: 482 -------------------------------------------------- ------------------------------------ | | Strongly | | | | Strongly | Total | | |Disapprove|Disapprove| Neutral | Approve | Approve |Responses | | |----------+----------+-------- --+----------+----------+----------| | | N |Row %| N |Row %| N |Row %| N |Row %| N |Row %| N |Row %| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |Survey Question | | | | | | | | | | | | | |------------------| | | | | | | | | | | | | |1. Road | | | | | | | | | | | | | |Maintenance | 5| 1.2| 134| 31.7| 115| 27 .2| 143| 33.8| 26| 6.1| 423|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |2. Parks Upkeep | 4| 1.0| 86| 21.0| 93| 22 .7| 195| 47.6| 32| 7.8| 410|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |3. Snowplowing | 76| 17.3| 169| 38.5| 86| 19 .6| 93| 21.2| 15| 3.4| 439|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |4. Sheriff | | | | | | | | | | | | | |Patrolling | 0| 0| 94| 22.3| 111| 26 .4| 180| 42.8| 36| 8.6| 421|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |5. Ordinance | | | | | | | | | | | | | |Enforcement | 20| 4.6| 156| 35.9| 121| 27 .9| 107| 24.7| 30| 6.9| 434|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |6. Town Office | | | | | | | | | | | | | |Hours | 1| 0.2| 80| 19.1| 124| 29 .7| 177| 42.3| 36| 8.6| 418|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |7. Community | | | | | | | | | | | | | |Events | 1| 0.2| 38| 9.2| 51| 12 .3| 204| 49.4| 119| 28.8| 413|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |8. Youth Programs | 2| 0.5| 50| 11.7| 45| 10 .5| 201| 47.1| 129| 30.2| 427|100.0| |------------------+----+-----+----+-----+----+--- --+----+-----+----+-----+----+-----| |9. Senior Services| 3| 0.7| 94| 22.2| 121| 28 .6| 167| 39.5| 38| 9.0| 423|100.0| -------------------------------------------------- ------------------------------------ |
Data Set | TOWNSURVEY |
Featured Steps | PROC TRANSPOSE PROC TABULATE |
Featured Step Statements and Options | PROC TABULATE
|
Formatting Features | TABLE statement: MISSTEXT= option TABLE statement: RTS= option when sending output to the LISTING destination |
Additional Features | Macro programming SAS file input/output functions |
Related Technique | PROC REPORT:
|
A Closer Look | Reshaping Data Working with Missing Class Variable Values in PROC TABULATE |
Other Examples That Use This Data Set | Example 2.8 |
This report shows the results of a survey submitted to town residents regarding their opinions of town services. The survey participants were asked to rate nine services on a scale from 1 to 5, with 1 being the least favorable and 5 being the most favorable.
Each observation in the TOWNSURVEY data set represents the responses to the nine questions for one participant. The survey also included a comment line, but this example does not include this comment variable. See Example 2.8 for a program that processes the comments.
To construct this crosstabulation of service and opinion of service, you must have a data set that contains a variable for type of service and a variable for the respondent’s evaluation of the service. This will allow the five possible responses to be treated as five classifications by PROC TABULATE.
PROC TRANSPOSE reshapes the data into a new data set that contains the variables for type of service and for evaluation of the service. Each observation in the transposed data set contains the response to one question for one survey participant. The transposed data set that PROC TABULATE analyzes contains nine times as many observations as the original data set, because there are nine questions.
Open code macro language statements use SAS functions to open and close the analysis data set and determine the number of nondeleted observations in the analysis data set. This value is inserted in the title. For more information on working with SAS functions, see Example 3.10.
Figure 3.9a shows the survey form submitted to town residents.
PROC REPORT can produce a report similar to one produced by PROC TABULATE as shown in Figure 3.9b. The following PROC REPORT step defines QUESTION as a grouping variable. This definition causes QUESTION to act as the class variable in the row dimension. The program defines CHOICE as an across variable. This definition causes CHOICE to act as a class variable in the column dimension.
It’s not quite as easy to specify row percentages in PROC REPORT as it is in PROC TABULATE. To do this in PROC REPORT requires programming statements in a COMPUTE block. If you nest the PCTN statistic underneath CHOICE, the percentages will have as their denominator the total frequency count of the response column.
Town Survey Results Number of Participants: 482 ------------------------------------------------------------------------------------------ -------- | | | Strongly Strongly | | Disapprove Disapprove Neutral Approve Approve Total| |Survey Question N Pct N Pct N Pct N Pct N Pct N| |----------------------------------------------------------------------------------------- -------| |1. Road Maintenance | 5| 1.2| 134| 31.7| 115| 27.2| 143| 33.8| 26| 6 .1| 423| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |2. Parks Upkeep | 4| 1.0| 86| 21.0| 93| 22.7| 195| 47.6| 32| 7 .8| 410| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |3. Snowplowing | 76| 17.3| 169| 38.5| 86| 19.6| 93| 21.2| 15| 3 .4| 439| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |4. Sheriff Patrolling | .| 0.0| 94| 22.3| 111| 26.4| 180| 42.8| 36| 8 .6| 421| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |5. Ordinance Enforcement| 20| 4.6| 156| 35.9| 121| 27.9| 107| 24.7| 30| 6 .9| 434| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |6. Town Office Hours | 1| 0.2| 80| 19.1| 124| 29.7| 177| 42.3| 36| 8 .6| 418| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |7. Community Events | 1| 0.2| 38| 9.2| 51| 12.3| 204| 49.4| 119| 28 .8| 413| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |8. Youth Programs | 2| 0.5| 50| 11.7| 45| 10.5| 201| 47.1| 129| 30 .2| 427| |------------------------+-----+------+-----+------+-----+------+-----+------+-----+------ +------| |9. Senior Services | 3| 0.7| 94| 22.2| 121| 28.6| 167| 39.5| 38| 9 .0| 423| ------------------------------------------------------------------------------------------ -------- |
The following program reshapes the input data set the same way as in the main example. Note that the default split character (/) has been inserted in the RESPONSE format. This was not needed in the PROC TABULATE step.
A Closer Look |
The original input data set has all the information that you need to make the crosstabular report, but PROC TABULATE cannot process the information in that format to produce the report.
PROC TRANSPOSE rearranges the data so that each observation in the new data set contains the variable SURVEYID, a variable for question number (QUESTION), and a variable for question response (CHOICE). PROC TABULATE can process this structure to create the desired crosstabular report.
If you submit the following PROC TABULATE step using the original data set, you will obtain frequency counts and percentages, but they will not be shown in a crosstabular fashion. The questions and their responses define the rows. The two columns of the report are the N and PCTN statistics.
proc tabulate data=townsurvey missing; class q1 q2 q3 q4 q5 q6 q7 q8 q9; table (q1 all) (q2 all) (q3 all) (q4 all) (q5 all) (q6 all) (q7 all) (q8 all) (q9 all), n*f=4. pctn*f=5.1; run;
The first page of this report is shown in Figure 3.9c. Note that the MISSING option on the PROC TABULATE statement forces inclusion of missing values as valid categories. If you omit the MISSING option, only observations where all question responses were nonmissing are included in the report. Out of a total of 482 respondents, 146 responded to all nine questions. For more information on how PROC TABULATE works with missing values, see “Working with Missing Class Variable Values in PROC TABULATE” later in this section.
Town Survey Results Number of Participants: 482 ------------------------------------- | | N |PctN | |------------------------+----+-----| |q1 | | | |------------------------| | | |. | 59| 12.2| |------------------------+----+-----| |1 | 5| 1.0| |------------------------+----+-----| |2 | 134| 27.8| |------------------------+----+-----| |3 | 115| 23.9| |------------------------+----+-----| |4 | 143| 29.7| |------------------------+----+-----| |5 | 26| 5.4| |------------------------+----+-----| |All | 482|100.0| |------------------------+----+-----| |q2 | | | |------------------------| | | |. | 72| 14.9| |------------------------+----+-----| |1 | 4| 0.8| |------------------------+----+-----| |2 | 86| 17.8| |------------------------+----+-----| |3 | 93| 19.3| |------------------------+----+-----| |4 | 195| 40.5| |------------------------+----+-----| |5 | 32| 6.6| |------------------------+----+-----| |All | 482|100.0| |------------------------+----+-----| |q3 | | | |------------------------| | | |. | 43| 8.9| |------------------------+----+-----| |1 | 76| 15.8| |------------------------+----+-----| |2 | 169| 35.1| |------------------------+----+-----| |3 | 86| 17.8| |------------------------+----+-----| |4 | 93| 19.3| |------------------------+----+-----| |5 | 15| 3.1| |------------------------+----+-----| |All | 482|100.0| |------------------------+----+-----| |q4 | | | |------------------------| | | |. | 61| 12.7| |------------------------+----+-----| |2 | 94| 19.5| |------------------------+----+-----| |3 | 111| 23.0| |------------------------+----+-----| |4 | 180| 37.3| |------------------------+----+-----| |5 | 36| 7.5| |------------------------+----+-----| |All | 482|100.0| |------------------------+----+-----| |q5 | | | |------------------------| | | |. | 48| 10.0| |------------------------+----+-----| |1 | 20| 4.1| |------------------------+----+-----| |2 | 156| 32.4| ------------------------------------- |
PROC TRANSPOSE restructures the data so that values that were stored in one observation are written to one variable. In this example, the values of the variables Q1 through Q9 are transposed and saved in the variable CHOICE. You can specify which variables you want to transpose. The following paragraphs illustrate how PROC TRANSPOSE reshapes the data in this example.
When you transpose with BY processing, as this example does, you create from each BY group one observation for each variable that you transpose. In this example, SURVEYID is the BY variable. Each observation in the input data set is a BY group, because the value of SURVEYID is unique for each observation.
This example transposes nine variables, Q1 through Q9. Therefore, the output data set contains nine observations from each BY group. These observations correspond to the nine questions per survey participant.
Figure 3.9d uses the first two observations in the input data set to illustrate the transposition.
The TOWNSURVEY data set contains some missing responses. The counts in the “Total” column of the main report are the frequencies of nonmissing values. The counts, therefore, are less than 482, the total number of survey participants that was printed in the title.
By default, when you define a variable as a class variable, PROC TABULATE omits observations that have missing values for that class variable. This rule applies to all TABLE statements in the PROC TABULATE step, even if you do not include the variable in any of the TABLE statements.
To compute statistics for categories that are defined by missing values, add the MISSING option to the PROC TABULATE statement. When you add MISSING to the PROC TABULATE step in the main program of this example, PROC TABULATE adds a new column to the report that contains the N and ROWPCTN statistics for missing responses. This is shown in Figure 3.9e. The new column is the one labeled “No response.”
Town Survey Results Number of Participants: 482 ------------------------------------------------------------------------------------------ ------- | | No | Strongly | | | | Strongly | Total | | | response |Disapprove|Disapprove| Neutral | Approve | Approve |Responses | | |----------+----------+----------+----------+----------+----------+----------| | | N |Row %| N |Row %| N |Row %| N |Row %| N |Row %| N |Row %| N |Row %| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |Survey Question | | | | | | | | | | | | | | | |------------------| | | | | | | | | | | | | | | |1. Road | | | | | | | | | | | | | | | |Maintenance | 59| 12.2| 5| 1.0| 134| 27.8| 115| 23.9| 143| 29.7| 26| 5.4| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |2. Parks Upkeep | 72| 14.9| 4| 0.8| 86| 17.8| 93| 19.3| 195| 40.5| 32| 6.6| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |3. Snowplowing | 43| 8.9| 76| 15.8| 169| 35.1| 86| 17.8| 93| 19.3| 15| 3.1| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |4. Sheriff | | | | | | | | | | | | | | | |Patrolling | 61| 12.7| 0| 0| 94| 19.5| 111| 23.0| 180| 37.3| 36| 7.5| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |5. Ordinance | | | | | | | | | | | | | | | |Enforcement | 48| 10.0| 20| 4.1| 156| 32.4| 121| 25.1| 107| 22.2| 30| 6.2| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |6. Town Office | | | | | | | | | | | | | | | |Hours | 64| 13.3| 1| 0.2| 80| 16.6| 124| 25.7| 177| 36.7| 36| 7.5| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |7. Community | | | | | | | | | | | | | | | |Events | 69| 14.3| 1| 0.2| 38| 7.9| 51| 10.6| 204| 42.3| 119| 24.7| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |8. Youth Programs | 55| 11.4| 2| 0.4| 50| 10.4| 45| 9.3| 201| 41.7| 129| 26.8| 482|100.0| |------------------+----+-----+----+-----+----+-----+----+-----+----+-----+----+-----+---- +-----| |9. Senior Services| 59| 12.2| 3| 0.6| 94| 19.5| 121| 25.1| 167| 34.6| 38| 7.9| 482|100.0| ------------------------------------------------------------------------------------------ ------- |
The total N is now 482 for all questions, and the row percentages are now based on a denominator of 482, not on the total number of nonmissing responses for a question.
The PROC TABULATE statement with the MISSING option looks as follows:
proc tabulate data=townsurvey2 missing;
The output from the PROC TABULATE step that includes the MISSING option on the PROC TABULATE statement is shown in Figure 3.9e.
PROC REPORT reference, usage information, and additional examples. See “The REPORT Procedure” in the “Procedures” section of Base SAS 9.1 Procedures Guide.
PROC TABULATE reference, usage information, and additional examples. See “The TABULATE Procedure” in the “Procedures” section of Base SAS 9.1 Procedures Guide.
PROC TRANSPOSE reference, usage information, and additional examples. See “The TRANSPOSE Procedure” in the “Procedures” section of Base SAS 9.1 Procedures Guide.
SAS File Input/Output Functions. See “Functions and CALL Routines” in SAS 9.1 Language Reference: Dictionary.
SAS Macro Programming. In the “Understanding and Using the Macro Facility” section of SAS 9.1 Macro Language: Reference, see “Macro Variables” and “Using SAS Language Functions in the DATA Step and Macro Facility” in the “Interfaces with the Macro Facility” section. Also see “Macro Language Dictionary.”
3.147.72.74