How do I get my SAS results into a data set?

A very common question in SAS is: I see the table in the output, but how do I get it into a dataset.

Generally, the purpose is to reformat the table or control when it gets printed.There are 3 steps to getting the output to a table.

  1. Run the proc with ODS TRACE ON (or refer to the documentation, ODS OUTPUT table names).
  2. Determine the NAME of the output object you want.
  3. Add in an ODS TABLE YOURTABLENAME=  statement to capture the table.

Here’s an example:

Problem:

How do I get the results from PROC FREQ with totals in to a SAS dataset?

Although the default OUT= option from PROC FREQ will generate the output data it will not generate the row and column totals, nor the overall total, but the ODS table will.

First, let’s take a look at the standard results from PROC FREQ.

Proc Freq Data=sashelp.class;

table age*sex/out=summary1;

Run;

Proc Print Data=Summary1;

Run;

The output:

The SAS System 21:38 Sunday, August 11, 2013 2

Obs Age Sex COUNT PERCENT

1 11 F 1 5.2632
2 11 M 1 5.2632
3 12 F 2 10.5263
4 12 M 3 15.7895
5 13 F 2 10.5263
6 13 M 1 5.2632
7 14 F 2 10.5263
8 14 M 2 10.5263
9 15 F 2 10.5263
10 15 M 2 10.5263
11 16 M 1 5.2632

If we then sandwich the PROC FREQ code between ODS TRACE ON and ODS TRACE OFF the following appears in the log:

7 ODS trace ON;
8 Proc Freq Data=sashelp.class;
9 table age*sex/out=summary1;
10 Run;

Output Added:
————-
Name: CrossTabFreqs
Label: Cross-Tabular Freq Table
Template: Base.Freq.CrossTabFreqs
Path: Freq.Table1.CrossTabFreqs
————-
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.SUMMARY1 has 11 observations and 4 variables.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.07 seconds
cpu time 0.01 seconds

From the log output we can see the table name is CrossTabFreqs and the code changes to:

ODS table CrossTabFreqs=summary2;

Proc Freq Data=sashelp.class;

table age*sex/out=summary1;

Run;

Proc print data=summary2;

run;

And the final output is below. Note that Age and Sex and missing and those will represent row/column totals. Also, the percentages are included in the default output.

The SAS System

Obs Table Age Sex _TYPE_ _TABLE_ Frequency Percent RowPercent ColPercent Missing
1 Table Age * Sex 11 F 11 1 1 5.263 50.000 11.1111 .
2 Table Age * Sex 11 M 11 1 1 5.263 50.000 10.0000 .
3 Table Age * Sex 11 10 1 2 10.526 . . .
4 Table Age * Sex 12 F 11 1 2 10.526 40.000 22.2222 .
5 Table Age * Sex 12 M 11 1 3 15.789 60.000 30.0000 .
6 Table Age * Sex 12 10 1 5 26.316 . . .
7 Table Age * Sex 13 F 11 1 2 10.526 66.667 22.2222 .
8 Table Age * Sex 13 M 11 1 1 5.263 33.333 10.0000 .
9 Table Age * Sex 13 10 1 3 15.789 . . .
10 Table Age * Sex 14 F 11 1 2 10.526 50.000 22.2222 .
11 Table Age * Sex 14 M 11 1 2 10.526 50.000 20.0000 .
12 Table Age * Sex 14 10 1 4 21.053 . . .
13 Table Age * Sex 15 F 11 1 2 10.526 50.000 22.2222 .
14 Table Age * Sex 15 M 11 1 2 10.526 50.000 20.0000 .
15 Table Age * Sex 15 10 1 4 21.053 . . .
16 Table Age * Sex 16 F 11 1 0 0.000 0.000 0.0000 .
17 Table Age * Sex 16 M 11 1 1 5.263 100.000 10.0000 .
18 Table Age * Sex 16 10 1 1 5.263 . . .
19 Table Age * Sex . F 01 1 9 47.368 . . .
20 Table Age * Sex . M 01 1 10 52.632 . . .
21 Table Age * Sex . 00 1 19 100.000 . . 0

You’ll notice that the table you see in the dataset does not have the same format as the output table displayed in the output window. Unfortunately, this is common in a number of procedures and you’ll need to reformat the data to get it to your desired output format.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s