BookmarkSubscribeRSS Feed
FP12
Obsidian | Level 7

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?

 

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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

 

 

--
Paige Miller
Reeza
Super User

That could look like:

 

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

data want;
set myFile.BKH: ;
run;
FP12
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4888 views
  • 3 likes
  • 3 in conversation