Hi,
I'm trying to create a new dataset from an xlsx file and I see the error. I have never experienced this error before as I have done this so many times (Almost everytime). Don't know why I'm getting this error.
Here is the simple dataset that I want to create:
%let input=C:\desktop\Documents\MyPrograms\;
libname D "&input";
data have;
set D.Test;
run;
The error is:
%let input=C:\desktop\Documents\MyPrograms\;
29
30 libname D "&input";
NOTE: Libref D was successfully assigned as follows:
Engine: V9
Physical Name: C:\desktop\Documents\MyPrograms\
31
32 data have;
33 set D.Test;
ERROR: File D.TEST.DATA does not exist.
34 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.HAVE may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.HAVE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
I don't understand why this is not working. My excel file is attached.
Your library is to the directory. If you're trying to access an Excel via libname, point the libname statement to the Excel file AND include the DBMS type on the libname statement.
Libname myfile xlsx 'path to xlsx file';
proc datasets lib=myfile;
run;quit;
Your library is to the directory. If you're trying to access an Excel via libname, point the libname statement to the Excel file AND include the DBMS type on the libname statement.
Libname myfile xlsx 'path to xlsx file';
proc datasets lib=myfile;
run;quit;
Hi Reeza and Gautham,
I tried both the ways and didn't work still.
NOTE: Libref P was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx
8 data mine;
9 set Test;
ERROR: File WORK.TEST.DATA does not exist.
10 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MINE may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.MINE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
you need to give the full path
%let input=C:\desktop\Documents\MyPrograms\Test.xlsx;
THis is the error I'm getting even if I use the full path
34 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx;
35 libname D excel "&input.test.xlsx";
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
To have an excel file as a libray you need to indicate the engine to use and have an explicit file in the libname statement. SAS does not support looking at files of an external data type such as Excel or DBMS in a folder, each such source needs to be explicitly named.
libname D excel "&input.test.xlsx";
if you insist on a macro variable.
This is error I'm getting when I use a macro
32 %let input=C:\Users\Desktop\Documents\MyProgramssamples\;
33 libname D excel "&input.test.xlsx";
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
Use XLSX instead of Excel
Hi Reeza,
It worked with xlsx but the dataset error is till coming (Dataset test does not exist).
43 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx;
44 libname D xlsx "&input";
NOTE: Libref D was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\UsersDesktop\Documents\MyProgramssamples\Test.xlsx
45
46 data mine;
47 set Test;
ERROR: File WORK.TEST.DATA does not exist.
48 run;
you missed to add library
47 set Test;
@Malathi13 wrote:
47 set Test;
so SAS used default work library and you got this error
ERROR: File WORK.TEST.DATA does not exist.
try
set D.Test;
Still showing the same error.
76 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx;
77 libname D xlsx "&input";
NOTE: Libref D was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx
78
79
80 data mine;
81 set D.Test;
ERROR: Couldn't find range or sheet in spreadsheet
ERROR: File D.Test.DATA does not exist.
82 run;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MINE may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.MINE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
You refer to the workbook by the sheet name not the workbook name.
Run the proc contents to see what the datasets are called.
if u are referring to sheet1 then try this code
set D.Test.Sheet1;
Thanks Gautham,
The correct way to mention the shhet name in the set statement is
set D.sheet1
Thanks for you suggestions, they were evry close to the right answer.
M
Thank you all for your valuable suggestions, it finally worked. I have to mention the sheet name and not the excel file name in the dataset.
Here is my correct code:
%let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx;
libname D xlsx "&input";
proc datasets lib=D; quit;
data mine;
set D.sheet1;
run;
PROC SQL;
SELECT *
FROM D.sheet1;
QUIT;
And the log:
95 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx;
96 libname D xlsx "&input";
NOTE: Libref D was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx
97
98 proc datasets lib=D;
98 ! quit;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
99
100 data mine;
101 set D.sheet1;
102 run;
NOTE: The import data set has 10 observations and 7 variables.
NOTE: There were 10 observations read from the data set D.sheet1.
NOTE: The data set WORK.MINE has 10 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds
Thanks
M
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.