DATA Step, Macro, Functions and more

wildcards in sheet name or RANGE in PROC IMPORT

Reply
Occasional Contributor Xin
Occasional Contributor
Posts: 7

wildcards in sheet name or RANGE in PROC IMPORT

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!

 

 

Super User
Posts: 17,784

Re: wildcards in sheet name or RANGE in PROC IMPORT

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.  

Super User
Posts: 9,671

Re: wildcards in sheet name or RANGE in PROC IMPORT

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;
Super User
Super User
Posts: 7,392

Re: wildcards in sheet name or RANGE in PROC IMPORT

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.

 

Occasional Contributor Xin
Occasional Contributor
Posts: 7

Re: wildcards in sheet name or RANGE in PROC IMPORT

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!

Ask a Question
Discussion stats
  • 4 replies
  • 493 views
  • 0 likes
  • 4 in conversation