topic Re: Unable to see the worksheets in the library after using the xlsx engine in SAS Certification

Unable to see the worksheets in the library after using the xlsx engine

Uma_Devi — Wed, 17 May 2023 13:32:00 GMT

I used the following procedure to create a library using xlsx engine. there are twenty worksheets in the excel file. But in proc contents I am not able to see why?

libname xyz xlsx 'C:\Users\Mallik\Desktop\94basepe\input\SDTM_METADATA.xlsx';
run;
proc contents data =xyz._all_ ;
run;
proc datasets lib=xyz;
quit;

Log:

libname xyz xlsxl'C:\Users\Mallik\Desktop\94basepe\input\SDTM_METADATA.xlsx';
NOTE: Libref XYZ was successfully assigned as follows:
Engine: EXCEL
Physical Name: C:\Users\Mallik\Desktop\94basepe\input\SDTM_METADATA.xlsx
19626 run;

when I use proc contents or proc datasets I am only able to see the following without the list of the sheets in the workbook. The library when verified by opening also had no files. Why did this happen???

Re: Unable to see the worksheets in the library after using the xlsx engine

Kurt_Bremser — Wed, 17 May 2023 13:42:02 GMT

Since LIBNAME XLSX can be used to create a spreadsheet file, success of the LIBNAME statement does not mean the file is actually there; if it's not there, it would be created with the first write operation to it.

Run this:

libname xyz xlsx 'C:\Users\Mallik\Desktop\94basepe\input\SDTM_METADATA.xlsx';

data xyz.class;
set sashelp.class;
run;

and look at the log.

BTW LIBNAME is a global statement, which is executed immediately and needs no RUN.

Re: Unable to see the worksheets in the library after using the xlsx engine

Uma_Devi — Wed, 17 May 2023 13:51:39 GMT

I had to access an excel workbook with several sheets and see the names of
the sheets. The workbook is supposed to have many sheets. But none were
visible in the proc contents procedure after creating the library with the
name of the xlsx file -- SDTM_metadata.

Re: Unable to see the worksheets in the library after using the xlsx engine

Kurt_Bremser — Wed, 17 May 2023 14:10:26 GMT

Please post the log from the code I gave you.

Re: Unable to see the worksheets in the library after using the xlsx engine

Mark2010 — Wed, 17 May 2023 14:27:43 GMT

@Uma_Devi - Based on some earlier conversations, I think you sometimes use On Demand for Academics. Are you using On Demand for Academics for this code?

Re: Unable to see the worksheets in the library after using the xlsx engine

Uma_Devi — Wed, 17 May 2023 15:11:39 GMT

I used Sas 9.4. And this was during my certification test that I had this
problem. While practicing both on Sas studio and Sas 9.4 at home for the
exam, i had no issues.

Sincerely
Uma

Re: Unable to see the worksheets in the library after using the xlsx engine

Mark2010 — Wed, 17 May 2023 15:26:13 GMT

If using a locally installed version of SAS and accessing local files, then you should be OK. But following up on the point made by @Kurt_Bremser , The LIBNAME statement is going to work even if the argument for the Excel file location is wrong. Here is the example of me running your code on my computer:

Obviously I don't have your file nor your folder structure, but notice my LIBNAME statement does not produce an error. As expected, nothing is displayed in the PROC CONTENTS. I suggest that there is something wrong (such as a typo or the file doesn't exist) with the 'C:\Users\Mallik\Desktop\94basepe\input\SDTM_METADATA.xlsx' argument in your code.