Return to Homepage

Goto the Tip of the Month Archive

Other interesting pages ...
LinkedIn Profile
SAS Cheat Sheet
Useful SAS Code
Full SAS Example
Basic Statistics
Contact Information

SAS Tip of the Month
September 2013
(for SAS and WPS)

Last month we looked at importing some data from Excel, this month we are going to look at the converse, exporting some data to Excel.

From the start, there are a number of ways of doing this but I am just looking at the basic, put the data out to a sheet with variable names in the first row. There are ways to format cells, color code cells or rows, and a number of other things, but that is beyond this tip.

First some data:

   data student;
      length name $20 sex $1 age 8;
      input name $ sex $ age;
   cards;
   Alexandra F 15
   Bailey    F 16
   Blake     M 18
   Caroline  F 16
   Dominic   M 13
   Jose      M 17
   Justin    M 18
   Melanie   F 15
   Oliver    M 15
   Sydney    F 14
   ;
   run;

Once we run this dataset you will see that their are three variables and ten observations.

How do we get this into Excel? Last month we looked at PROC IMPORT, well there is a related procedure called PROC EXPORT for getting the data out. The basic syntax for the use of the procedure is:

   PROC EXPORT DATA=dataset_name FILE="filename" DBMS=EXCEL REPLACE;
      SHEET="sheet_name";
   RUN;

where

  • filename -- directory and name for the Excel file being created
  • dataset_name -- dataset being moved to the Excel sheet
  • sheet_name -- name of sheet being created (defaults to Sheet1 if not present)

The DBMS=EXCEL option tells SAS that the data being created will be in an Excel spreadsheet, while the REPLACE option is necessary if you want to overwrite an existing sheet (too many imports of data have failed because the programmer assumed any dataset of the same name would be automatically overwrittern when it is not!).

There are other statements that can be used including MIXED and RANGE, but these are beyond the example used here -- consult the SAS User Documentation for the use of these statements.

With this knowledge, lets export the data to the Excel file:

   PROC EXPORT DATA=student FILE="C:\TEMP\Student.xls" DBMS=EXCEL REPLACE;
      SHEET="StudentDemographics";
   RUN;

Once this is run, the Excel spreadsheet with the data from the dataset is in the file Student.xls in the tab StudentDemographics.

One thing to note using this method, the variable names are sent to the first row of the spreadsheet -- it is not possible to send variable label text using this method.

I hope this has been helpful. See you in October!

________________________________
Updated September 2, 2013