topic Re: Trying to create a library to read Excel files in SAS Studio

Trying to create a library to read Excel files

Esmatullah — Thu, 07 Nov 2019 18:17:17 GMT

I have a problem with creating a library to read and access Excel files.

Based on the instruction, I have to first create a library to access and read Excel files. The statements to create the intended library is:

*complete the OPTIONS statement;
options validvarname=v7;

*complete the LIBNAME statement;
libname xlstorm xlsx "s:/workshop/data/storm.xlsx";

Running this gives this message:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 *complete the OPTIONS statement;

74 options validvarname=v7;

76 *complete the LIBNAME statement;

77 libname xlstorm xlsx "s:/workshop/data/storm.xlsx";

NOTE: Libref XLSTORM was successfully assigned as follows:

Engine: XLSX

Physical Name: s:/workshop/data/storm.xlsx

Then, I run the full statement:

*complete the OPTIONS statement;
options validvarname=v7;

*complete the LIBNAME statement;
libname xlstorm xlsx "s:/workshop/data/storm.xlsx";
*complete the DATA=option to reference the STORM_SUMMARY worksheet;
proc contents data=xlstorm.storm_summary;
run;
*clear the xlstorm library;
libname xlstore clear;

Opposite to the instructor's result- which is a nice table, mine gives me this message:

78 *complete the DATA=option to reference the STORM_SUMMARY worksheet;

79 proc contents data=xlstorm.storm_summary;

ERROR: File XLSTORM.storm_summary.DATA does not exist.

80 run;

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

81 *clear the xlstorm library;

82 libname xlstore clear;

WARNING: Libref XLSTORE is not assigned.

84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

What is wrong with my activity while the library was created/assigned at the first step?

Looking for quick help.

Thanks

Re: Trying to create a library to read Excel files

PGStats — Thu, 07 Nov 2019 18:26:50 GMT

The note says that storm_summary doesn't exist in the library, i.e. in your excel workbook.

If storm_summary is a worksheet name, the syntax to read it as a dataset is

xlstorm.'storm_summary$'n

Re: Trying to create a library to read Excel files

Tom — Thu, 07 Nov 2019 18:34:27 GMT

To see what exists in the workbook use _ALL_ as the memname in the PROC CONTENTS code. That will show the variables for every dataset (worksheet) in the file.

proc contents data=xlstorm._all_;
run;

Note that when you define the libref using the LIBNAME statement SAS does not know whether your intention is to READ from that file or write to that file. So if the file does not exist the LIBNAME statement will work fine. But then trying to get the contents of the file you haven't written anything into yet will fail.

Make sure the file you want to access actually is in that directory on the machine where SAS is running (NOT on the machine where the browser you are using to access the SAS/Studio.) Note that the S drive on the SAS server might not be the same location as the S drive on the machine that is accessing SAS/Studio. So if S: is mapped to some network share (like \\servername\sharename\) you might want to use that instead of the S: in the path.

Re: Trying to create a library to read Excel files

maximedesse — Fri, 16 Oct 2020 20:53:32 GMT

Neither response is useful. I am facing the same problem literally in similar circumstances. Trying to run an activity in the course work to replicate results displayed in the video. After running the code, the library is actually assigned, but not a single worksheet from the workbook was distributed into it. The message error came from the proc contents part of the code, because the worksheet it is trying to assess actually does not exist in the concerned library. The question is: how can the library be properly assigned and the worksheets not distributed, when the Excel spreadsheet actually exists and the log did not return a "File not found" or "File does not exist" error message? That's the crux of the question that needs to be addressed.

Re: Trying to create a library to read Excel files

Tom — Fri, 16 Oct 2020 21:11:55 GMT

I answered that question already.

To syntax you use to make a libref to read from an existing file is exactly the same as you would use to create a new file.

So you can code:

libname x xlsx 'somefile.xlsx';

And if somefile.xlsx does not exist the LIBNAME statement will NOT generate any error.

But if you then try to see what is in your library:

proc contents data=x._all_; run;

You will get an error since there is nothing in that XLSX file yet.

So it sounds like the filename you are using is not the right filename.

Since you are using SAS/Studio you can use the Server Files and Folder tab in the SAS/Studio interface to find where the file you want to read is. Right click on the filename there and select Properties and you will see the filename you need to use in your SAS code to reference that file in your SAS code.

Re: Trying to create a library to read Excel files

Cynthia_sas — Sat, 17 Oct 2020 16:23:28 GMT

Hi:
S:\workshop\data is the path we use in our classroom lab machines or in our Live Web labs. Some of the course videos in Programming 1 do show this path. However, the intention is for the student to replace the s:\workshop\data portion of the path with THEIR location for the class data. Typically this will be something like one of the following examples (depending on how you are using SAS and what set of directions you followed to make the data):

** Option 1 for Data path in the class videos;
libname pg1 's:\workshop\data';
libname xlstorm xlsx "s:\workshop\data\storm.xlsx";

** Option 2 for Data path in the class videos (some classes do not have a data subfolder);
libname pg1 's:\workshop';
libname xlstorm xlsx "s:\workshop\storm.xlsx";

** SAS OnDemand for Academics;
libname pg1 '/home/<userID>/EGP1V2/data';
libname xlstorm xlsx "/home/<userID>/EGP1V2/data/storm.xlsx";

** SAS University Edition in a Virtual Machine;
libname pg1 '/folders/myfolders/EGP1V2/data';
libname xlstorm xlsx "/folders/myfolders/EGP1V2/data/storm.xlsx";

** SAS in a local installation on your C: drive;
libname pg1 'c:\SAS_class\EGP1V2\data';
libname xlstorm xlsx "c:\SAS_class\EGP1V2\data\storm.xlsx";

** SAS on a Unix server;
libname pg1 '/usr/xxxyyy/class/EGP1V2/data';
libname xlstorm xlsx "/usr/xxxyyy/class/EGP1V2/data/storm.xlsx";

** SAS Enterprise Guide without write access to a C: drive;
** (creation program makes all data in the WORK location);
** &path macro variable holds WORK path -- macro variable created by autoexec;
libname pg1 "&path";
libname xlstorm xlsx "&path/storm.xlsx";

If you are currently in a class using one of our classroom lab machines with an S: drive, then please work with your instructor on figuring out why your data path is not working.

If you are using the e-learning class and skipped over the Course Overview and Data Setup section of the course, then please go back to that section (above Lesson 1 in the Table of Contents -- with the word "REQUIRED" next to it) and follow the instructions to make the data for your method of using SAS.

Hope this helps,
Cynthia

Re: Trying to create a library to read Excel files

Srygl1sl — Fri, 06 May 2022 14:57:58 GMT

This unfortunately did not help with this issue. I have tried running the syntax using the correct location for storm.xlsx (I obtained the location directly from the file properties in SAS studio). I am still getting a library with no contents/tables.

Re: Trying to create a library to read Excel files

Cynthia_sas — Fri, 06 May 2022 15:49:31 GMT

Hi:
There are 2 possibilities that I can think of:
1) you are using SAS through a corporate or school license and the setup program did not create the class XLSX files because your copy of SAS does not have the right SAS/Access engine for making XLSX file.
2) something is still wrong with your libname statement.

What is your interface to SAS: SAS Studio? SAS on Windows? SAS Enterprise Guide? Are you using SAS OnDemand for Academics?

Did you successfully run the data creation program following the class instructions? Can you see the storm.xlsx file under the data folder for class? What is the EXACT syntax that you submitted? Were there error messages in the SAS log? If you continue to have issues that you can't resolve, then I recommend that you send a screen shot of your log messages to elearn@sas.com and explain to them how you are using SAS and whether the data setup program worked correctly.
Cynthia

Re: Trying to create a library to read Excel files

Srygl1sl — Fri, 06 May 2022 15:59:24 GMT

I do see Storm.xlsx under the EPG194/data folder. I am using SAS studio
within SAS OnDemand for Academics. I used the path/location directly
provided by the "properties" for Storm.xlsx in SAS Studio.

There were no error messages in my log. The syntax I used is below.

option validvarname=v7;

libname xlstorm XLSX "/home/U40384536/EPG194/data/storm.xlsx";
run;

Re: Trying to create a library to read Excel files

Kurt_Bremser — Fri, 06 May 2022 16:40:27 GMT

Are you perfectly sure your username starts with a capital "U"? All the usernames I've seen so far are lowercase.

Re: Trying to create a library to read Excel files

Srygl1sl — Fri, 06 May 2022 16:46:28 GMT

By George, Kurt- you solved it! I had no idea it would be case sensitive 🙃

Re: Trying to create a library to read Excel files

Cynthia_sas — Fri, 06 May 2022 17:00:10 GMT

Hi:
Are you sure that your userID starts with a capital U??? Most of the userIDs that I've seen on the OnDemand server start with a lowercase u. SAS OnDemand is a Linux-based system, so any type of spelling error or incorrect case would cause an error. Can you check the Properties of your Files(Home) location and double check the userID.
Cynthia

Re: Trying to create a library to read Excel files

Kurt_Bremser — Fri, 06 May 2022 19:51:39 GMT

Case sensitivity is one of the most important features of UNIX systems. Files aaa and AAA can coexist in the same location.

This plays an important role for SAS. SAS files (datasets, views, catalogs, etc) must always have all-lowercase names, or they won't work.