XLSX Library issues

[Abastaki]

I've created an excel files library running this command 

options validvarname=v7;

libname xlstorm xlsx '/folders/myfolders/storm.xlsx';

but i can't seem to find the worksheet in the library. so how do i do this so i can access the excel worksheet?

[ed_sas_member]

Hi @Abastaki
Please look at the Error message in the log (red cross)
It seems that the path of the Excel file is not valid. To get the right one, please right click on the file name in the File and Folder part on the left of your screen, and select Properties. You will then be able to copy-paste the path.
Best,

[Abastaki]

There's no error message. And i've tried the path you mentioned before, didn't work either.

[ed_sas_member]

Please post the log

[Gordonmolapo]

Hi,

I have tried to read the storm's excel workbook from the library I have created, however, I am unable to access the file.

[Gordonmolapo]

Attached is the log message after creating the active library.

[Kurt_Bremser]

---

@Gordonmolapo wrote:

Attached is the log message after creating the active library.

---

There is no problem. The library reference was successfully created, now use it.

In the future, post your logs by just copy/pasting them into a window opened with this button:

The "little running man" right next to it is for posting SAS code (provides coloring similar to the SAS Enhanced Editor).

[Gordonmolapo]

Hi Kurt,

I am using SAS Enterprise guide 7.1. Even after successfully creating the active library, the excel sheets do not appear in the library that I have created.

[Kurt_Bremser]

What are the names of your sheets? If they do not follow the rules for valid SAS names, you will have to use

options validmemname=extend;

[Gordonmolapo]

Sheet names

[Gordonmolapo]

I used the code suggested but SAS is only reading the first sheet and not the other sheets.

The solution to my initial problem was regarding the path I was using. I used the network path.

[Kurt_Bremser]

Please post the code you used to actually read that first sheet.

If you want to read all your sheets in one procedure, use PROC COPY:

proc copy
  in=xstorms
  out=work
;
run;

[Gordonmolapo]

libname xstorms '\\HO125489-T\Users\LebohangM\Documents\EPG194\data';

proc import datafile='\\HO125489-T\Users\LebohangM\Documents\EPG194\data\storm.xlsx' 
dbms=xlsx out=xstorms.storm replace;
run;

[Tom]

---

@Gordonmolapo wrote:

libname xstorms '\\HO125489-T\Users\LebohangM\Documents\EPG194\data';

proc import datafile='\\HO125489-T\Users\LebohangM\Documents\EPG194\data\storm.xlsx' 
dbms=xlsx out=xstorms.storm replace;
run;

---

If you do not tell PROC IMPORT which worksheet to import from the workbook it will import the first sheet.

[Kurt_Bremser]

PROC IMPORT without a sheet name will only import the first sheet. IMPORT creates one dataset, so it has to select one sheet, and if you do not tell it which, it defaults to the first.

If you want to import multiple sheets, your best option is PROC COPY from a libname you defined with LIBNAME XLSX.