wildcards in sheet name or RANGE in PROC IMPORT

[Xin]

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!

[Reeza]

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]

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]

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]

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!