proc import excel sheet

[FP12]

Hi,

I have an Excel file with several sheet.

I need to import to a table the sheet BKH..."

Sometimes the sheet can be named "BKH_0210", sometimes "BKH546", or whatever else beginning by "BKH", I am sure it's the only sheet name containing and beginning by BKH

So I tried:

proc import datafile = "&inputdir./&input_file."
DBMS = xlsx OUT = &output_table.;
getnames=no;
sheet LIKE "BKH%";
run;

But it's not working...I just tried but I already had the feeling it wouldn't work.

Do you have an idea on how I can do it?

[PaigeMiller]

---

@FP12 wrote:

Hi,

I have an Excel file with several sheet.

I need to import to a table the sheet BKH..."

Sometimes the sheet can be named "BKH_0210", sometimes "BKH546", or whatever else beginning by "BKH", I am sure it's the only sheet name containing and beginning by BKH

So I tried:

proc import datafile = "&inputdir./&input_file."
DBMS = xlsx OUT = &output_table.;
getnames=no;
sheet LIKE "BKH%";
run;

But it's not working...I just tried but I already had the feeling it wouldn't work.

Do you have an idea on how I can do it?

---

The LIKE syntax will not work in PROC IMPORT. It only works in PROC SQL.

You can use the EXCEL engine in a Libname statement to determine the names of all the sheets in your Excel file. Then you can find the one that begins with BKH, and then use that name found in your PROC IMPORT.

https://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n1wvmggexroxgyn17...

[Reeza]

That could look like:

libname myFile XLSX 'path to my excel file.xlsx';

data want;
set myFile.BKH: ;
run;

[FP12]

Thanks Reeza,

It works perfectly... when there are not spaces character.

What if it's "BK H" instead of "BKH"? I tried and it is not working. Do you know a solution for this?

[FP12]

I have another problem with this solution...

The columns names are extracted from the first row and I don't want.

I want something like:

getnames = no

[Reeza]

@FP12 unfortunately I don't think that's possible using the libname method.