Error in XLSX engine - SAS/ACCESS Interface to PC Files

[JalloClem]

Hi everyone,

I'm taking part in SAS Programming 1 course and the first issue that I see is that the autoexec regarding the import of the data for the entire course goes in error when I try to use the libname - XLSX engine. I went through a lot of similar topics but no solutions are working.

In particular, I'm using SAS Enterprise Guide 7.1 and when I code :

libname NP XLSX "&path\np_info.xlsx" ;

the log returns me that "XLSX engine cannot be found".

Running the proc setinit, I have SAS/ACCESS Interface to PC Files still valid.

Could you please help me?

Thanks,

Gianluigi

[Reeza]

Run, product_status proc. SETINIT tells you what's licensed but not installed. PRODUCT_STATUS will tell you what's installed. I suspect you don't have a portion of the installation you need.


proc product_status;run;

[JalloClem]

Thanks both!

Running the product_status that's what it results:

For Base SAS Software ...
Custom version information: 9.4_M3
Image version information: 9.04.01M3P062415
For SAS/STAT ...
Custom version information: 14.1
For SAS/GRAPH ...
Custom version information: 9.4_M3
For SAS Enterprise Miner ...
Custom version information: 14.1
For High Performance Suite ...
Custom version information: 2.2_M4
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M3

Noway to continue the course with my Enterprise Guide?

[Cynthia_sas]

Hi:
To create the data successfully for Programming 1 when using Enterprise Guide, you need to be running SAS 9.4 Maintenance release 2 or higher on your server (Enterprise Guide will work with multiple versions of SAS on a server -- you need to know specifically what version you're running). Also, you need to have SAS/Access to PC File Formats installed for correct access to the XLSX Libname engine. If you are running a lower version of SAS, such as SAS 9.4 M1 or you have 9.4M5 but you don't have SAS/Access for PC File Formats, the data creation program will not work.
Your choices are to 1) work with your SAS Administrator to find out whether you can get access to the correct level of SAS and SAS/Access products or 2) use SAS OnDemand for Academics as an independent learner to complete the Programming 1 course setup (you would have to use SAS Studio instead of EG).
Hope this helps,
Cynthia

[JalloClem]

Thanks Cynthia,

as I stated above, this is my situation after running poduct status:

For Base SAS Software ...
Custom version information: 9.4_M3
Image version information: 9.04.01M3P062415
For SAS/STAT ...
Custom version information: 14.1
For SAS/GRAPH ...
Custom version information: 9.4_M3
For SAS Enterprise Miner ...
Custom version information: 14.1
For High Performance Suite ...
Custom version information: 2.2_M4
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M3

It seems to satisfy all the requirements. Could it be an extension/update to solve this issue?

[Reeza]

Can you show the full code you ran and the exact log error? Maybe you just made a mistake in the program somewhere otherwise from what I'm seeing you should be fine. Did you run the instructions specifically designed for EG?
And what did you set &path to ?

[JalloClem]

Yes, sure. The code itself is huge and just copy/pasted following the instructions in the course.

The log displaying the error is:

 LIBNAME xl XLSX "&path/storm.xlsx";
ERROR:  XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.

In the following libname, for example - translated from Italian :

libname pg1 "&path";

NOTE: The libref PG1 refers to the same physical WORK library.

NOTE: The libref PG1 has been correctly assigned as follows:

Engine: V9

Path is : C:\Users\JalloClem\AppData\Local\Temp\SEG16840\SAS Temporary Files\_TD13044_ITPC000321_\Prc2

but the same problem occurs when I use excel files on the desktop

[Reeza]

You cannot run that program to your work library. Aren't you supposed to create a folder somewhere and use that folder? I thought that was the instruction?

This creates the files for the course in the work library and when you exit your session the work library is cleared and all the files are cleared. So you'd have to set it up every single time.

[Cynthia_sas]

Hi,
For EG users, so many cannot write to/from their local machine, that we make all the data in WORK each time the EG project starts (for Programming 1 and Programming 2 and other e-learning classes). We tell them to make a process flow called AUTOEXEC and so, assuming they use the same project for their class work, the class data does get created correctly for them every time they open the project. And, as part of the startup code, we create a macro variable called &PATH that holds the location of the WORK folder.

This is different than the instructions for ALL other methods of making the data, but it has worked reliably for EG users -- we used to have to deal with problems from EG users would could not read/write to their local C: drive from the SAS server -- but WORK is an allowed location. So EG users do not have the same folder structure as other uses, but we tell them in EVERY exercise to use &PATH for their data location.

It looks like the server does meet all the requirements from the PROC PRODUCT_STATUS, but looks like the server does NOT meet all the requirements based on the XLSX error message in the log..

I do NOT know why the XLSX engine is not found since you do seem to have all the right levels, as described here: https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/ -- so I'm stumped. The only thing I can think is that the server where you are running the PROC PRODUCT_STATUS is different from the server where EG is running the code, but that would only make sense in a multi-server scenario, like a BI install with multiple servers. When you start Enterprise Guide, do you need to log onto a SAS Profile to do some of your work. Your SAS Profile can point you to a specific server.

If you send mail to curriculumconsulting@sas.com, please let us know what email address you use for your e-learning so we can check that you have used the correct instructions (there are currently 2 versions of Programming 1 out there). If it turns out that the Curriculum Consultants can't help you get your code working and the issue turns out to be your server, then we can transfer your track to Tech Support for more in-depth help.
Cynthia

[Reeza]

I almost wonder if the courses shouldn't be redesigned to work off a GitHub repository but as I write that I realize that means you need an external internet connection so that won't work for everyone either....I do not envy software developers dealing with multiple tools + OS + versions.

[Cynthia_sas]

Hi, Reeza:
The SAS OnDemand instructions work like a charm. Since the OnDemand server is on our network, we have a program that downloads the right zip file and executes the right program to make the data. University Edition works well, as long as folks set up their shared folder location correctly and follow the right set of instructions. It is the EG instructions which were the hardest, since we can't be assured that EG folks have write access to a folder. We used to make them go to their administrator to get folder access and so this new method of writing to WORK for EG students is a huge improvement over that -- as long as they have the right level of software. I really think we've got the best approach now.

And, we've posted a new set of videos for the EPG1V2 version of the Programming 1 course -- complete with EG7 and EG8 instructions. Take a look here: https://communities.sas.com/t5/SAS-Communities-Library/Creating-Data-for-Programming-1-and-Programmi... and click on the big orange button for EPG1V2 that you'll see in the middle of the page. (And the older EPG194 video link is there too).
Cynthia

[JalloClem]

It's clear. So I can't see any solution to my problem. Could you suggest me any workaround? Maybe I could skip the mid-course short quiz requiring xlsx engine, but do you know if there is something concerning xlsx engine required in the final test?

I'm not a real novice in using SAS (I use it for 3 years for my job), so if xlsx engine could be skipped temporarly but I could be able to go further over the online course, for me it would be fine.

In the meantime, I wrote curriculumconsulting@sas.com as you suggested, I'll let you know their reply.

[Cynthia_sas]

Hi:
I think the quickest thing to do to get working is for you to use a SAS OnDemand for Academics independent learner account in order to practice using the XLSX engine. The XLSX engine is needed to make ALL the Excel data for class on Enterprise Guide. So if you do not have access to the XLSX engine, then you don't have the complete data used in class. And, you won't be able to do any of the work in class that uses the XLSX engine. It's going to be more than just one quiz that you need to skip.
You're going to have to work with Tech Support and your SAS Administrator to find out why the XLSX engine doesn't work on your system. Tech Support will need to have the SAS Administrator check things on the server that you're not going to be able to do or change.
That's why I recommend SAS OnDemand for Academics as the best workaround in this situation. Here's the content guide for the Base SAS Programmer certification: https://www.sas.com/content/dam/SAS/documents/technical/certification/content-guide/specialist-base-... you can determine for yourself how much of the content on the exam will be related to the XLSX engine.
Cynthia

Additional test of XLSX engine:

  In order to determine whether the issue is with your system, please submit this code in a new EG project. It only uses SASHELP.CLASS:

** start;
%put =======> System is: &sysvlong4;
%put =======> Site number is: &syssite;
%put =======> Operating system is: &sysscp &sysscpl;
proc setinit;
run;
  
%let path=%sysfunc(pathname(work));
  
**1) test export using XLSX engine;
libname myxlout "&path/class_copy1.xlsx";
  
data myxlout.newclass;
  set sashelp.class;
  where age le 12;
run;
  
proc print data=myxlout.newclass;
  title '1) Export using Excel workbook and XLSX libname engine';
run;
libname myxlout clear; 
   

**2) test import using XLSX engine;
libname myxlin "&path/class_copy1.xlsx";

data work.class_import;
  set myxlin.newclass;
run;
libname myxlin clear; 

proc print data=work.class_import;
  title '2) After importing from XLSX file';
run;
   
** 3) Test PROC IMPORT with DBMS=XLSX;
options validvarname=v7 msglevel=I;
proc import datafile="&path/class_copy1.xlsx"
     out=work.class_copy2 
     dbms=xlsx replace;
	 sheet=newclass;
run;
 
proc print data=work.class_copy2;
  title '3) using PROC IMPORT with DBMS=XLSX';
run;
** end;

If the above code fails, then the log from the program will be sufficient to open a track with Tech Support on this issue. If the above code works, then you need to re-test the data creation programs from the class. If the above program works, then the data creation program for EG should work too.