how to import multiple sheets from a single excel workbook without using macro or proc sql.
Without macros? Simple. Write code to import each sheet, one-by-one.
If you have SAS/Access to PC files you can try LIBNAME EXCEL Engine.
If your trying to append all the data in different sheets you assign the excel file as a library and read the individual sheets.
NOTE: If you see the datasets in the Excel library with "$" trailing then the dataset has to be referenced as FILE.'sheet1$'n
Libname file EXCEL "<FilePath>";
PROC SQL ;
select COMPRESS(cat(libname,".","'",memname,"'","n"))
into :ranges separated by ' '
from dictionary.members
where libname = 'FILE'
;
QUIT;
%PUT &ranges;
DATA test;
set &ranges;
run;
LIBNAME File clear;
Hope this helps,
Thanks,
Suryakiran
Well, @SuryaKiran, that's brilliant, I like it ... but the original request was very stubborn and clear ... no macros, no PROC SQL (and sounds very much like a homework assignment)
The SAS/ACCESS Interface to PC Files engine that you use may depend on your version of SAS and the type of Excel file that you need to import.
Here is sample code to import an XLSX file using SAS 9.4 M4:
libname xl xlsx 'C:\temp\MyFile.xlsx' access=read;
options validvarname=any validmemname=extend;
proc datasets nolist;
copy in=xl out=work;
run; quit;
Vince DelGobbo
SAS R&D
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.