The Code on SAS onDemand for Academics Stops Working

[Ficu2019]

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.

[SASKiwi]

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

[Ficu2019]

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

 70        

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

 72        

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

 NOTE: Libref X was successfully assigned as follows:

       Engine:        XLSX

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

 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.

 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.

 81        

 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

 83        

 84         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

[SASKiwi]

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;

 

[Ficu2019]

Hello there,

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

[Patrick · Accepted solution]

---

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

[Ficu2019]

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.

[Patrick]

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

[Ksharp]

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;
   

[Ficu2019]

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

[Patrick]

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