Programming 1 Lesson 2, successfully credted library for excel file but no table showed in the lib

[BigGGG]

I successfully created NP Library but there are no tables created in the NP Library. Anyone having the same problem? Thank you.

[ed_sas_member]

Hi @BigGGG 

Could you please share the code you used and post the log?

Prior to this lesson, did you complete successfully the data set up at the very beginning of the course, using the cre8data.sas file ?

 Best,

[BigGGG]

Hi @ed_sas_member 

I have finished the setup before and I even went back to go through it again.

This is my code

options validvarname=v7;
libname NP xlsx "F:\SAS\myfolders\coursedata\EPG194\data\np_info.xlsx";

and here is the log

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         options validvarname=v7;
 74         libname NP xlsx "F:\SAS\myfolders\coursedata\EPG194\data\np_info.xlsx";
 NOTE: Libref NP was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: F:\SAS\myfolders\coursedata\EPG194\data\np_info.xlsx
 75         
 76         
 77         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 89         

Thank you

[Reeza]

Are you using SAS UE or your own installation? 
I noticed the myfolders folder and that's usually used in SAS UE, but you also use your F drive which is not SAS UE so I wonder if you've accidentally used the wrong instruction set somehow?

[BigGGG]

I use UE, but it's capable of reading excel files anywhere in your computer right? I have tried other excel files which are store somewhere else, the same problem happened.

[Reeza]

Not anywhere on your computer, just where you've set up the shared drive space, so in the myfolders. And you wouldn't reference it from the F drive you would need to use the unix format. Can you run the following and check your log and myfolders for the demo.xlsx file?

If it doesn't work, post your log from the code and a screenshot of the myfolders folder in your Files Folders and Server pane.

---

@BigGGG wrote:
I use UE, but it's capable of reading excel files anywhere in your computer right?

libname demo xlsx '/folders/myfolders/demo.xlsx';

data demo.want;
set sashelp.class;
run;

libname demo;

[BigGGG]

It works thank you! I'm just wondering why in the demonstration video she used computer file path (S drive) and it worked every time.

[Reeza]

It varies based on your SAS installation, so if you have a local installation you can use your local drives. If you have a server installation, or SAS UE, then you cannot access your local drives unless you have it set up as a shared drive, which is the myfolders location.

The videos are not customized for each set up, just the setup instructions are customized for each installation type. 

---

@BigGGG wrote:
It works thank you! I'm just wondering why in the demonstration video she used computer file path (S drive) and it worked every time.

---

[Cynthia_sas]

Hi:
That video was recorded using the instructor's classroom machine, that had an S: drive. The setup instructions have specific instructions for University Edition versus SAS OnDemand for Academics.
Every time you see a need for a path, frequently represented by FILEPATH in the starter program, you need to replace FILEPATH with your location. For the instructor, that location was on her S: drive. For Univ Edition, that location should be something like:
/folders/myfolders/EPG194/data
That is ALWAYS the path for the data on University Edition. What the instructor uses in the videos is HER usage scenario. You have to use the actual path that fits YOUR usage scenario.
Hope this explains that the S: drive is a unique location for the instructor and doesn't correspond to what you would need to use.
Cynthia

[makelly]

Hi there, 

I'm having this same problem regarding creating the np library. I am using SAS Enterprise Guide. 

I used the following code: 

libname np xlsx "&path/np_info.xlsx";

My log indicates that the Libref np was successfully assigned.

The 'Physical Name' location appears to reference the work drive on my organization's server. But I do not see 'np' in the list of libraries that appear in my work drive. 

Also, I do not see any xlsx type files in my course folder. 

Any help you could provide would be greatly appreciated! Thank you in advance. 

[Cynthia_sas]

Hi:
On Enterprise Guide, your PG1 folder will ONLY show you the SAS data files that were created. The XLSX file will not be visible in EG unless you follow the physical path to the work location, as shown in the PROC CONTENTS output after the data is created. EG students typically do not have read/write access to their local C: drive when SAS is running on a server, so we make a "helper" variable called &PATH as part of the data setup process. &PATH will always show the physical folder location of the WORK location. Did your code have libname np clear; at the end of the program? if so, what happened was that you referenced the NP library and then you immediately closed it. But another issue that you might have with Enterprise Guide is that you need to be running SAS 9.4 maintenance release 2 or higher in order to use the XLSX libname engine. Did you get any XLSX engine errors when you ran the data creation program? If you did get errors, that's a clue that your corporate SAS image did not have the correct version of SAS to make the class data. But, if the data creation program worked for you, what happens is that we tell students to make a AUTOEXEC project so that every time they open the project, the data is created over again in their WORK location. Your data, when practicing on EG is not persistent. As soon as you close EG, your WORK location is deleted. The next time you want to work with the class data, you need to open the class project and re-create the data files. When you run the data creation program, the PROC CONTENTS will show you the folder path you need to go to to see ALL the files -- the sas7bdat files and the XLSX, CSV and other files.
Cynthia

[makelly]

Hi Cynthia, 

Thank you so much for your response!

I believe I have figured out the NP activity. I think the the np library may not have been showing up because I wasn't hitting the 'refresh' button in the server window. 

However, I'm still a bit unclear on how to access the Excel files. 

I saved the course folder on my C drive. So, when I go to my C drive, open EPGIV2_EG, and then open the the 'demos' sub-folder, I see only 29 files all with a .sas extension. 

Should I be seeing the xlsx and csv files in this folder too?

When I run the pg1v2_eg program (including the PROC CONTENTS step), I see from the Physical Name entry in my Results window that my data is saved in what looks like the work directory of my organization's SAS server. When I expand the work directory in the server window, I see that 24 data sets appear to be saved here. 

Does that sound right to you?

Many thanks!
Marian

[Cynthia_sas]

Hi:

  NO, you should NOT see the XLSX files in any of the sub-folders for activities, demos, practices or output. If you were running on a local installation of SAS, you would see the XLSX files in your data sub-folder. However, Enterprise Guide users have a special set of instructions. All of the EG data is created in the WORK location, which is a temporary location. On my system, my WORK location is a very, very long physical name that changes each time I open Enterprise Guide. For example, in this video https://youtu.be/mcysTyfVSM4?t=7  (for EG7 making the data) at time stamp 2:13, you see the very, very long physical location where ALL the data files were written:

  Then, at time stamp 3:29 in the video, you will see a view of the PG1 library in the Library window and you do not see any XLSX files in the LIBRARY, that's because SAS libraries only show you SAS format data sets in a library view. If you want to see ALL the files in this temporary WORK location, including the XLSX files, you would have to navigate to that particular location. This is why we make a helper variable called &PATH that stores this location so you can use it in your code. Then at time stamp 5:52, after I close and open EG a second time, the data is made over again in the new WORK location, you see this:

Here's a better closeup of the two different paths that are shown inside the video:

And you can see that each time I start EG and run the data creation program, my physical location for the class data, including the XLSX files is different.

  You could always open Windows File Explorer while EG is open and navigate to that location. Or, within EG, under the Libraries icon, you should see a Files icon and you can navigate down your file path until you get to the physical name. Here's a screen shot of an Enterprise Guide 8 session, with the FILES icon in EG expanded until you can see all the XLSX files in that long location:

This is why we tell EG users to trust that the files have been created, and to use &PATH as the helper variable that holds the location of the data files. There's no folder structure in this operating system WORK location.

Hope this helps,

Cynthia

[makelly]

Hi again Cynthia, 

Thank you so much for your detailed explanation! I believe I understand now. 

I think I was getting confused because I had thought users were being encouraged to open the Storm.xlsx file in Excel at the beginning of the demo on using a library to read Excel files, but as I re-watch it, I believe the intention was for users to follow along on screen.

I have not succeeded in navigating to the corresponding physical name location in the Windows File Explorer view. Perhaps that's a bit more complicated when your data is stored on a server? But I had no problem progressing through the rest of the lesson. And it looks and sounds like all of the data files that I need are contained in the &PATH variable and I needn't worry! So, I think I'm all good from here. 

Thanks again!
Marian

[Cynthia_sas]

Yes, if your WORK folder is on a server then it is possible for navigation access to be difficult. That's why we make the &PATH helper variable. In the demo, the instructor is able to open the XLSX file because it resides on her accessible drives. If students can follow-along that's fine, but if not, then that is why we have &PATH and the XLSX engine.
Cynthia