topic import multiple Excel sheets into SAS in SAS Enterprise Guide

How do I import Excel data into SAS EG?

Daily1 — Wed, 20 Apr 2022 04:42:51 GMT

excel file name is Countries with multiple sheets name like us, france,japan, etc

i want multiple sheets on one dataset in sas eg

How do I import Excel data into SAS EG?

Re: How do I import Excel data into SAS EG?

SASKiwi — Wed, 20 Apr 2022 06:40:16 GMT

The easiest way is to use the File Import Wizard which you can access from the EG File menu. That will give you one SAS dataset per sheet, but you can easily combine combine the datasets afterwards.

Re: How do I import Excel data into SAS EG?

andreas_lds — Wed, 20 Apr 2022 06:59:08 GMT

The best way to solve problems like yours: drop excel, use csv files instead (and don't open csv files with excel, but with a text editor).

Use the libname statement to access the excel-file.
Get the names of the sheets from sashelp.vtable.
Combine the sheets using a data step with set, hope that all variable have the same metadata (from my experience this won't be the case in 100% of all excel files i had to process).
Write complex code fixing the issues caused by excel.

Example code:

/* crating an excel-file to demonstrate the steps 1-3 */
proc sort data=sashelp.cars out=work.cars;
   by Origin;
run;

option nobyline;

ods excel file="PATH\cars.xlsx" options(sheet_name="#byval1");

proc print data=work.cars noobs;
   by Origin;
run;

ods excel close;

/* 1) reading the excel-file */
libname nofun xlsx "PATH\cars.xlsx";

/* 2) Get the names of the sheets in the excel-file */
proc sql noprint;
   select cats('nofun.', MemName)
      into :SheetList separated by ' '
      from sashelp.vtable
         where upcase(LibName) = 'NOFUN';
quit;

/* 3) combine the sheets */
data work.combined;
   set &sheetList.;
run;

libname nofun clear;

Unfortunately even this didn't work as expected:

 93         data work.combined;
 94            set &sheetList.;
 ERROR: Variable Cylinders has been defined as both character and numeric.
 ERROR: Variable Cylinders has been defined as both character and numeric.
 95         run;
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.COMBINED may be incomplete.  When this step was stopped there were 0 observations and 14 variables.

I can't provide code for step 4, but i am sure that you can find something useful in the community.

import multiple Excel sheets into SAS

Daily1 — Wed, 20 Apr 2022 12:11:42 GMT

Hi All,

Can anyone help me to understand,

SAS : Importing multiple excel sheets in a single dataset

Regards

Re: import multiple Excel sheets into SAS

PaigeMiller — Wed, 20 Apr 2022 12:36:24 GMT

Search the SAS Communities for

import multiple excel sheets

This has been discussed many times.

Re: import multiple Excel sheets into SAS

ballardw — Wed, 20 Apr 2022 14:13:12 GMT

Basically you don't. You import all the sheets and then combine them. Hopefully.

You might be able to use a LIBNAME statement pointing to the spreadsheet, which would treat each sheet as a data set.

If you actually use proc import you may not be able to combine the data sets because each one imported separately may have different properties, such as length or type, for same named variables. Also things like multiple header rows can cause problems because the "header" information becomes variable values.

Re: import multiple Excel sheets into SAS

Sajid01 — Wed, 20 Apr 2022 23:56:11 GMT

Looks like this essentially a uplicate of yout other post https://communities.sas.com/t5/SAS-Enterprise-Guide/How-do-I-import-Excel-data-into-SAS-EG/m-p/808727#M40562

Re: import multiple Excel sheets into SAS

Kurt_Bremser — Thu, 21 Apr 2022 05:21:58 GMT

@Sajid01 wrote:

Looks like this essentially a uplicate of yout other post https://communities.sas.com/t5/SAS-Enterprise-Guide/How-do-I-import-Excel-data-into-SAS-EG/m-p/808727#M40562

Merged everything.

Re: import multiple Excel sheets into SAS

Patrick — Thu, 21 Apr 2022 08:32:22 GMT

@Daily1 wrote:

Hi All,

Can anyone help me to understand,

SAS : Importing multiple excel sheets in a single dataset

Regards

As others already wrote you need to think of each Excel sheet as a separate table and though you need to write code to combine these tables into one.

One of the challenges with Excel as a source: What column attributes you get in SAS (especially type and length) depends on the cell data in the source Excel sheet. So even if all your sheets got the same structure (the same columns) you still could face an issue with the data types and especially the lengths of the columns. Let's say in your first sheet there is a column A with the longest string being a single character then SAS will create a variable A with a length of $1 - but then if in your 2nd sheet in column A there is a string of 2 characters then for this sheet SAS will create a column with a length of $2. If you now combine the first SAS table with the 2nd on then SAS will create a target column with the length as found the first time in the source tables - which could be $1 and which then leads to data truncation (and only a Warning and not an Error in the SAS log).

To get around the length issue what you can do is combine the data via a SQL UNION Corr as with this syntax the length in the target table will be the longest length found in source. Below code generates such SQL syntax.

What I haven't addressed in below code is the case where for the "same" column the data type differs between the tables. That would require quite a bit more coding and just demonstrate that Excel isn't an ideal source for any ETL process.

/** create sample Excel Workbook with two sheets **/
%let path_on_disk=%sysfunc(pathname(work));
/*%let path_on_disk=c:\temp;*/

libname demo xlsx "&path_on_disk\demo.xlsx";
data demo.class_1;
  set sashelp.class;
run;
data demo.class_2;
  length sex $6;
  set sashelp.class;
  if sex='F' then sex='Female';
  else sex='Male';
run;
data demo.class_3;
  set sashelp.class;
run;
libname demo clear;

/** read data from all sheets in Excel Workbook and combine into a single table **/
libname demo xlsx "&path_on_disk\demo.xlsx";
/* create list of all sheets in the Excel Workbook */
proc sql;
  create table source_tables as
  select cats(libname,'.',memname) as source_table length=41
  from dictionary.tables
  where libname='DEMO'
  ;
quit;

/* generate code that concatenates the data into a new table */
filename codegen temp;
data _null_;
  file codegen;
  set source_tables end=last;
  if _n_=1 then 
    do;
      put 
        'proc sql;' /
        '  create table want as' /
        '  select * from ' source_table
        ;
    end;
  else
    do;
      put
        '  union corr all' /
        '  select * from ' source_table
        ;
    end;
  if last then
    do;
      put
        '  ;'/
        'quit;'
        ;
    end;
run;

/* execute generated code */
%include codegen / source2;

filename codegen clear;
libname demo clear;