topic Re: XLSX Library issues in New SAS User

XLSX Library issues

Abastaki — Sat, 27 Jun 2020 11:23:28 GMT

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?

Re: XLSX Library issues

ed_sas_member — Sat, 27 Jun 2020 11:39:40 GMT

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,

Re: XLSX Library issues

Abastaki — Sat, 27 Jun 2020 12:07:55 GMT

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

Re: XLSX Library issues

ed_sas_member — Sat, 27 Jun 2020 12:14:31 GMT

Please post the log

Re: XLSX Library issues

Kurt_Bremser — Sat, 27 Jun 2020 12:32:31 GMT

Since libname xlsx can be used to create a new Excel file, the success of assigning the libname does not mean that SAS has found the file; if it did not find the file, it will create it as soon as you write to the library.

So you must first look in your Server Files and Folders section first if the Excel file is actually in myfolders, and take the spelling from there (for the UNIX system of UE, which is case sensitive, Storm.xlsx and storm.xlsx are different files).

PS if in doubt, show us a screenshot of the open Server Files and Folders section where the Excel file is visible.

Re: XLSX Library issues

AhmedAl_Attar — Sat, 27 Jun 2020 17:03:55 GMT

Follow the instructions from @ChrisHemedinger 2015 Blog

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

Try to copy the data/worksheet into a WORK table.

Hope this helps,

Ahmed

Re: XLSX Library issues

Gordonmolapo — Tue, 05 Apr 2022 14:27:07 GMT

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.

Re: XLSX Library issues

Gordonmolapo — Tue, 05 Apr 2022 14:30:55 GMT

Attached is the log message after creating the active library.

Re: XLSX Library issues

Kurt_Bremser — Tue, 05 Apr 2022 16:03:32 GMT

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

Re: XLSX Library issues

Gordonmolapo — Wed, 06 Apr 2022 07:57:38 GMT

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.

Re: XLSX Library issues

Kurt_Bremser — Wed, 06 Apr 2022 08:04:37 GMT

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;

Re: XLSX Library issues

Gordonmolapo — Wed, 06 Apr 2022 08:45:23 GMT

Sheet names

Re: XLSX Library issues

Gordonmolapo — Wed, 06 Apr 2022 08:48:20 GMT

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.

Re: XLSX Library issues

Kurt_Bremser — Wed, 06 Apr 2022 09:33:06 GMT

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;

Re: XLSX Library issues

Gordonmolapo — Wed, 06 Apr 2022 13:01:31 GMT

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;

Re: XLSX Library issues

Tom — Wed, 06 Apr 2022 13:44:17 GMT

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

Re: XLSX Library issues

Kurt_Bremser — Wed, 06 Apr 2022 15:18:38 GMT

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.

Re: XLSX Library issues

Cynthia_sas — Wed, 06 Apr 2022 20:04:14 GMT

Hi:
I suggest you go back to the Programming 1 class and review the material on using PROC IMPORT. When you are importing from a multi-sheet workbook, PROC IMPORT expects the OUT= to be a SAS dataset. The Excel Workbook is the equivalent of a library containing multiple tables (one table for each sheet). So a single SAS dataset is all that the OUT= option is going to create.
Are you working in Lesson 2? There is a demo in Lesson 2 that you should revisit, as shown below:

In this demo, the instructor uses the XLSX LIBNAME engine to run a PROC CONTENTS on the entire workbook and then, after reviewing the PROC CONTENTS output, she shows how to point PROC CONTENTS to a single sheet in the workbook --also using the LIBNAME engine. PROC IMPORT is NOT used in this demo, at all.
In Lesson 2, PROC IMPORT is used to read a tab delimited file and a CSV file -- but you cannot use the XLSX engine to read either of those types of "raw" or "unstructured" data files.
Please clarify exactly in the course where you are working by providing the title of the Lesson, demo, activity or practice you're working in. Most of the activities and practices have code that you can reveal when you're following the instructions. Usually you can compare your code to the solution to see what you've missed. If you're trying to repeat the demo in Lesson 2, you're not following the demo steps.
Cynthia