How to pull in Excel sheet names

[Kurt_Bremser]

Query the proper DICTIONARY table:

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

proc sql;
select memname from dictionary.tables where libname = "INEXCEL";
quit;

[rabindrakumar1]

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

proc import datafile="/home/sandhyatippur/cert/input/test_tabs.xlsx"

       out=test dbms=xlsx

       replace;

getnames=yes;

run;

[stippur]

I tried using proc import like you mentioned and the error message I got is:

ERROR: Physical file does not exist,
/home/sandhyatippur/cert/input//test_tabs.

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

proc import datafile='/home/sandhyatippur/cert/input/test_tabs.xlsx'
out=test dbms=xlsx
replace;
getnames=yes;
run;

please advise. Thanks.

[Reeza]

Show the log with the error messages.

[stippur]

please see error from my log

73 libname inexcel xlsx '/home/sandhyatippur/cert/input/test_tabs.xlsx';
NOTE: Libref INEXCEL was successfully assigned as follows:
Engine: XLSX
Physical Name: /home/sandhyatippur/cert/input/test_tabs.xlsx
74
75
76
77 proc import datafile='/home/sandhyatippur/cert/input/test_tabs.xlsx'
78 out=test dbms=xlsx
79 replace;
80 getnames=yes;
81 run;
ERROR: Physical file does not exist,
/home/sandhyatippur/cert/input//test_tabs.
NOTE: The SAS System stopped processing this step because of errors.

[Vince_SAS]

Add this line of code right before your LIBNAME statement, and respond with the result shown in the log:

%put &=SYSSCPL;

Vince DelGobbo

SAS R&D

[stippur]

Please see the log message below.

%put &=SYSSCPL
74
75 libname inexcel xlsx '/home/sandhyatippur/cert/input/test_tab.xlsx';
SYSSCPL=Linux libname inexcel xlsx
'/home/sandhyatippur/cert/input/test_tab.xlsx'
76
77 proc sql;
78 select memname from dictionary.tables where libname ="inexcel";
NOTE: No rows were selected.
79 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 5291.93k
OS Memory 33960.00k
Timestamp 07/14/2020 05:29:26 AM
Step Count 24 Switch Count 0
Page Faults 0
Page Reclaims 534
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0
80
81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

[Kurt_Bremser]

Libnames in SAS can be written in lowercase, but SAS itself keeps them always in uppercase, so your SQL statement must be:

select memname from dictionary.tables where libname ="INEXCEL";

to have any chance of succeeding.

If the SQL still returns no rows, then the Excel file has no sheets (unlikely), or it is not there (very likely).

[Reeza]

Go to Servers Folders and Files, find the file and then right click it and select properties. That will show you the path to your files.

[Kurt_Bremser]

The success of the LIBNAME statement tells you nothing, as it can be used in this way to create a new Excel file (which will be created physically when the first table is written to it).

When SAS tells you a file is not there, then it is not there, period.

Check the presence of the file (log on to the server and do directory listings with ls), and make sure you have the correct spelling (keep in mind that the UNIX system is case sensitive, Test_tabs.xlsx is different from test_tabs.xlsx).

[stippur]

Thanks so much for your help.

[himself]

This is a very nice response, thanks, just a quick one, can one get the sheets as they occur in the .xlsx, seems the dictionary.table lists the sheets in ascending order