Question about Accessing Data-Activity- reading excel data

[RonMann]

I am going through the:
SAS Programming 1: Essential
Lesson 2: Accessing Data
Using a Library to read Excel Files (note: there are no Excel or csv files available to follow the example. Or I can't find them.)
As for current version, I don't see that at the top but under SAS Enterprise Guide>Help>About it says Version 7.13 HF3 (7.100.3.5438) (32-bit)

[ateret_frank]

Hi I'm also having a problem with this activity - my current version says 9.04.01M6P110718. Could that help?

[Cynthia_sas]

Hi:

  It is useful to have your version of SAS. However it is also useful to know HOW you are using SAS to work with the class practices. You have several choices:

1) free choices

--1a SAS Studio with SAS OnDemand for Academics

--1b SAS Studio with SAS University Edition in a Virtual Machine

2) other choices (typically through work or school license)

--2a Enterprise Guide with local or server SAS on company network

--2b SAS Studio with local or server SAS on company network

--2c SAS Display Manager on Windows with local installation of SAS on C: drive

  We have a set of data creation instructions for each of these methods. The instructions are NOT interchangeable. In other words, if you are using Enterprise Guide, you cannot use the SAS OnDemand instructions and vice versa.

  Also, for the free options, students are running SAS 9.4 M6 version with the correct SAS/Access products installed. The reason you need the SAS/Access products is that the data for the class (ALL the data) are created with a program that uses the XLSX Libname engine. Without the correct SAS/Access product, your program will NOT work correctly. For the school or work users, the SAS installation needs to be running SAS 9.4 M2 release, because that's when the XLSX Libname engine was first production; and also the SAS/Access engine for PC File formats must be installed or the setup program will NOT work.

  The most frequent problem that students encounter happen because they pick the wrong set of instructions. Other problems occur because of the version issues described above.

  Without knowing EXACTLY how you are using SAS -- one of the 5 methods I've described above--it's hard to help. We did make a video that shows each of these methods. You'll find all 5 videos here, in the Community Forum: https://communities.sas.com/t5/SAS-Communities-Library/How-to-access-data-for-SAS-Programming-1-cour...

  Hope this helps,

Cynthia

[ateret_frank]

Thank you so much! I was actually able to figure it out. People with Macs -
make sure all of your files are downloaded and not in iCloud!

[SASJedi]

Running this program will produces reliable information in the SAS log about what SAS products and versions installed and available to your SAS session:

proc product_status; run;

[RonMann]

Here is the program and the log. Also, this Excel file is the exported Excel version of the Storm_Summary.sas7bdat file. The STORM_SUMMARY is also the name of the sheet in the is workbook. The zip file, coursedata.wk.zip downloaded as part of this course EPG194 did not contain any Excel or csv files as referenced in the course.

OPTIONS VALIDVARNAME=V7;
LIBNAME XLCLASS "Z:\My SAS Files\STORM_SUMMARY.xlsx";

PROC CONTENTS DATA=XLCLASS.STORM_SUMMARY;
RUN;

LIBNAME XLCLASS CLEAR;

LOG:

NOTE: Libref XLCLASS was successfully assigned as follows:
Engine: XLSX
Physical Name: Z:\My SAS Files\STORM_SUMMARY.xlsx

ERROR: File XLCLASS.STORM_SUMMARY.DATA does not exist.

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.
NOTE: Libref XLCLASS has been deassigned.

[Panagiotis]

With Enterprise Guide you need to follow a specific setup and place everything in your work library since the install of SAS can be different for each user. Did you follow the guide here to get your data in the work library: Programming 1 Data.

Do you have a library called PG194 with SAS tables? Does those work for you?

- Peter

[RonMann]

Yes. I followed the instructions for setting up files for Enterprise Guide on my computer. All the steps including running 1egpg194 and 2egpg194 SAS programs. I don't have a library called PG194. I have one called PG1.

[RonMann]

These are the instructions I used for Setup Instructions for your own SAS software. https://vle.sas.com/pluginfile.php/257520/mod_scorm/content/28/setup_eg.htm

[Panagiotis]

Perfect so it looks like everything was set up correctly. From the looks of it you are not pointing to the correct location of the Excel file.

You can see the location of all the SAS tables and additional files (Excel, csv and txt) by typing the code below and checking  your log.

%put &=path;

You will cover macro variables in this course, but for now understand that instead of typing in the long location that you are seeing with %put, you can use the &path macro variable to point to that exact location. &path is just storing that string that you saw in your log.

That location is where the STORM.XLSX file resides. So you will want to use the &path macro variable (location of your work library) and the STORM.XLSX file. So try typing this:

OPTIONS VALIDVARNAME=V7;
LIBNAME XLCLASS "&path\storm.xlsx";

PROC CONTENTS DATA=XLCLASS.STORM_SUMMARY;
RUN;

LIBNAME XLCLASS CLEAR;

Let me know if this works!

- Peter

[RonMann]

Got it. Only one thing. I had to add the engine XLSX to the LIBNAME line for it to work. Then it worked fine. 

In the course, SAS Programming 1: Essentials, Lesson 2: Accessing Data in the Demo: Using a Library to Read Excel Files the speaker shows the actual Excel workbook file along with the sheets in the file. Those were the files I don't see anywhere. Are those Excel files accessible?

[Panagiotis]

My mistake! Glad you caught it 

They should be. I'm not sure how your SAS is set up but you can try to go to the work library location.

Run the statement

%put &=path;

Then check your log and you should see a file path. You should be able to go to that file path and find all the files.

- Peter

[Panagiotis]

You can also right click on the work library and select "properties". You will see the path to that location.

- Peter

[Panagiotis]

 Hi @RonMann , did you find that location?

- Peter

[RonMann]

I found the location but it seems I can not access it directly. The location is G:\path\filename and that is not listed as a drive on my computer.