Cornell University Cornell University CISER

CISER Computing

How to Import an Excel File into SAS

Here are the steps to import an Excel file.

  1. Examine the content of your Excel file.

    A few guidelines:

    • It is ideal for the first row to contain the column header because by default this will be used as the variable names...

    • ... and that the data values begin in the second row.

    • As much as possible, the variable names (column header) must conform to the variable naming rules. The rules are:

      Variable names must begin with a letter or underscore. The rest can be any letter, number or underscore. They can also be up to 32 characters long.

      Please note, however, that SAS will automatically convert invalid characters in the variable names (column header) into an underscore when SAS imports the Excel file into a SAS data set.
    • As much as possible, the values of a variable must be of the same type. SAS uses the first 8 observations to determine the TYPE to assign to the imported column(s). In case of mixed-types, the type that is the most dominant in the first 8 observations will be applied. In case of a tie i.e., four (4) character and four (4) numeric variables, the numeric type takes precedence. Once imported, values that do not conform to the assigned type will be converted to missing values.

    • Make sure that rows immediately following the last observation and columns immediately following the last variable are not activated by deleting them so that SAS will not mistake them for additional observations or additional columns, respectively.
Excel image

In Example 1:

The first row of data is the column header. (recommended!)

The second row of data is the start of the data values. (recommended!)

The column names AGE OF RESPONDENT and SEX:CHAR FORMAT do not conform to the variable naming rules because of the presence of invalid characters - spaces in the former, and colon and space in the latter. (Not recommended!) You can rename this manually or let SAS do the renaming automatically. If you let SAS rename the variables automatically, the former variable will be renamed in the resulting SAS dataset as AGE_OF_RESPONDENT and the latter as SEX_CHAR_FORMAT.

The data values in each column are consistent in type. (recommended!)

Rows immediately following the last observation, and columns immediately following the last variable are not activated. (recommended!)

Example 2
Excel image

Example 2 is similar to Example 1 except that the second and fourth columns have mixed-type values. (Not recommended!)

Remember that SAS uses the first 8 observations to determine the TYPE to assign to the imported column(s). In case of mixed-types, the type that is the most dominant in the first 8 observations will be applied. In case of a tie i.e., four (4) character and four (4) numeric variables, the numeric type takes precedence.

Once imported, values that do not conform to the assigned type will be converted to missing values.

So..

When SAS imports column 2, because there is a tie in the first 8 observations i.e, four character values and four numeric values, SAS assigns a numeric type for this column. The character values will be converted to missing values.

When SAS imports column 4, because there are more character than numeric values in the first 8 observations, SAS assigns a character type for this column. The numeric values will be converted to missing values.

  1. Close the Excel File you want to import to avoid a file sharing violation!

  2. Open SAS, then click File > Import Data (this opens the Import Wizard).
Import Wizard image

  1. The default type is Microsoft Excel, accept the default by clicking Next. This opens the Connect to MS Excel dialog box.
Import Wizard image

  1. Click Browse, then locate the Excel file you want to import, then click Open. Suppose we want to import the excel file DEMO1.XLS located in the C:\SASWORKSHOP folder.
Import Wizard image

  1. Click OK (This opens the Select Table dialog box).
Import Wizard image

  1. Select the table you want to import.
    Note: If your Excel file has multiple tables (or worksheets), click the pull- down (see red arrow above) menu to see the list of worksheets in that Excel file.
  2. Click Options (this opens the Spreadsheet Options dialog box).
Import Wizard image

  1. Check/Uncheck appropriate boxes in the Spreadsheet Options dialog box , or accept the default, then click OK.

  2. Click Next (this opens the Select Library and Member dialog box).
Import Wizard image

  1. Click the Library pull-down menu (see red arrow) to select the library where you want to store the SAS data set to be created. Then enter the name of the SAS data set to be created (eg., demo1) in the Member box (see pink arrow).
Import Wizard image

  1. Click Next (this opens the Create SAS Statements dialog box).
Import Wizard image

Note 1:  PROC IMPORT is the SAS procedure used to import your Excel file into SAS. Click Browse to create a SAS program file that contains the PROC IMPORT statements used to import your Excel file. When you click Browse, you will be asked to specify the name and location of the SAS program file. (By default, SAS program files have a .SAS extension.) Then click Save.

Note 2:  If you want to import multiple tables from the same Excel file, saving the program file maybe the best option for you. Because you can open this program file in the Enhanced Editor, copy and paste the PROC IMPORT statement and then simply replace the name of the table in the SHEET = statement one by one with the names of the tables you want to import.

  1. Click Finish.