SAS Programming 1: Essentials error

[monazsuzsi]

NOTE: The data set WORK.T_TYPE_CODES has 5 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
123410     ;;;;

123411     
123412     /*************************************************
123413       Build storm.xlsx
123414       2. Export
123415          PG1.STORM_SUMMARY       to tab Storm_Summary
123416          PG1.STORM_DETAIL        to tab Storm_Detail
123417          PG1.STORM_DAMAGE        to tab Storm_Damage
123418          PG1.STORM_RANGE         to tab Storm_Range
123419          PG1.STORM_2017          to tab Storm_2017
123420          PG1.STORM_BASINCODES    to tab Basin_Codes
123421          PG1.STORM_SUBBASINCODES to tab SubBasin_Codes
123422          PG1.STORM_BASINCODES    to tab Basin_Codes
123423          WORK.T_TYPE_CODES       to tab Type_Codes
123424     *************************************************/
123425     LIBNAME xl XLSX "&path/storm.xlsx";
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.
123426     libname pg1 "&path";
NOTE: Libref PG1 refers to the same physical library as WORK.
NOTE: Libref PG1 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\mona\AppData\Local\Temp\SEG12912\SAS Temporary Files\_TD15744_CONTROLLINGPC_\Prc2
123427     options validvarname=any;
123428     data xl.'Storm_Summary'n;
123429        set pg1.storm_summary(rename=(Hem_NS='Hem NS'n Hem_EW='Hem EW'n));
123430        Basin=upcase(basin);
123431     run;

ERROR: Libref XL is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

123432     data xl.'Storm_Detail'n;
123433        set pg1.storm_detail;
123434     run;

ERROR: Libref XL is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

123435     data xl.'Storm_Damage'n;
123436        set pg1.storm_damage;
123437        format date date9. cost comma15.;
123438     run;

Hi!

I would like to take the SAS Programming 1: Essentials Course in SAS Enterprise Guide. In the first section I downloaded, extracted and saved the zip. When I pg1v2_eg code, it says "The XLSX engine cannot be found" and "Libref XL is not assigned"

What can I do?

[ballardw]

For any error copy from the log the code and all messages, notes and errors related to that code. Then on the forum when asking a question about the error, open a text box using the </> icon above the message window and paste the entire bit of text. The text box is important because the message windows on this forum will reformat text and move diagnostic messages that SAS often places in the log.

[FreelanceReinh]

Hi @monazsuzsi and welcome to the SAS Support Communities!

---

@monazsuzsi wrote:

I would like to take the SAS Programming 1: Essentials Course ...

---

I have never taken this (or any other basic SAS) course, but I'm sure it is a great starting point.

---

@monazsuzsi wrote:

... it says "The XLSX engine cannot be found" and "Libref XL is not assigned"

What can I do?

---

It looks like your SAS installation does not include the module SAS/ACCESS Interface to PC File Formats, which contains the XLSX engine. I don't have this module either and get the same error messages.

The good news is: Chances are that you don't really need that module for the course, as it is not necessary at all for learning basic SAS programming. (I'm wondering why a first SAS course would require anything beyond a SAS/Base installation.) Your SAS log (thanks for posting it!) shows that the XLSX engine would be used to create an Excel workbook whose tabs contain the data from datasets pg1.storm_summary, pg1.storm_detail, etc. But these SAS datasets already exist (most likely) as .sas7bdat files in the library pg1, which "was successfully assigned," as the log says. So you have many other and better ways to view the content of those datasets than looking at Excel spreadsheets. For example, you can open a dataset by double-clicking it in the Servers pane of SAS Enterprise Guide.

For the time being I would simply skip all parts of the code that rely on the XLSX engine or Excel itself and whenever the course says something like "look at the tab [say] Storm_Summary of storm.xlsx," just look at the corresponding SAS dataset, i.e., pg1.storm_summary.

We'll be happy to help if other difficulties arise. Enjoy the SAS Programming course!

[Quentin]

Update / Correction to my comment below.  Happily, Cynthia noted that SAS On Demand for Academics does provide the XLSX engine, which is good news.  This means that learners can use it to complete programming essentials, which is great news.

---

@FreelanceReinh wrote in part:

(I'm wondering why a first SAS course would require anything beyond a SAS/Base installation.) 

Agree. There was a thread about the changes to this course a few years ago, but I can't find it.  My surprise was they changed it so that they are no longer teaching how to read text data with an INPUT statement (I think they made it an optional appendix).  I hadn't realized that reading /writing Excel was deemed "essential" for beginner SAS programmers, but I suppose it is, unfortunately. 

I haven't used SAS On Demand for Academics.  But from a quick look, it doesn't have SAS/ACESS to PC files (which you need for XLSX engine), which is surprising to me. I would think it would be good to have learners be able to complete Programming I with SODA.

[Cynthia_sas · Accepted solution]

Hi:
Unfortunately, the XLSX engine IS needed for the Programming 1 class to make the class data and to do the practices. The good news is that if the OP's version of SAS at work does not include the XLSX engine, then it is possible to get a free SAS OnDemand for Academics account which does include the XLSX engine.
The ability to read/write Excel files was one of the big changes in Programming 1 and on the Base Programmer certification exam back in 2018.
One thing we always recommend is that EG students should check with their SAS Admin to find out whether it's possible to upgrade their EG to point to the most current version of SAS. In many shops, they might have several servers with different versions of SAS running, and so it is worth asking. If the shop is not at a point where an upgrade is possible, then we recommend using SAS Studio with SAS OnDemand for Academics to do the work in Programming 1. Learning the SAS syntax is the most important part of Programming 1, and code is code, no matter which interface (EG or SAS Studio) you use.
Cynthia

[FreelanceReinh]

Hi @Cynthia_sas:

@Cynthia_sas wrote:
Unfortunately, the XLSX engine IS needed for the Programming 1 class to make the class data ...

You aren't saying that the class data are provided in the form of Excel files, or? If they were, my workaround would be to save them as .csv files and then write a DATA step to read the .csv files, which would be a useful lesson in itself.

From what the OP posted,

123413       Build storm.xlsx
123414       2. Export
123415          PG1.STORM_SUMMARY       to tab Storm_Summary

I was under the impression that the task was to build an .xlsx file from existing SAS datasets, but obviously I don't see the whole picture.

[Cynthia_sas]

Hi, @FreelanceReinhard:
We use the XLSX engine initally to MAKE the Excel files that students will read in their practices. Students then use the XLSX engine in their practices to both READ/IMPORT the files that we've created for them and to WRITE/EXPORT SAS data to Excel.

So yes, the XLSX engine is used for ALL of those scenarios and we also provide students with CSV, TAB and TXT files to READ/IMPORT, as well. The data creation program that we use makes all the folders and all the files (SAS data sets, Excel files, and raw data files) that are needed for the class practices and demos.

Converting the XLSX files to CSV would defeat the purpose of the practice where we ask them to use the XLSX engine to read the XLSX files that we've created for the class.
Cynthia

[FreelanceReinh]

Thanks for explaining the background. Now it makes sense to me. So, the premise is that XLSX has become such an important file format that even starters need to know how to read and write to those files with SAS.

@Cynthia_sas wrote:
When students use SAS OnDemand for Academics, they only have to upload 1 .SAS program to the OnDemand server.

---

This sounds like a convenient solution to the problem (which was created by moving the actual software away from the learners).

[Reeza]

Disagree - SAS University Edition was likely a support nightmare. They had to support Windows, Unix and Mac users and deal with a lot of different installations with different issues that issues get blamed on SAS. Cloud installations are also more similar to real life enterprise versions that require download/upload. UE also required multiple software applications (Oracle VirtualBox) which end users may upgrade or not at their preference causing further issues. Not to mention various language supports required and all sorts of different hardware support. And it had license limits which are no longer present in On Demand.

Only benefit, in my opinion, to UE was no internet access required and can process larger data sets.

[FreelanceReinh]

Thanks, @Reeza, very good points. I never used SAS University Edition, but I remember many posts on here from users who were confused by the requirements inherent to that concept (virtual Unix machine vs. local PC, "myfolders", etc.). It was rather the SAS Learning Edition that people wanted to get back.

[Reeza]

Ah, that's partially before my time with SAS. Personally, SAS is an Enterprise designed software and the installation does take some technical skills that make it cumbersome for end users who just want to program so I'm still a fan of UE and On Demand which have much better end user experience.

[Quentin]

Yep, SAS learning edition was fun because (IIRC) it was basically plain PC SAS, just crippled to 100 obs or something like that. I did install SAS University Edition a few years ago on my Mac using Virtual Box just for fun. It felt like a headache.

Since now Studio connected to a server in the cloud seems to be becoming the primary programming interface, I agree with Reeza it's probably a good decision to have SODA use studio and cloud. Hoping to play with it soon, just so I can feel better about recommending it to others.

The surprise to me is I don't think there is an equivalent free learning Viya environment, is there? Seems like with all the encouragement to have folks move to Viya, SAS would benefit from giving folks a free path to play with Viya before trying to convince a PHB to buy it.

[Reeza]

There's a two week/30 day trial of Viya but not a longer term one where you could really get a feel for it.

[FreelanceReinh]

@Quentin: I think you mean the great 2019 thread "Programming 1/2 content: removed INFILE and added macro vars?", which I remember, too. It has accumulated a total of 55 likes ...

I also haven't used SAS On Demand for Academics (or its predecessor, SAS UE), but the SAS Learning Edition until it was sadly discontinued.

It seems that @monazsuzsi has the PG1 library on her local computer, which I think is better (for learning purposes) than having to "upload" datasets to a server, virtual machine, cloud or whatever newish user interfaces may demand.

[Cynthia_sas]

Hi:
When students use SAS OnDemand for Academics, they only have to upload 1 .SAS program to the OnDemand server. Then they have to run that program and it makes ALL the folders and ALL the files and SAS data sets that are needed for the course. 1 program, that's all that needs to be used to make the data for class.
Cynthia