5 Easy Ways to Import Excel into SAS Enterprise

Importing Excel Files Directly Using PROC IMPORT

The most straightforward method to import an Excel file into SAS Enterprise Guide is through the use of PROC IMPORT. This procedure facilitates data importation without requiring extensive coding, making it ideal for users of all skill levels.

• Launch SAS Enterprise Guide.
• Go to the Data menu, select Import Data, and then choose Microsoft Excel.
• Navigate to your Excel file in the dialog box that appears.
• Click Next and review the settings for data mapping, worksheet selection, and data handling options.
• When settings are correct, click Finish to import the data.

This method generates a SAS program code automatically, which you can view, edit, and save for future reuse. Here's an example of how the generated SAS code might look:

PROC IMPORT OUT= WORK.Imported_data
    DATAFILE= "path/to/your/excel/file.xlsx"
    DBMS=EXCEL REPLACE;
    RANGE="Sheet1$";
    GETNAMES=YES;
RUN;

⚠️ Note: Always ensure that the Excel file is closed during the import process to avoid data inconsistencies.

Utilizing SAS ODBC

For advanced users seeking more control, SAS offers the Open Database Connectivity (ODBC) method:

• Create an ODBC data source for your Excel file:
  • Access the ODBC Data Source Administrator on your machine.
  • Add a new User DSN, select Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb).
  • Point to your Excel file and complete the setup.
• In SAS Enterprise Guide, write or insert the following code:

  
  PROC SQL;
      CONNECT TO ODBC (DSN=your_excel_dsn_name);
      CREATE TABLE work.imported_data AS
      SELECT * FROM CONNECTION TO ODBC
      (SELECT * FROM `Sheet1$`);
  DISCONNECT FROM ODBC;
  QUIT;
  

This technique leverages SAS's SQL capabilities to fetch data from an external source, in this case, an Excel file through ODBC. Remember to replace 'your_excel_dsn_name' with the actual DSN name you created.

Using SAS Macro to Automate Import

If you regularly import data from Excel, consider automating the process with a SAS macro:

%MACRO import_excel(data_file, out_table);
PROC IMPORT OUT= &out_table
    DATAFILE= "&data_file"
    DBMS=EXCEL REPLACE;
    RANGE="Sheet1$";
    GETNAMES=YES;
RUN;
%MEND import_excel;

%import_excel(path/to/excel.xlsx, imported_data);

This macro simplifies the process by allowing you to specify the Excel file path and the output SAS data set name as parameters. Here's how to use it:

• Define the macro at the top of your SAS program.
• Call the macro with the file path and desired output table name.

✅ Note: Macros are highly useful for repetitive tasks and can save significant time over manual imports.

Importing from Multiple Sheets

When your Excel workbook contains multiple sheets, use a data step and PROC IMPORT to loop through each sheet:

%let sheets = Sheet1 Sheet2 Sheet3;

%macro import_all_sheets(sheets);
    %do i = 1 %to %sysfunc(countw(&sheets));
        %let sheet = %scan(&sheets, &i);
        PROC IMPORT OUT= work.import_&sheet
            DATAFILE= "path/to/excel/multiple.xlsx"
            DBMS=EXCEL REPLACE;
            RANGE="&sheet$";
            GETNAMES=YES;
        RUN;
    %end;
%mend;

%import_all_sheets(&sheets);

This macro iterates through each sheet in the specified workbook, creating a separate data set for each.

Handling Complex Excel Data with PROC TRANSPOSE

Sometimes, Excel data might not be in a straightforward, rectangular format. Here's how you can handle such data:

• Import the data normally.
• Use PROC TRANSPOSE to reformat the data:

  
  PROC IMPORT OUT= work.imported_complex
      DATAFILE= "path/to/excel/complex.xlsx"
      DBMS=EXCEL REPLACE;
      RANGE="ComplexData$";
      GETNAMES=YES;
  RUN;
  
  PROC TRANSPOSE DATA=work.imported_complex OUT=work.transposed_data PREFIX=Column_;
      BY GroupID;
      ID VarName;
      VAR Value;
  RUN;
  

This code transposes the data, converting column values into variables, which is particularly useful for survey data or any scenario where the structure of the data changes from row to column or vice versa.

🔍 Note: When dealing with complex data, always inspect the dataset structure before deciding on the transformation method to ensure data integrity.

Importing Excel files into SAS Enterprise Guide can range from simple, direct methods like PROC IMPORT to more sophisticated techniques that require deeper SAS knowledge. Each approach offers different levels of control, automation, and data manipulation capabilities. Whether you're a beginner or an advanced user, mastering these import methods can significantly enhance your data analysis workflow within SAS.

Can I import multiple Excel files at once?

+

Yes, you can use macros to loop through multiple Excel files, importing each one into separate data sets or appending them into one.

What should I do if my Excel file has headers?

+

Set the GETNAMES=YES option in PROC IMPORT or specify the row where headers start to ensure SAS correctly assigns variable names from your headers.

How do I handle date formats when importing Excel data?

+

Use the FORMAT= option within the PROC IMPORT statement to specify date formats, or use data step programming to convert the date strings to SAS dates.

What if my Excel data contains special characters or blank cells?

+

SAS can handle special characters; however, ensure your SAS session uses the correct encoding. For blank cells, SAS will treat them as missing values by default, which you can change with data step or PROC SQL.

Can I import an Excel file with more than 65,536 rows?

+

Yes, modern versions of Excel support over a million rows, and SAS can handle large datasets. Ensure your SAS environment has enough memory and consider using data step programming or PROC IMPORT with appropriate DBMS options for optimization.