topic Re: The Code on SAS onDemand for Academics Stops Working in SAS Programming

The Code on SAS onDemand for Academics Stops Working

Ficu2019 — Thu, 09 Jan 2025 18:36:41 GMT

Hello all,

I am using SASonDemand for Academics. The following SAS codes worked perfectly just about a couple of weeks ago. The codes are as follows:

OPTIONS NOOVP NODATE NONUMBER LS=70 PS=50;

LIBNAME X xlsx '/home/.../.../.../Book2.xlsx';

DATA Weekly; 
   SET X.Sheet1;
   
PROC FREQ DATA=Weekly order=data;
   BY Observation;
   TABLES Depositor/BINOMIAL(p=0.3 level='Paid_Weekly' wald exact) alpha=0.1;

RUN;

When I was trying to run the code again in SASonDemand for Academics this morning, I got the following error message:

The table "WORK.WEEKLY" cannot be opened because it does not contain any columns.

I've attached the original Excel file.

Any idea why the glitch has happened? Thanks.

Re: The Code on SAS onDemand for Academics Stops Working

SASKiwi — Thu, 09 Jan 2025 19:06:41 GMT

Your SAS log should provide evidence of what is happening. Please post the complete log using the </> icon.

Re: The Code on SAS onDemand for Academics Stops Working

Ficu2019 — Thu, 09 Jan 2025 19:22:28 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71 OPTIONS NOOVP NODATE NONUMBER LS=70 PS=50;

73 LIBNAME X xlsx '/home/…/…/…/Book2.xlsx';

NOTE: Libref X was successfully assigned as follows:

Engine: XLSX

Physical Name: /home/.../.../.../Book2.xlsx

75 DATA Weekly;

76 SET X.Sheet1;

ERROR: File X.Sheet1.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.WEEKLY may be incomplete. When this step

was stopped there were 0 observations and 0 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2822.21k

OS Memory 25080.00k

Timestamp 01/09/2025 07:12:40 PM

Step Count 24 Switch Count 2

Page Faults 0

Page Reclaims 737

Page Swaps 0

Voluntary Context Switches 15

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 160

78 PROC FREQ DATA=Weekly order=data;

79 BY Observation;

ERROR: Variable OBSERVATION not found.

80 TABLES Depositor/BINOMIAL(p=0.3 level='Paid_Weekly' wald

80 ! exact) alpha=0.1;

ERROR: Variable DEPOSITOR not found.

82 RUN;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 336.03k

OS Memory 22948.00k

Timestamp 01/09/2025 07:12:40 PM

Step Count 25 Switch Count 0

Page Faults 0

Page Reclaims 30

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: The Code on SAS onDemand for Academics Stops Working

SASKiwi — Fri, 10 Jan 2025 01:42:30 GMT

The key error is this:

ERROR: File X.Sheet1.DATA does not exist.

Your Excel workbook does not have a sheet called Sheet1.

I suggest you check the contents of your workbook like so to confirm the correct sheet names:

LIBNAME X xlsx '/home/…/…/…/Book2.xlsx';

proc datasets library = X;
run;

Re: The Code on SAS onDemand for Academics Stops Working

Ficu2019 — Fri, 10 Jan 2025 01:46:49 GMT

Hello there,

Thanks for your response. I've checked the original Excel file attached, and the tab name is called "Sheet1." Thanks.

Re: The Code on SAS onDemand for Academics Stops Working

Patrick — Fri, 10 Jan 2025 01:53:48 GMT

@Ficu2019 wrote:

Hello there,

Thanks for your response. I've checked the original Excel file attached, and the tab name is called "Sheet1." Thanks.

You need to check the Excel file that SAS accesses. There won't be a sheet called Sheet1 in it.

LIBNAME X xlsx '/home/.../.../.../Book2.xlsx';

Just upload your correct original Excel again to the location to which your libname points to.

Below code allows you to verify which sheets SAS can converts to SAS tables for the Excel to which your libname points to.

LIBNAME X xlsx '/home/.../.../.../Book2.xlsx';
proc contents data=x._all_;
run;

Re: The Code on SAS onDemand for Academics Stops Working

Ksharp — Fri, 10 Jan 2025 01:54:02 GMT

The problem is here:

 74        

 75         DATA Weekly;

 76            SET X.Sheet1;

 ERROR: File X.Sheet1.DATA does not exist.

 77        

 

 NOTE: The SAS System stopped processing this step because of errors.

 WARNING: The data set WORK.WEEKLY may be incomplete.  When this step

          was stopped there were 0 observations and 0 variables.

Your code is not right, Should like:

OPTIONS NOOVP NODATE NONUMBER LS=70 PS=50;

LIBNAME X xlsx '/home/.../.../.../Book2.xlsx';

DATA Weekly; 
   SET X.'Sheet1$'n;
run;

Re: The Code on SAS onDemand for Academics Stops Working

Ficu2019 — Fri, 10 Jan 2025 02:08:08 GMT

Hello there,

Thanks! Yes, it worked! However, what has been puzzling is that the original codes DID work very well until about a couple of weeks ago. That is, the file folder to which the Excel file was imported did not have to be named the same as the library's name. For example, I named the library X for the ease of typing; but, the folder where the Excel resides was meaningful. Any idea why there is this change or was I missing any other info?

Anyway, I found out an alternative by simply creating a permanent SAS dataset without using the libname statement, and it worked, too.

Re: The Code on SAS onDemand for Academics Stops Working

Ficu2019 — Fri, 10 Jan 2025 02:11:20 GMT

Thanks. I will try this method as well. Thanks.

Re: The Code on SAS onDemand for Academics Stops Working

Patrick — Fri, 10 Jan 2025 02:15:42 GMT

@Ksharp You don't need the quoting if the Excel tab name complies with SAS table naming conventions. Below works for me.

LIBNAME X xlsx "/home/&SYSUSERID/temp/Book2.xlsx";
data weekly;
	set x.sheet1;
run;

Re: The Code on SAS onDemand for Academics Stops Working

Patrick — Fri, 10 Jan 2025 02:22:10 GMT

@Ficu2019

@Ficu2019 wrote:

Hello there,

Thanks! Yes, it worked! However, what has been puzzling is that the original codes DID work very well until about a couple of weeks ago. That is, the file folder to which the Excel file was imported did not have to be named the same as the library's name. For example, I named the library X for the ease of typing; but, the folder where the Excel resides was meaningful. Any idea why there is this change or was I missing any other info?

Anyway, I found out an alternative by simply creating a permanent SAS dataset without using the libname statement, and it worked, too.

A libname definition that uses the xlsx engine can have any valid libref (like X). The path must point to the actual Excel file and not just the folder that contains the Excel file.

libname <libref> xlsx '<path>/<name of Excel workbook>.xlsx';
data <sastable>;
  set <libref>.<sheet name>;
run;

Also important: SAS onDemand for Academics runs under Linux which is case sensitive. Make sure that both path and Excel file name use the exact casing.