BookmarkSubscribeRSS Feed
Xin
Fluorite | Level 6 Xin
Fluorite | Level 6

Greetings!

I was wondering how to use wildcards in sheet name or range name when using LIBNAME or PROC IMPORT? For example, if there are several .xlsx files to be read in, each with multiple sheets and one of the sheets has name started with "F", i want to read in the "F" sheet for each file only, how to tell SAS that in date step or PROC IMPORT. Here are some codes i was thinking of but couldn't work.  

 

* i is a macro variable holds the number of files to be read; 

data test&i; 

   set libname."F*$"n; 

run; 

 

OR

 

proc import out = test&i datafile = read&i dbms = excel replace; 

   sheet = "F*$"; 

run; 

 

Thank you!

 

 

4 REPLIES 4
Reeza
Super User

Sadly I don't think there's an easy way within variable literals.

 

If you can use libname though, you can use proc datasets to generate a list of the sheets available and store that information into a macro variable to be used.  

Ksharp
Super User

You can try to use PROC COPY.

 

libname x excel '.........';
proc copy in=x out=work;
 select F: ;  /* select all the tables start with F*/
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, general advice, don't use either Excel or Proc Import, your life will be much better.  If you absolutely have to use it then simplest way is to write a VBA macro to find the sheets needed and save them out to CSV:

Something along the lines of this: http://stackoverflow.com/questions/10551353/saving-excel-worksheet-to-csv-files-with-filenameworkshe...

But just add an if statement to check if SheetName contains "F".

Then in SAS its a simple datastep import program.

 

Xin
Fluorite | Level 6 Xin
Fluorite | Level 6

Thank you all for the responses. Keshan and Reeze's responses are brainstorming. Keshan's code works well when there is no liternal names in the sheet names. When there is $ in the sheet name, PROC COPY (or COPY statement in PROC DATASETS) generate error message. I ended up using proc sql to create a series of macro variables using where statement to select the sheet name contains "F". It works well so far. Thanks!

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
  • 4 replies
  • 3444 views
  • 0 likes
  • 4 in conversation