Learning SAS? Welcome to the exclusive online community for all SAS learners.

Problems creating a sas data set from excel worksheet

Reply
New Contributor
Posts: 3

Problems creating a sas data set from excel worksheet

Hello,

     I am trying to create a sas data set from an excal worksheet and this is the following code that I am am using in sas studio:

                                   libname mydata '/folders/myfolders/custfm.xls';

     However, when I run it I receive this log output :

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 libname mydata '/folders/myfolders/custfm.xls';

ERROR: Library MYDATA is not in a valid format for access method RANDOM.

ERROR: Error in the LIBNAME statement.

44 

45 

46 ;

47 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

57 ;

How should I fix my code so that it is read?

Super User
Posts: 10,020

Re: Problems creating a sas data set from excel worksheet

libname mydata xls '/folders/myfolders/custfm.xls';


libname mydata excel  '/folders/myfolders/custfm.xls';

New Contributor
Posts: 3

Re: Problems creating a sas data set from excel worksheet

Hi Ksharp,

  I tried your solutions but I received this lag error:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

42 ;

43 libname mydata excel '/folders/myfolders/custfm.xls';

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

44 ;

45 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

55 ;

Super User
Posts: 19,770

Re: Problems creating a sas data set from excel worksheet

You can't use the libname reference method in Analytics U because its on a Unix install.

You'll need to use a proc import instead.

SAS Super FREQ
Posts: 8,863

Re: Problems creating a sas data set from excel worksheet

Hi:

  If you look carefully at the practice exercise that has you read the CUSTFM workbook, you will see a statement that you should write the code, but not submit the code. This is because the code you need to submit in a UNIX environment will be different than the simple code that is shown in the course.
          

  There is a way to read the XLS file using PROC IMPORT. I am not entirely sure that the LIBNAME engine method is possible on University Edition because you would need to have the PC File Server installed in order to use the LIBNAME engine and you might also have to use SERVER= and PORT= options. However, that code is outside the scope of what we teach in Programming 1.
      

  The attached screen shots show 1) the note in the practice that tells you NOT to submit the code and 2) shows the code that does work in the University Edition (but it is different from what is shown in the solution in the course).
     

cynthia


not_submit_code.pngpossible_method_for_custfm.png
Super User
Posts: 10,020

Re: Problems creating a sas data set from excel worksheet

Or try:

libname mydata xls  '/folders/myfolders/custfm.xls';

New Contributor
Posts: 2

Re: Problems creating a sas data set from excel worksheet

Isn't it the same statement you gave in an earlier reply? Or any change in it?

Anyway it does not work.

Tx

SAS Super FREQ
Posts: 8,863

Re: Problems creating a sas data set from excel worksheet

Hi,

The reason it doesn't work is that XLS is not a valid "engine" to use with the LIBNAME statement. If you continue to have issues with reading Excel files and the information on this posting does not help (https://communities.sas.com/message/219580), then my suggestion is that you open a track with SAS Tech Support.

cynthia

Ask a Question
Discussion stats
  • 7 replies
  • 2086 views
  • 0 likes
  • 5 in conversation