topic Question about Accessing Data-Activity- reading excel data in Programming 1 and 2

Question about Accessing Data-Activity- reading excel data

SU2018 — Tue, 06 Nov 2018 21:50:30 GMT

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

Re: Question about Accessing Data-Activity- reading excel data

Reeza — Wed, 07 Nov 2018 01:00:36 GMT

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

Re: Question about Accessing Data-Activity- reading excel data

PremS — Wed, 07 Nov 2018 01:30:59 GMT

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.

Re: Question about Accessing Data-Activity- reading excel data

Cynthia_sas — Wed, 07 Nov 2018 19:29:50 GMT

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:

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

Re: Question about Accessing Data-Activity- reading excel data

yayicecream — Thu, 08 Nov 2018 17:58:22 GMT

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?

Re: Question about Accessing Data-Activity- reading excel data

Panagiotis — Thu, 08 Nov 2018 18:07:31 GMT

@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

Re: Question about Accessing Data-Activity- reading excel data

yayicecream — Thu, 08 Nov 2018 18:10:32 GMT

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)."

Re: Question about Accessing Data-Activity- reading excel data

Panagiotis — Thu, 08 Nov 2018 18:23:49 GMT

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.

Re: Question about Accessing Data-Activity- reading excel data

yayicecream — Thu, 08 Nov 2018 18:39:23 GMT

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.

Re: Question about Accessing Data-Activity- reading excel data

Cynthia_sas — Thu, 08 Nov 2018 20:39:10 GMT

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

Re: Question about Accessing Data-Activity- reading excel data

RonMann — Tue, 26 Feb 2019 20:27:30 GMT

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.

Re: Question about Accessing Data-Activity- reading excel data

Panagiotis — Tue, 26 Feb 2019 20:35:31 GMT

Do you have a log you can show us?

- Peter

Re: Question about Accessing Data-Activity- reading excel data

Cynthia_sas — Tue, 26 Feb 2019 21:55:54 GMT

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

Re: Question about Accessing Data-Activity- reading excel data

RonMann — Wed, 27 Feb 2019 13:30:35 GMT

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

Re: Question about Accessing Data-Activity- reading excel data

Panagiotis — Wed, 27 Feb 2019 13:37:42 GMT

@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

Re: Question about Accessing Data-Activity- reading excel data

RonMann — Wed, 27 Feb 2019 13:37:58 GMT

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.

Re: Question about Accessing Data-Activity- reading excel data

RonMann — Wed, 27 Feb 2019 13:45:42 GMT

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)

Re: Question about Accessing Data-Activity- reading excel data

Panagiotis — Wed, 27 Feb 2019 13:58:18 GMT

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

Re: Question about Accessing Data-Activity- reading excel data

RonMann — Wed, 27 Feb 2019 14:05:13 GMT

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.

Re: Question about Accessing Data-Activity- reading excel data

RonMann — Wed, 27 Feb 2019 14:12:58 GMT

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