BookmarkSubscribeRSS Feed
asuman
Obsidian | Level 7

how to import multiple sheets from a single excel workbook without using macro or proc sql.

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16
Not sure if its possible without the use of macros , as the multiple sheets are to be converted to each separate dataset. It would be interesting to know if there is way to achieve it.
Thanks,
Jag
PaigeMiller
Diamond | Level 26

Without macros? Simple. Write code to import each sheet, one-by-one.

--
Paige Miller
SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran
PaigeMiller
Diamond | Level 26

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)

--
Paige Miller
Vince_SAS
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3190 views
  • 4 likes
  • 5 in conversation