DATA Step, Macro, Functions and more

proc import excel sheet

Reply
Contributor
Posts: 34

proc import excel sheet

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?

 

Trusted Advisor
Posts: 1,931

Re: proc import excel sheet

[ Edited ]

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...

 

 

Super User
Posts: 19,822

Re: proc import excel sheet

That could look like:

 

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

data want;
set myFile.BKH: ;
run;
Contributor
Posts: 34

Re: proc import excel sheet

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?

Contributor
Posts: 34

Re: proc import excel sheet

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
Super User
Posts: 19,822

Re: proc import excel sheet

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

Ask a Question
Discussion stats
  • 5 replies
  • 140 views
  • 3 likes
  • 3 in conversation