topic Re: proc import excel sheet in SAS Programming

proc import excel sheet

FP12 — Tue, 22 Aug 2017 17:40:29 GMT

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?

Re: proc import excel sheet

PaigeMiller — Tue, 22 Aug 2017 17:48:50 GMT

@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#n1wvmggexroxgyn17rp61jml3cvn.htm

Re: proc import excel sheet

Reeza — Tue, 22 Aug 2017 18:20:25 GMT

That could look like:

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

data want;
set myFile.BKH: ;
run;

Re: proc import excel sheet

FP12 — Wed, 23 Aug 2017 10:04:46 GMT

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?

Re: proc import excel sheet

FP12 — Wed, 23 Aug 2017 11:35:39 GMT

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

Re: proc import excel sheet

Reeza — Wed, 23 Aug 2017 14:46:29 GMT

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