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:
*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;
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
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.
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.
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.
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
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.
Are you perfectly sure your username starts with a capital "U"? All the usernames I've seen so far are lowercase.
By George, Kurt- you solved it! I had no idea it would be case sensitive 🙃
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.
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.