topic Re: Import Excel with multiple sheet into SAS Viya in SAS Programming

Import Excel with multiple sheet into SAS Viya

GingerJJ — Wed, 08 Jan 2025 18:18:31 GMT

Hello,

I used to use SAS EG to import excel with multiple sheets. It works very well. Here is my code:

libname AB excel 'X:\datafile.xls' ;
proc sql noprint ;
select cats( "AB.'", memname, "'n" ) into : xlfiles separated by ' '
from dictionary.members
where libname = 'AB'
order by memname ;
%put The workbook contains &sqlobs worksheets with data ;
quit ;
data together ;
set &xlfiles ;
run ;

However, when I tried to used it in my SAS Viya, it gives me this error:

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

How can I import excel with multiple sheet into SAS Viya?

Thank you.

Re: Import Excel with multiple sheet into SAS Viya

SASJedi — Wed, 08 Jan 2025 21:14:37 GMT

The EXCEL engine is technology that works with Windows, but SAS Viya runs in Linux. The XLS file type stopped being the default for Excel in 2003 - that's some seriously old technology! Since then, the Excel default file type has been XLSX. If you can save the Excel file as in XLSX format, you can use the XLSX engine in your LIBNAME statement like this:

libname AB xlsx '~/datafile.xlsx' ;

Also, SAS Viya won't have access to Windows drives - you'll have to upload the XLSX file to a directory on Viya Linux environment. The above code assumes you loaded the file into your home directory. After that, the rest of your code should work just fine.

If this isn't possible, your system may have access to a SAS PC Files server. If so, you could use SAS PCFILES syntax for your LIBNAME statement, and that should work, too.

Re: Import Excel with multiple sheet into SAS Viya

Ksharp — Thu, 09 Jan 2025 01:17:07 GMT

That means your sas don't have license of EXCEL engine. But you could try other engines: XLS XLSX:

libname AB xls 'X:\datafile.xls' ;
libname AB xlsx 'X:\datafile.xls' ;

proc copy in=AB out=work noclone;

run;

Re: Import Excel with multiple sheet into SAS Viya

SASJedi — Thu, 09 Jan 2025 14:43:29 GMT

Just to clarify, all flavors of SAS Viya installations include a SAS/ACCESS to PC File Formats.