How do I use "Where" statement and "like" operator in Proc Import step properly?

[JFuchsia]

How can I use a where statement and a like operator for sheet to retrieve the proper sheet with a partial name?

Right now this worked: 

proc import datafile="B:\123\file.xls"
     out=table1
	dbms=excel replace;
   where sheet like 'Heat Map';
     getnames=yes;
	 scantext=yes;

run; 

But it gives me red text for "where" and a warning in green:

WARNING: No data sets qualify for WHERE processing.

How can I make this work without red text and without warnings?

I know there is a proper way.

Thanks in advance,

JFuchsia

[Patrick]

What you're trying to do is afaik not supported nor documented syntax for Proc Import.

You could use a libname with the xlsx engine and then search for the table with a like operator using Proc SQL

proc sql;
select libname, memname
from dictionary.tables
where libname='<libref>' and memname like '....'
;
quit;

[JFuchsia]

This approach did not work.  I received notes indicating that there were two tables not returned or displayed because the name was too long.

I also want to output a table from the sheet that is selected, and that doesn't seem possible with this approach. Thank you!

[JFuchsia]

PS. It didn't work, I just got lucky the first time.

[Tom]

Is there some reason you have the ancient (and undocumented) XLS file format instead of the modern (and documented) XLSX file format?

If you had an XLSX file then you can use the XLSX libref engine and discover the sheet names.  Or just read the XML file that is embedded in the XLSX file to find the sheet names.

Is there some reason why you don't know the sheet names in the XLS workbook?

Does the workbook have more than one worksheet?  If it does not then it does not matter to PROC IMPORT as it will just read the first worksheet.

[JFuchsia]

It's just that the sheet names are always some form of the main name, in this case "Heat Map".  There are multiple sheets and I don't want just the first one.

Thanks for checking.

[Tom]

---

@JFuchsia wrote:

It's just that the sheet names are always some form of the main name, in this case "Heat Map".  There are multiple sheets and I don't want just the first one.

Thanks for checking.

---

If you cannot change the process to use XLSX files instead of XLS files then you will need to find some non-SAS program that can get the list of sheetnames from a XLS file.   If you are allowed to run operating system commands from your SAS program then you could call such a program from your SAS program and read the results into a dataset so that you could use it to pick the sheets you want to read.

And if your copy of SAS is running on Windows then you could probably create such a program using a Visual Basic program.

[Kurt_Bremser]

options validmemname=extend;

libname my_excel excel "B:\123\file.xls";

proc sql;
create table sheets as
  select memname
  from dictionary.tables
  where libname = "MY_EXCEL" and upcase(memname) like '%HEAT MAP%'
;
quit;

If that gives you problems, post the complete log.

[JFuchsia]

It did not give me a problem. The only problem is that it creates a table that lists the memname. How can I get that table from the library I just created and create a new table from it. Plus when I go to the library I created, the table won't open because the name is not SAS friendly. In reality I just want to import one sheet at a time without having to open the entire workbook every time just to figure out what the sheet name is. Especially if there are several sheets in a work book, which there are. Is there any way around this?

Thank you,

JFuchsia

[Tom · Accepted solution]

So you are lucky.  I appear to be running on Windows and your version of SAS and your version of EXCEL are compatible.

Once you have the list of the names of the worksheets in a dataset you can use that to generate code to read the sheets you want.

Not sure what you mean about "open the entire workbook every time".  How else can you tell what is in a file without opening it?

Also not sure what you mean by "the table won't open because the name is not SAS friendly".  What "table" is that?  Did you mean the MEMNAME values returned by the query to DICTIONARY.TABLES are not valid V7 SAS names? 

That should not cause any trouble when using them as the SHEET name in PROC IMPORT code.   If you want to use them as MEMNAME values to reference the worksheet using the libref you created with the EXCEL engine then you will need to do two things.  First is set the VALIDMEMNAME option to EXTEND.  And second is convert them to valid SAS names by using name literals.  You can use the NLITERAL() function to do that.  Or if the names do not contain and single quote characters just wrap it with single quotes and append the letter N.

So perhaps something like this to copy all of the selected sheets into datasets in the WORK library.

libname my_excel excel "B:\123\file.xls";

proc sql;
select nliteral(memname) into :memlist separated by ' '
  from dictionary.tables
  where libname = "MY_EXCEL"
    and upcase(memname) like '%HEAT MAP%'
;
quit;

proc copy inlib=my_excel outlib=work;
  select &memlist;
run;

But you might want to make up some more friendly names of the datasets you create from the worksheet.  Names that are valid SAS names and so do not need name literals to reference or using VALIDMEMNAME=extend setting to use them in the future.

[JFuchsia]

This worked for getting my table out. How can I rename the table?

Thank you!!!

[Tom]

You could rename them after the fact. Look at PROC DATASETS.

Or copy them one by one using DATA steps instead so that you can chose the name that is used.

data good_name;
  set my_excel.'bad name'n;
run;

[JFuchsia]

Figured  it out!

data sheet;
  set my_excel.&memlist;
run;

[Tom]

---

@JFuchsia wrote:

Figured  it out!

data sheet;
  set my_excel.&memlist;
run;

---

That will only work when you only found one matching sheet name.