DATA Step, Macro, Functions and more

Dataset error

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Dataset error

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.


Accepted Solutions
Solution
‎02-16-2017 02:40 PM
Super User
Posts: 17,784

Re: Dataset error

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


All Replies
Solution
‎02-16-2017 02:40 PM
Super User
Posts: 17,784

Re: Dataset error

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;

 

 

 

Contributor
Posts: 46

Re: Dataset error

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

 

Contributor
Posts: 21

Re: Dataset error

[ Edited ]

you need to give the full path

 

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

Contributor
Posts: 46

Re: Dataset error

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.

 

Super User
Posts: 10,483

Re: Dataset error

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.

Contributor
Posts: 46

Re: Dataset error

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.

 

Super User
Posts: 17,784

Re: Dataset error

Use XLSX instead of Excel

Contributor
Posts: 46

Re: Dataset error

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;

 

Contributor
Posts: 21

Re: Dataset error

[ Edited ]

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;

 

 

Contributor
Posts: 46

Re: Dataset error

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

 

 

 

Super User
Posts: 17,784

Re: Dataset error

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

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

Contributor
Posts: 21

Re: Dataset error

if u are referring to sheet1 then try this code

 

set D.Test.Sheet1;

Contributor
Posts: 46

Re: Dataset error

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

Contributor
Posts: 46

Re: Dataset error

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 389 views
  • 0 likes
  • 4 in conversation