topic Re: excel engine to import excel into SAS in SAS Programming

excel engine to import excel into SAS

Ronein — Wed, 27 Nov 2019 10:31:42 GMT

Hello

I am trying to learn using Excel engine to import Excel table into sas data set.

I will show 3 examples.

First example is working well.

Example1-

Here there is XLSX file that contain one sheet.

It is working well with no errors.

libname xl   XLSX     "/path/cars_Hybrid.xlsx"; 
data cars_Hybrid;
set xl.cars_Hybrid;
run;

libname xl clear;
/*Import excel file called cars_Hybrid.xlsx
 and create sas data set called cas_Hybrid */

Example2-

Here there is XLS file that contain one sheet.

I get an error "ERROR: File XL.class11.DATA does not exist."

libname    xl     XLSX     "/path/class11.xls"; 
data class11;
  set xl.class11;
run;
libname xl clear;

Example3-

Here there is XLS file that contain multiple sheets.

I want to import into sas only sheet called "USA"

libname      xl    XLSX      "/path/MultiSheet1.XLS"; 
data cars_Hybrid;
  set xl.'USA$'n;;
run;

I get an error "ERROR: File XL.'USA$'n.DATA does not exist."

Re: excel engine to import excel into SAS

Ksharp — Wed, 27 Nov 2019 11:36:28 GMT

Check Sheet name by

libname      xl    XLSX      "/path/MultiSheet1.XLS"; 
proc contents data=xl._all_;
run;

or try dictionary table:

proc sql;

select memname

from dictionary.tables

where libname='XL';

quit;

And check if it contains trailing blanks .

Re: excel engine to import excel into SAS

Ronein — Wed, 27 Nov 2019 11:46:58 GMT

Thank you.

Please find a full example.

In xlsx file there are 3 sheets:S1,S2,S3 and I want to import only sheet "S1" into SAS


proc export data=Sashelp.Class 
file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/TYOTA2/RRR.xlsx"
dbms=xlsx;
sheet='S1';

proc export data=sashelp.cars
file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/TYOTA2/RRR.xlsx" 
dbms=xlsx;
sheet='S2';

proc export data=Sashelp.Shoes 
file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/TYOTA2/RRR.xlsx" 
dbms=xlsx; 
sheet='S3';
run;


libname   www    XLSX     "/path/RRR.xlsx"; 
proc contents data=www._all_;
run;


data RRR_Sheet1;
  set www.'S1$'n;;
run;

/*ERROR: Couldn't find range or sheet in spreadsheet*/
/*ERROR: File WWW.'S1$'n.DATA does not exist.*/

Re: excel engine to import excel into SAS

Ksharp — Wed, 27 Nov 2019 12:00:51 GMT

I mean check the right sheet name .

Maybe

'S1$'n

should be

'S1 $'n

proc sql;

select nliter(memname)

from dictionary.tables

where libname='WWE';

quit;

Re: excel engine to import excel into SAS

Kurt_Bremser — Wed, 27 Nov 2019 12:16:31 GMT

You're outsmarting yourself. You used valid SAS names for the sheets on export, so use them again for import:

%let exfil=/folders/myfolders/test.xlsx;

proc export
  data=sashelp.class 
  file="&exfil"
  dbms=xlsx
  replace
;
sheet='S1';

proc export
  data=sashelp.cars
  file="&exfil" 
  dbms=xlsx
  replace
;
sheet='S2';

proc export
  data=sashelp.shoes 
  file="&exfil" 
  dbms=xlsx
  replace
; 
sheet='S3';
run;

libname www xlsx "&exfil";

proc contents data=www._all_;
run;

data RRR_Sheet1;
  set www.S1;
run;

Log:

 73         %let exfil=/folders/myfolders/test.xlsx;
 74         
 75         proc export
 76           data=sashelp.class
 77           file="&exfil"
 78           dbms=xlsx
 79           replace
 80         ;
 81         sheet='S1';
 82         
 
 NOTE: The export data set has 19 observations and 5 variables.
 NOTE: "/folders/myfolders/test.xlsx" file was successfully created.
 NOTE:  Verwendet wurde: PROZEDUR EXPORT - (Gesamtverarbeitungszeit):
       real time           0.06 seconds
       cpu time            0.04 seconds
       
 
 83         proc export
 84           data=sashelp.cars
 85           file="&exfil"
 86           dbms=xlsx
 87           replace
 88         ;
 89         sheet='S2';
 90         
 
 NOTE: The export data set has 428 observations and 15 variables.
 NOTE: "/folders/myfolders/test.xlsx" file was successfully created.
 NOTE:  Verwendet wurde: PROZEDUR EXPORT - (Gesamtverarbeitungszeit):
       real time           0.07 seconds
       cpu time            0.05 seconds
       
 
 91         proc export
 92           data=sashelp.shoes
 93           file="&exfil"
 94           dbms=xlsx
 95           replace
 96         ;
 97         sheet='S3';
 98         run;
 
 NOTE: The export data set has 395 observations and 7 variables.
 NOTE: "/folders/myfolders/test.xlsx" file was successfully created.
 NOTE:  Verwendet wurde: PROZEDUR EXPORT - (Gesamtverarbeitungszeit):
       real time           0.06 seconds
       cpu time            0.06 seconds
       
 
 99         
 100        libname www xlsx "&exfil";
 NOTE: Libref WWW was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /folders/myfolders/test.xlsx
 101        
 102        proc contents data=www._all_;
 103        run;
 
 NOTE:  Verwendet wurde: PROZEDUR CONTENTS - (Gesamtverarbeitungszeit):
       real time           0.26 seconds
       cpu time            0.25 seconds
       
 
 104        
 105        data RRR_Sheet1;
 106          set www.S1;
 107        run;
 
 NOTE: The import data set has 19 observations and 5 variables.
 NOTE: There were 19 observations read from the data set WWW.S1.
 NOTE: The data set WORK.RRR_SHEET1 has 19 observations and 5 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.02 seconds
       cpu time            0.02 seconds

Re: excel engine to import excel into SAS

Ronein — Wed, 27 Nov 2019 12:16:32 GMT

name is S1