topic Re: Dataset error in SAS Programming

Dataset error

Malathi13 — Thu, 16 Feb 2017 17:25:47 GMT

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\;

30 libname D "&input";

NOTE: Libref D was successfully assigned as follows:

Engine: V9

Physical Name: C:\desktop\Documents\MyPrograms\

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.

Re: Dataset error

Reeza — Thu, 16 Feb 2017 17:49:56 GMT

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;

Re: Dataset error

gauthamk28 — Thu, 16 Feb 2017 17:54:53 GMT

you need to give the full path

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

Re: Dataset error

ballardw — Thu, 16 Feb 2017 17:55:04 GMT

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.

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 18:00:39 GMT

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

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 18:07:41 GMT

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.

Re: Dataset error

Reeza — Thu, 16 Feb 2017 18:08:53 GMT

Use XLSX instead of Excel

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 18:09:22 GMT

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.

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 18:13:53 GMT

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

46 data mine;

47 set Test;

ERROR: File WORK.TEST.DATA does not exist.

48 run;

Re: Dataset error

gauthamk28 — Thu, 16 Feb 2017 18:21:13 GMT

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;

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 18:21:19 GMT

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

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

Re: Dataset error

Reeza — Thu, 16 Feb 2017 18:28:17 GMT

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

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

Re: Dataset error

gauthamk28 — Thu, 16 Feb 2017 18:31:19 GMT

if u are referring to sheet1 then try this code

set D.Test.Sheet1;

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 18:34:08 GMT

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

98 proc datasets lib=D;

98 ! quit;

NOTE: PROCEDURE DATASETS used (Total process time):

real time 0.03 seconds

cpu time 0.01 seconds

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

Re: Dataset error

Reeza — Thu, 16 Feb 2017 19:05:52 GMT

@Malathi13 awesome 🙂

Please mark one of the answers (not this one!) as the solution and the question will be answered/closed.

Re: Dataset error

Malathi13 — Thu, 16 Feb 2017 19:41:58 GMT

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.