Question about Accessing Data-Activity- reading excel data

[SU2018]

I am working on SAS programming 1 essentials. I am doing Lesson 2: Accessing Data. Under 'Activity', Question 1: 

• Write a PROC CONTENTS step to generate a report of the storm_summary.sas7bdat table.
  
  • Enterprise Guide: Specify &path and the full name of the table. Can you please help write the statement. I used the following: LIBNAME NP XLSX "Mac\Home\Desktop\Sas class\EPG194_EG\data/np_info.xlsx"; This creates a library 'NP'. But there is nothing within it. I think is wrong with the pathname I have used. Please help me resolve this. Im using SAS enterprise guide. Thank you, SU 

[Reeza]

Please Show the log. 

Though I have seen this happen. Try accessing the data anyways and see if it exists, replace the sheet1 below with the sheet name. 

Data test;
Set no.Sheet1;
Run;

---

@SU2018 wrote:

I am working on SAS programming 1 essentials. I am doing Lesson 2: Accessing Data. Under 'Activity', Question 1: 

• Write a PROC CONTENTS step to generate a report of the storm_summary.sas7bdat table.
  
  • Enterprise Guide: Specify &path and the full name of the table. Can you please help write the statement. I used the following: LIBNAME NP XLSX "Mac\Home\Desktop\Sas class\EPG194_EG\data/np_info.xlsx"; This creates a library 'NP'. But there is nothing within it. I think is wrong with the pathname I have used. Please help me resolve this. Im using SAS enterprise guide. Thank you, SU 

---

[Cynthia_sas]

Hi:

  Are you using SAS Enterprise Guide with a Macintosh? You said:

  And if you are using EG, then your data is just in your WORK location. So the part of the file path where you have EPG194_EG\data isn't necessary.

  Your path seems to indicate a Macintosh computer. However, EG is a Windows client. Did you partition your Mac to have a Windows area using Parallels or another product?

  Can you go to an editor window and run this code:

%put version: &sysvlong4;
%put system: &sysscp and &sysscpl;
%put client: &_clientapp;

And then post what you see in the LOG. That will show information about how you're using SAS and what you client application is.

  If you followed the Enterprise Guide instructions, then all you should need to do is use &PATH, the "helper" macro variable that is created in your AUTOEXEC process flow. So the correct syntax to reference your libname would depend on whether you wanted to read an Excel file (.XLSX) as you show or whether you want to read a SAS dataset. In either case, using &PATH can help you.

  To read the XLSX file, you would use &PATH like this to get PROC CONTENTS to run for your XLSX file:

There is NOT a sheet named sheet1. The sheets are named Parks, Species and Visits. But the practice you posted was for the storm_summary data, not the XLSX workbook.

So, if you want to run PROC CONTENTS for a SAS dataset, like storm_summary, then you have 2 options.

1) use the "long" operating system name and if you are using EG, then that location is your WORK folder (which &PATH should already be pointing to)

and here are the results:

or

2) use the LIBNAME statement to make a nickname for the long physical operating system location and then use the 2-level SAS name, as shown below:

 

 and here are the results:

If you are using Enterprise Guide, then you should have created a project with an AUTOEXEC Process flow that makes your data in the WORK location every time you open the project. Since the WORK location might change every time you log on, the &PATH helper variable makes it simpler to reference files. As you can see in the PROC CONTENTS output, the long name is the name that's used. On my Windows  system, the long name for my WORK location, today, is:

C:\Users\sasczz\AppData\Local\Temp\SEG3000\SAS Temporary Files\_TD10652_l10a732_\Prc2

which is not something I want to look for or type every time I want to start working with the class data!

But that means my data table names, today, are:

C:\Users\sasczz\AppData\Local\Temp\SEG3000\SAS Temporary Files\_TD10652_l10a732_\Prc2\np_info.xlsx

C:\Users\sasczz\AppData\Local\Temp\SEG3000\SAS Temporary Files\_TD10652_l10a732_\Prc2\storm_summary.sas7bdat

  It is far easier to use &PATH on EG to not have to type that long path location.

Cynthia

[PremS]

Do you know if your SAS session is connecting to a server at your work?  Enterprise Guide is a Windows client application, and having it doesn't mean that you have Base SAS on your computer.  If you do have SAS running on a server, then you need to have the files in a place where that server computer can access it (for example, a shared network drive).

A way to check if your SAS is running on a server is to check the Servers Pane on the bottom-left side (you may need to go under View -> Servers to open it up if it is closed).  Click the plus icon to expand 'Servers', and you should see a name right below.  If it says 'Local' then you have a local SAS installation.  If it says anything else (usually called SASApp), then your SAS is running on a server.

Picture attached of the Servers Pane.  Let us know what you see right below the 'Servers' item.

[yayicecream]

Hi, 

I am accessing SAS Enterprise Guide at work (not Base SAS). Where should I put my files, and does this mean that I have to change the name of the path?  

[Panagiotis]

@yayicecream,

Here is the link for the videos to get your data ready : How to access data for SAS Programming 1 course

When you go to the page, scroll to the bottom. There you will find a video for Enterprise Guide instructions.

Let us know if it works,

- Peter

[yayicecream]

Hi, 

Yes, 

I've completed the steps. I think my issue  is that I'm connected to SAS Enterprise Guide at work on a server, so I'm not able to access the files locally. Per the message before me: 

"Do you know if your SAS session is connecting to a server at your work?  Enterprise Guide is a Windows client application, and having it doesn't mean that you have Base SAS on your computer.  If you do have SAS running on a server, then you need to have the files in a place where that server computer can access it (for example, a shared network drive)."

[Panagiotis]

Yeah completely understandable.Since many who are using Enterprise Guide are connected through a server the video accounts for this and will show you how to create the data and store it in your WORK library to allow those of you on a server to access the data.

The video will show you how to set it up so that every time you are planning on working on the course you can run the programs so the data is created and placed in your WORK library. Since you are on a server you will need to run the program the video shows you every time you are working on the course. Remember once you close out of SAS the work library is cleared. So when you reopen Enterprise Guide to work on the course you will have to recreate the data.

Did you a create the autoexec process flow for the two SAS programs like it show in the video? Here is a screenshot of the end of the video and what your project tree should look like:

You might of missed a step for the Enterprise Guide setup since it is different from the others.

[yayicecream]

Hi, 

Yes, below is what my screen looks like. I noticed that there was no "storm" data file so I changed the name to "storm_2017" 

I then used the code: 

*Complete the OPTIONS statement;
options validvarname=v7 ;

*Complete the LIBNAME statement;
libname xlstorm xlsx "&path/storm_2017.xlsx";

*Complete the DATA= option to reference the STORM_SUMMARY worksheet;
proc contents data=xlstorm.storm_summary;
run;

*Clear the XLSTORM library;
libname xlstorm clear;

and the message I get in return is that "ERROR: File XLSTORM.storm_summary.DATA does not exist.

[Cynthia_sas]

Hi:
What you see in the above list, including Storm_2017 and Storm_Summary are SAS datasets. you would not try to read them with the XLSX engine, as you show.

Exactly what demo or activity are you working on?
Cynthia

[RonMann]

I have the exact same issue as the last contributor. It is the EPG194: Lesson 2: of the SAS Programming 1 Essentials. I even get the same Error message when trying to use the same syntax with any Excel local file I have. SAS will create the libname ok but when using the proc statement it does not recognize the sheets within the workbook.

[Panagiotis]

Do you have a log you can show us?

- Peter

[Cynthia_sas]

Also, keep in mind that you must have SAS 9.4 M2 on the server to be able to use the XLSX Libname engine.
Cynthia

[RonMann]

This is what it appears I have installed. So, I am not sure that meets the requirements you mentioned.

[Panagiotis]

@RonMann ,

What exact problem are you working on? Can you show us the code and the error in the log?

You can also run

proc setinit;
run;

At the top it'll say "Current Version". Let us know what that is.

- Peter

- Peter