Errors while using libname to import Excel data

[saspert]

I am trying to run an import from excel into sas (using PC SAS 9.2). Although I did not develop the original code, it seems to be a fairly simple process. However, I am running into errors. Just want to run it by you and check if there is something that I am missing.

Code

%let mth=JUL;

libname  myxls1 "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx";

data &mth.2013;

    set myxls1.BO_Query;

run;

libname myxls1 clear;

Log

647  %let mth=JUL;

648  libname  myxls1 "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx";

NOTE: Libref MYXLS1 was successfully assigned as follows:

      Engine:        EXCEL

      Physical Name: C:\2013q3\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_JUL2013.xlsx

649  data &mth.2013;

650      set myxls1.BO_Query;

ERROR: File MYXLS1.BO_Query.DATA does not exist.

651  run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.JUL2013 may be incomplete.  When this step was stopped there were 0

         observations and 0 variables.

WARNING: Data set WORK.JUL2013 was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

652  libname myxls1 clear;

NOTE: Libref MYXLS1 has been deassigned.

Alternate Import code

PROC IMPORT OUT= &mth.2013 DATAFILE= "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx"

            DBMS=xlsx REPLACE;

     SHEET="BO_Query";

     GETNAMES=YES;

RUN;

Log

660 PROC IMPORT OUT= &mth.2013 DATAFILE=

660! "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx"

661 DBMS=xlsx REPLACE;

ERROR: DBMS type XLSX not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

662 SHEET="BO_Query";

663 GETNAMES=YES;

664 RUN;

The Excel data is stored in a xlsx document with around 190000 records and 4 columns.

I have a similar datastep that is able to read in a different xlsx document. I am not sure what the difference is between them. Any suggestions on resolving this error will be appreciated.

Thanks,
saspert

[ChrisSelley]

Try changing your set statement as below:

%let mth=JUL;

libname  myxls1 "&path\Monthly_Summary_by_LATSK_In_Tran_In_Month_SK_GTET_&mth.2013.xlsx";

data &mth.2013;

    set myxls1."BO_Query$"n;

run;

libname myxls1 clear;

[saspert]

Thanks Chris. I think set myxls1."BO_Query$"n; statement makes a bit of difference. I noticed that Excel add a $ sign to the sheet name while importing - no idea why though.