I'm trying to access an Excel workbook called report.xlsb using Proc SQL pass-through.
How do I reference a sheet in the workbook called I&I?
Thank you.
proc sql dquote=ansi;
connect to excel (path="C:\Users\report.xlsb");
create table work.capii as
select * from connection to excel
(select * from I&I);
disconnect from excel;
quit;
Do you have to use pass-through? I couldn't get it to work either, and it doesn't look like you received any responses. But if you can use libname access rather than pass-thru, the below code works (9.3 on Win 7 32-bit):
libname myxl Excel "C:\Users\report.xlsb";
proc sql;
create table work.capii as
select * from myxl.'I&I$'n;
quit;
libname myxl clear;
HTH,
Karl
Ah, I remembered as soon as I hit "reply". There's a special character ($) in Excel worksheet names. In SAS, you deal with that using a name literal, as my previous reply indicates. But that doesn't work in SQL. Rather, you quote table names with square brackets. Your original post works for me if I change:
I&I
to:
[I&I$]
Try that and let me know.
Karl
Yes, [I&I$] is correct.
I want to tag your answer as correct, but I can't find that option for some reason.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.