Errors while using libname to import Excel data

saspert — Wed, 30 Oct 2013 17:31:51 GMT

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

Re: Errors while using libname to import Excel data

ChrisSelley — Thu, 31 Oct 2013 16:38:39 GMT

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;

Re: Errors while using libname to import Excel data

saspert — Mon, 03 Mar 2014 15:23:58 GMT

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.

topic Errors while using libname to import Excel data in SAS Programming

Errors while using libname to import Excel data

Re: Errors while using libname to import Excel data

Re: Errors while using libname to import Excel data