topic Re: SAS Programming 1: Essentials error in SAS Procedures

SAS Programming 1: Essentials error

monazsuzsi — Tue, 17 Aug 2021 11:20:45 GMT

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?

Re: SAS Programming 1: Essentials error

ballardw — Tue, 17 Aug 2021 10:56:45 GMT

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.

Re: SAS Programming 1: Essentials error

FreelanceReinh — Tue, 17 Aug 2021 14:08:02 GMT

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!

Re: SAS Programming 1: Essentials error

Quentin — Tue, 17 Aug 2021 16:20:24 GMT

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.

Re: SAS Programming 1: Essentials error

Cynthia_sas — Tue, 17 Aug 2021 15:52:26 GMT

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

Re: SAS Programming 1: Essentials error

Reeza — Tue, 17 Aug 2021 15:59:26 GMT

Are you using SAS On Demand or your own version of EG? If you're using your own it's likely you don't have the most up to date version and no license for the XLSX module. What is your version of SAS? And components?
You can view them with the following (results in the log):

*what is licensed;
proc setinit;run;

*what is installed;
proc product_status;run;

Re: SAS Programming 1: Essentials error

FreelanceReinh — Tue, 17 Aug 2021 16:23:23 GMT

@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.

Re: SAS Programming 1: Essentials error

FreelanceReinh — Tue, 17 Aug 2021 16:23:41 GMT

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.

Re: SAS Programming 1: Essentials error

Cynthia_sas — Tue, 17 Aug 2021 16:30:23 GMT

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

Re: SAS Programming 1: Essentials error

Cynthia_sas — Tue, 17 Aug 2021 16:31:45 GMT

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

Re: SAS Programming 1: Essentials error

FreelanceReinh — Tue, 17 Aug 2021 17:02:12 GMT

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

Re: SAS Programming 1: Essentials error

Reeza — Tue, 17 Aug 2021 17:53:33 GMT

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.

Re: SAS Programming 1: Essentials error

FreelanceReinh — Tue, 17 Aug 2021 18:38:40 GMT

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.

Re: SAS Programming 1: Essentials error

Reeza — Tue, 17 Aug 2021 18:50:19 GMT

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.

Re: SAS Programming 1: Essentials error

Quentin — Tue, 17 Aug 2021 19:08:19 GMT

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.

Re: SAS Programming 1: Essentials error

Reeza — Tue, 17 Aug 2021 19:27:44 GMT

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.