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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.