BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Malathi13
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

 

 

 

View solution in original post

15 REPLIES 15
Reeza
Super User

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;

 

 

 

Malathi13
Obsidian | Level 7

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

 

gauthamk28
Obsidian | Level 7

you need to give the full path

 

%let input=C:\desktop\Documents\MyPrograms\Test.xlsx;

Malathi13
Obsidian | Level 7

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.

 

ballardw
Super User

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.

Malathi13
Obsidian | Level 7

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.

 

Reeza
Super User

Use XLSX instead of Excel

Malathi13
Obsidian | Level 7

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;

 

gauthamk28
Obsidian | Level 7

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;

 

 

Malathi13
Obsidian | Level 7

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

 

 

 

Reeza
Super User

You refer to the workbook by the sheet name not the workbook name. 

Run the proc contents to see what the datasets are called.

gauthamk28
Obsidian | Level 7

if u are referring to sheet1 then try this code

 

set D.Test.Sheet1;

Malathi13
Obsidian | Level 7

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

Malathi13
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 11779 views
  • 5 likes
  • 4 in conversation