September 2004 Technical Tip Sending SAS proc output to a SAS data set

You use SAS procs such as FREQ to create reports for your client, but your client wants the results in an Excel spreadsheet. You know you can export a SAS dataset to Excel, but how do you put SAS proc output to a SAS dataset? The answer is ODS, or Output Delivery System. ODS was first made available with version 7 of the SAS System. With ODS, every result produced by a procedure has a name. Once you know the name, you can manipulate it as you would any SAS dataset.

First, you'll need to use the ODS TRACE statement to determine the name of the SAS output data set being created by your proc:

ods trace on;
proc freq data=test;
tables first * second;
run;
ods trace off;

Check the SAS Log to determine the name of the SAS output data set containing your report data:

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Data Name:
Path:       Freq.first_by_second.CrossTabFreqs
-------------

Once you've determine the name of the output you want, you use the ODS OUTPUT statement to send that output to a SAS dataset:

* ods trace on;
proc freq data=test;
tables first * second;
ods output crosstabfreqs = step1;
run;
* ods trace off;

The SAS Log will show you that a new SAS data set has indeed been created. You can get an idea of what ODS did for you by simply viewing the new data set with VIEWTABLE or PROC PRINT. You'll likely need to run PROC CONTENTS on the data set in order to determine the true column names. You can then use other procs (such as PROC TRANSPOSE) or additional DATA steps to manipulate the results until the final SAS data set is ready for exporting to Excel.

For a complete example, see http://www.caliberdt.com/tips/odsdemo.txt

Want to know more about SAS? Give us a call! You can count on Caliber Data Training for top quality mainframe and/or internet programming education.


Go to the articles index. Written by Bill Qualls. Copyright © 2004 by Caliber Data Training 800.938.1222