Libref is not assigned with xlsx engine in Libname (Invalid option name GOPTIONS)

[LL5]

Hi everyone,

I am trying to assign a libref with xlsx engine and seeing the below syntax error.

However, when I run another libname statement which is almost identical to the first one, the libref was successfully assigned.

I need some helps for the below two questions. Any advices are greatly appreciated.  

1. How to fix the error in the first libname statement? (I need to assign the libref to my own user folder as I have write permission to it)

2. Why was the second libname statement could run successfully since it's almost identical to the first one? (I do not have write permission to this folder)

libname qc2 XLSX "&rf_dpath_me./&pym6./qc_output_&pym6..xlsx" /* Try to point to my own folder which I have write permission*/

GOPTIONS ACCESSIBLE;

40         libname qc2

SYMBOLGEN:  Macro variable RF_DPATH_ME resolves to /workspace/users/a12345

SYMBOLGEN:  Macro variable PYM6 resolves to 201909

SYMBOLGEN:  Macro variable PYM6 resolves to 201909

40       !                 XLSX "&rf_dpath_me./&pym6./qc_output_&pym6..xlsx"

41        

42         GOPTIONS NOACCESSIBLE;

           ________

           22

ERROR: Libref QC2 is not assigned.

ERROR: Error in the LIBNAME statement.

ERROR 22-7: Invalid option name GOPTIONS.

However, when I run the below, the libref was successfully assigned.

libname qc2 XLSX "&rf_dpath./&pym6./qc_output_&pym6..xlsx"; /*  I don't have write permission to this folder*/

GOPTIONS ACCESSIBLE;

40         libname qc2

SYMBOLGEN:  Macro variable RF_DPATH resolves to /workspace/data/project

SYMBOLGEN:  Macro variable PYM6 resolves to 201909

SYMBOLGEN:  Macro variable PYM6 resolves to 201909

40       !                 XLSX "&rf_dpath./&pym6./qc_output_&pym6..xlsx";

NOTE: Libref QC2 was successfully assigned as follows:

      Engine:        XLSX

      Physical Name: /workspace/data/project/201909/qc_output_201909.xlsx

[Tom]

Were you trying to do this?

libname qc2 XLSX "&rf_dpath./&pym6./qc_output_&pym6..xlsx" access=readonly ;

[LL5]

Hi Tom. Thanks for your response. No, I need to assign the xlxs file to my user folder as I need to perform some additional steps. For the libname/libref which was successfully assigned, since I don’t have write permission to it, I cannot perform any additional steps. And that’s why the reason I need to change the libname path to my user folder, but it didn’t work.

[Tom]

---

@LL5 wrote:
Hi Tom. Thanks for your response. No, I need to assign the xlxs file to my user folder as I need to perform some additional steps. For the libname/libref which was successfully assigned, since I don’t have write permission to it, I cannot perform any additional steps. And that’s why the reason I need to change the libname path to my user folder, but it didn’t work.

---

I don't understand the question.  

If you want to MAKE a flle in your user folder then use the name of your user file in the name of the file.

Do you now know where your user folder is? 

If SAS is running on Unix then usually you can use ~ as a nickname for your home directory.

libname xxx xlsx '~/myfile.xlsx';
data xxx.sheet1;
 set sashelp.class;
run;

[LL5]

Thanks Tom for your advice. 

[Shmuel · Accepted solution]

Pay attention to the message

ERROR 22-7: Invalid option name GOPTIONS.

it is because you miss a semicolon ( ; ) at the end of the libname statement. 

libname qc2 XLSX "&rf_dpath_me./&pym6./qc_output_&pym6..xlsx";

[LL5]

Hi Shmuel. Thanks a lot for pointing this out. I am missing a semicolon.