3 Ways to Import Multiple Excel Sheets into SAS

In today's data-driven world, managing and analyzing large datasets is crucial for any business or research organization. One common scenario involves importing multiple Excel sheets into SAS for further analysis. Here are three efficient methods to accomplish this task:

Method 1: Using the SAS EXCEL Engine

The SAS Excel Engine provides a straightforward way to import multiple sheets from an Excel file. Here’s how you can do it:

1. Open SAS Studio or SAS Enterprise Guide: Ensure you have SAS software installed and open your preferred interface.
2. Set the Library: Assign a libref to the Excel file.

libname myexcel “path/to/your/ExcelFile.xlsx” engine=excel;
  

📌 Note: The path to the Excel file should be absolute or correctly mapped within SAS.

3. Import Multiple Sheets: Use PROC IMPORT to read all sheets from the Excel file.

proc import out=work.all_sheets
            data=myexcel
            dbms=excel replace;
            sheet=“*”;
run;
  

This will import all sheets from your Excel file into SAS with a single command. However, this approach might not preserve sheet names or might append sheets as observations if not configured properly.

4. Data Step for Dynamic Import: For more control, you can use a data step to loop through sheet names:

data null;
    length sheet_name $20;
    do until(eof);
        set myexcel.all end=eof;
        call symput(‘sheet’,sheet_name);
        call execute(‘%nrstr(%imp_sas(sheet_name);)’);
    end;
run;
%macro imp_sas(sh_name);
    proc import datafile=“path/to/your/ExcelFile.xlsx”
                out=work.&sh_name
                dbms=excel replace;
                sheet=“&sh_name”;
    run;
%mend imp_sas;
  

Method 2: Using PROC SQL to Import Multiple Sheets

SAS’s SQL procedures offer an alternative method for handling multiple sheets:

• Initialize the Excel library:

libname xlimport “path/to/your/ExcelFile.xlsx” engine=excel;
  

• Query the Excel file to get all sheet names:

proc sql;
    create table sheet_names as
    select memname
    from dictionary.tables
    where libname = ‘XLIMPORT’;
quit;
  

• Import each sheet into SAS:

data null;
    set sheet_names;
    call execute(’
    proc import datafile=“path/to/your/ExcelFile.xlsx”
                out=work.’ || memname || ‘
                dbms=excel replace;
                sheet=“’ || memname || ‘”;
    run;
    ‘);
run;
  

Method 3: Using Python’s Pandas with SAS

If you prefer Python for its data manipulation capabilities, integrating SAS with Python can be a powerful approach:

1. Prepare Python Environment: Ensure you have Python with pandas installed and the SASPy package for SAS connectivity.
2. Read Multiple Sheets with Pandas:

import pandas as pd
import saspy



xl_file = pd.ExcelFile(‘file_path.xlsx’)
df_list = [pd.read_excel(xl_file, sheet_name=sheet) for sheet in xl_file.sheet_names]
  

3. Write Each DataFrame to SAS: Use SASPy to send the DataFrame back to SAS:

sas = saspy.SASsession()
for df, name in zip(df_list, xl_file.sheetnames):
    sas.df2sd(df, table=name.replace(’ ‘, ‘’))
  

Here, each sheet is converted to a SAS dataset, with underscores replacing spaces in the dataset names for compatibility.

4. Clean Up in SAS: After importing in Python, you might need to run additional steps in SAS to ensure the datasets are formatted or appended as needed.

The methods described above provide varying levels of control and automation when importing multiple Excel sheets into SAS. Each approach has its merits:

• The EXCEL Engine method is simple but might require additional steps for organizing data.
• PROC SQL offers dynamic control over importing sheets but can be more complex to set up.
• Using Python’s Pandas provides a bridge for those comfortable with Python, allowing for preprocessing of data before sending to SAS.

How does SAS handle headers in Excel sheets?

+

SAS typically uses the first row of each Excel sheet as headers by default. However, you can customize this behavior using the “getnames=yes” or “getnames=no” options in PROC IMPORT to either include or exclude the first row as headers.

Can I import sheets selectively from an Excel file?

+

Yes, you can specify which sheets to import in all methods. For the EXCEL Engine, you can list specific sheets or use wildcards. With Python, you can selectively choose sheets to read with pandas.read_excel(), and then only those sheets will be processed by SAS.

What if my Excel file has mixed data types in a column?

+

SAS will attempt to import data based on the majority type within each column. If the column contains both numeric and string data, SAS might treat the column as character data. You might need to use SAS data step or functions like INPUT() or PUT() for type conversion after import.

What are the limitations when using Python for importing Excel into SAS?

+

Limitations include potential issues with large datasets due to memory constraints, the need to manage Python and SAS environments, and potential format incompatibilities between Python’s pandas and SAS.

What are the best practices for managing multiple datasets in SAS?

+

Best practices include:

• Using PROC APPEND to concatenate datasets if you need all data in one place.
• Storing datasets in a library to manage multiple datasets efficiently.
• Creating indexes for faster data retrieval.
• Using the SET statement with BY to handle multiple files in a data step.
• Automating data checks and validations post-import.