How to pull in Excel sheet names

[stippur]

Can anyone please let me know how to pull in an Excel file and be able to figure out the names of the sheet within the file? Please see the following code below... it some how is not working for me.

libname inexcel xlsx 'home/sandhyatippur/cert/input/test_tabs.xlsx';

proc copy in=inexcel out=work;
run;

[Tom]

That won't get the names, it will get the actual sheets.  You could see the name by looking at the log.

Note your path is missing the root node, and so will probably not work since I doubt your SAS session is running with the root node as the current working directory.

libname inexcel xlsx '/home/sandhyatippur/cert/input/test_tabs.xlsx';

If you just want to get the names and not actually copy the data then you can use PROC CONTENTS.

[stippur]

Thanks so much for your help!!!

[stippur]

I ran the proc contents and got this error message. Please advise.

proc contents data='/home/sandhyatippur/cert/input/test_tabs.xlsx';

ERROR: Extension for physical file name "/home/sandhyatippur/cert/input/test_tabs.xlsx" does not correspond to a valid member type.

[SASKiwi]

That's not how you use PROC CONTENTS. Please refer to SAS documentation for the correct usage. This example should help you: https://documentation.sas.com/?docsetId=proc&docsetTarget=n0i4dlelaoofiqn1vg1tsdiq7lfq.htm&docsetVer...

You can use either PROC DATASETS or PROC CONTENTS with the DIRECTORY option.

[stippur]

Thanks for your help. 

[Tom]

You need to use the libref you create with the LIBNAME statement.

proc contents data=inexcel._all_;
run;

[Reeza]

libname inexcel xlsx 'home/sandhyatippur/cert/input/test_tabs.xlsx';

proc contents data=inexcel._all_;
run;

PROC COPY will import all the excel sheets and data ranges that have valid data into your specified SAS library. 

If you want the to only select specific sheets, specify them in the PROC COPY.

proc copy in=inexcel out=work;
select Sheet1 Sheet2;
run;

Note that if your sheet names have spaces you will need to refer to them in the following manner, note the usage of the quotes and n. This is known as the name literal method of referencing a variable and is likely an issue you will run into quickly.

'Sheet Name With Spaces'n

[himself]

Hi could you be having a reference on the same, thanks, the only challenge with this is that one cannot know the first sheet appearing in the xlsx?

[Cynthia_sas]

Hi:
If you use the XLSX engine, you can use PROC CONTENTS. This is the technique we teach in the Programming 1 class. For example, in Programming 1 we have an XLSX file called np_info.xlsx and if that file is stored in this location on my system:
c:\SAS_class\EPG1V2\data\np_info.xlsx

then this code will show you the sheet names. As shown below, my np_info.xlsx workbook has 3 sheets:

When you are done with the file, then you should submit  a libname np clear; to clear the hold on the XLSX file so that SAS does not hold the file open (which would prevent anyone from opening the file in Excel without getting the read-only message from Excel).

Cynthia

[himself]

Hi @Cynthia_sas , thanks, so much for the response, do you mind sharing a link on programming 1 class. I have a look,

Secondly,Is it possible to have the list of sheet names, as they appear in the excel.xlsx engine

[Cynthia_sas]

Hi, here's the link for Programming 1. This is the site for all our e-learning classes and the free classes are listed at the top of this page:https://support.sas.com/edu/elearning.html?ctry=us&productType=library -- just click the orange button that says "Start Now" in order to activate the training. You'll need to sign into your SAS Profile as part of the activation process.

I'm unclear on your second question. PROC CONTENTS shows you the list of sheet names as they appear in the XLSX workbook. I don't quite understand the meaning of "as they appear in the XLSX engine" -- the XLSX engine is the SAS method to read the workbook and treat each sheet as a separate data table. So each sheet has a name. That's what the PROC CONTENTS shows.. You could use PROC SQL as shown in the 07-13-2020 04:10 AM posting by KurtBremser to create just the list of sheet names.
Cynthia

[himself]

Hi @cynthia, so what i mean is for example lets say we have a xlsx
workbook which has the following work sheets, lets say john abel himself
so can see that john is the first sheet followed by abel and himself, so
you realise that after running proc contents , you get the sheet listed in
alphabetical order, yet this is not the order in the workbook, hope its
clear.

Thanks

[Tom]

Don't ask new questions on old threads.

There is actually a more recent thread that actually answers your question.

https://communities.sas.com/t5/SAS-Programming/How-to-import-second-sheet-from-Excel-workbook-in-to-...

[himself]

Thanks @Tom