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:
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.
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;
Just to clarify, all flavors of SAS Viya installations include a SAS/ACCESS to PC File Formats.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.