Question about Accessing Data-Activity- reading excel data

[Panagiotis]

I'm guessing it's on a server somewhere and not on your local machine so you don't have direct access to it. SAS might only have access.

A quick work around is you can download the zip file from one of  the other interface choices on the e-learning. Extract the zip file locally and you will see them all. I'm guessing your SAS won't be able to access them if they are on your local computer, but you can see them if you are curious.

- Peter

[RonMann]

Exactly on the server. Can you point me to one of the other interface choices? I did download and install the Oracle Virtual Machine but it turns out that runs Studio and I am using Enterprise Guide.

[Panagiotis]

There are no other interface options for Enterprise Guide. SAS University Edition and SAS OnDemand for Academics are free but both are SAS Studio. 

In the end if all you are practicing is your programming, any of the interfaces will suffice. The programming is the same in each interface. Each has an editor, log and results. Working with Studio will still give you the opportunity to practice your programming the same as it would in Enterprise Guide.

- Peter

[AndreaLovdel]

Hi @Panagiotis  🙂

I know this was posted a while ago but I've come across the same problem. I have the path, but like the previous mentioned issue, I can't access it as it's probably on the server somewhere. So, I should download the excel files manually then? 

Also, you said to write this and run it, but it comes up with an error for me. RonMann said he had to add XLSZ to the libname, but where exactly do I add that to the code? I am quite new at this so I might not fully get it 🙂 

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

PROC CONTENTS DATA=XLCLASS.STORM_SUMMARY;
RUN;

LIBNAME XLCLASS CLEAR;

This was your instructions, but I can't spot the mistake hehe. After running it, it says: NOTE: Library XLCLASS does not exist.

and then this. ERROR: File XLCLASS.STORM_SUMMARY.DATA does not exist.

I do have the PG1 library with files etc, but like before, I just don't have access to the excel files. I hope it makes sense.

Thank you!

Andrea 
 

[Kurt_Bremser]

Which environment are you using?

• University Edition in virtual machine; if yes, which base operating system?
• On Demand for Academics
• licensed and locally installed, used via Display Manager or Enterprise Guide; most likely Windows.
• licensed in a client/server setup, with Enterprise Guide or SAS Studio

The use of a backslash in your LIBNAME lets me suspect a Windows Environment, but if you use UE that can't work.

[AndreaLovdel]

I'm using licensed in a server setup, with Enterprise Guide. So, I'm going through quite a few problems that I can't get those excel files of storm, but now I have them as I manually got them from the studio instructions. 

I also can't complete the next step of creating the NP library, although I managed to create the PG1 library, and everything is set up correctly. I reckon I might not be able to access some files/folders due to using a server?

Many thanks,

Andrea  

[Kurt_Bremser]

You have two options for accessing the Excel file in such a setup:

• Using the EG Import task and wizard; the locally stored file is converted to a textual format by EG (using the JET or ACE engine, supplied by Microsoft), sent to the server, and read there with a data step
• moving the file to the server and reading it there directly through code

The second option can be done with

• a network share, accessible by both your desktop and the SAS server
• copying the file to the server with a utility like WinSCP
• using the Copy Files task in EG

After having the file on the server, the SAS server's operating system determines the correct filename syntax to use for the path, either in a LIBNAME or PROC IMPORT statement.

[Negarev]

Hi, sorry I'm having the same issue: I can't find the Excel file named storm_summary.xlsx

I am using SAS EG 7.15

I have set up the files in the work library.

I have got the files set up:

However, there is no excel file anywhere to be found... I believe many people have asked this question before, but for the life of me I can't figure out what I'm doing wrong...

Please help

[Kurt_Bremser]

Do you use Enterprise Guide in a fully licensed environment (your company or university), or against SAS on Demand for Academics?

[Negarev]

I use SAS EG in a fully licensed environment (my company)

[Al14]

The files should be in the data folder in your screenshot.  Do you see the Excel file in there?

[Negarev]

Yes, that's exactly what I thought, but the data folder is empty, apart from a program that I've saved there:

[Cynthia_sas]

Hi:
The PG1 library only shows you the SAS data sets. You will NOT see the Excel files in the PG1 library. If you navigate to the physical location of the folders on your system, then in the data subfolder you should see all the other files, there are JSON files, CSV files, XLSX files and some other "non-SAS" files in the data subfolder. Then, after you run the data creation program the PG1 library is created and the SAS data files are put into the PG1 library.
Cynthia

[Kurt_Bremser]

If you want to use an Excel file as a library, you have to specify the XLSX engine in the LIBNAME statement.

[Panagiotis]

You will want to use the XLSX engine like mentioned.

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

PROC CONTENTS DATA=XLCLASS.STORM_SUMMARY;
RUN;

LIBNAME XLCLASS CLEAR;

What the XLSX engine does is it let's SAS know you are trying to read an Excel file. Without it, SAS assumes it's reading a .sas7bdat file. 

After you try this can you show us the code and log if it doesn't work. That'll help us. Also, if you can take a screenshot of your pg1 library. I want to make sure you have the data for the course set up correctly.

- Peter