Cornell University Cornell University CISER

CISER Computing

How Can I Export the Output From SAS To an Excel File?

Method A: The QUICK EASY WAY (one-way tables only)

IF your output is relatively short and involves only one-way tables:

  • Save your output in html format:
    • Tools→Options→Preferences→ choose the "Results" tab and check the box that says "HTML".
  • Run your Procedure
  • Go to the "Results Viewer" to view the html output
  • Copy and Paste the data cells from the Results Viewer to an Excel table!

Method B: Output your results as SAS data sets and Export to Excel

First, create an output dataset from the results of your frequency distribution. Here are examples of how to output your procedure results into a SAS data set:

  • For a one way table for a single variable ("v1freq" is the output dataset which will contain the frequency distribution for the "v1" variable)
    PROC FREQ DATA = mydata;
    TABLES v1 /OUT = v1freq;
    RUN;
  • For multiple one way tables you can use ODS (Output Delivery System) to create separate output datasets for the frequency distribution of each variable:
    • In the example below "mydatA" is a dataset in my WORK library.
    • The ODS output statement will create one dataset for each of the variables in the tables statement: "freqs" (which will contain the "name" variable) and "freqs1" which will contain the "v1" variable.
      ODS TRACE ON;
      PROC FREQ DATA = mydatA;
      TABLES name v1;
      ODS OUTPUT OneWayFreqs(match_all)=freqs;
      RUN;
      ODS TRACE OFF;

Second, export the SAS data set to Excel:

  • Assuming the output SAS data set is stored in the mydat library:
    • Option 1:
      • From the SAS menu, click
        • File→Export Data→library="mydat", member=dataset name
        • Check "Standard Data Source" and choose your version of Excel from the picklist provided, choose Next→Enter the location where you wish to save the Excel file and name the file.
        • Choose whether you want to save a Proc Export file as described in the next window (optional) choose Finish.
    • Option 2:
      • From the mydat library in the Explorer Window, right-click on the output SAS data set file, then choose View in Excel.

Method C: Output your results using ODS HTML BODY= statement

The ODS HTML BODY allows the user to save the results as an html file. Usually, the filename extension assigned to the file is .HTML. However, the user can opt to assign a .XLS extension so it can be read in Excel (although .HTML files can be read directly into Excel as well).

ODS HTML BODY = 'u:\thesis\v1.xls';
PROC FREQ DATA=mydata;
TABLES name v1;
ODS OUTPUT OneWayFreqs(match_all)=freqs;
RUN;
ODS HTML CLOSE;