topic Re: How to pull in Excel sheet names in SAS Programming

How to pull in Excel sheet names

stippur — Sun, 12 Jul 2020 21:02:34 GMT

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;

Re: How to pull in Excel sheet names

Tom — Sun, 12 Jul 2020 21:08:49 GMT

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.

Re: How to pull in Excel sheet names

stippur — Sun, 12 Jul 2020 21:21:00 GMT

Thanks so much for your help!!!

Re: How to pull in Excel sheet names

stippur — Sun, 12 Jul 2020 21:46:07 GMT

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.

Re: How to pull in Excel sheet names

SASKiwi — Sun, 12 Jul 2020 22:35:36 GMT

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&docsetVersion=9.4&locale=en

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

Re: How to pull in Excel sheet names

Tom — Sun, 12 Jul 2020 23:08:38 GMT

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

proc contents data=inexcel._all_;
run;

Re: How to pull in Excel sheet names

Reeza — Sun, 12 Jul 2020 23:35:07 GMT

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

Re: How to pull in Excel sheet names

stippur — Mon, 13 Jul 2020 04:31:39 GMT

Thanks for your help.

Re: How to pull in Excel sheet names

Kurt_Bremser — Mon, 13 Jul 2020 08:10:41 GMT

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;

Re: How to pull in Excel sheet names

rabindrakumar1 — Mon, 13 Jul 2020 08:20:13 GMT

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;

Re: How to pull in Excel sheet names

stippur — Mon, 13 Jul 2020 17:51:00 GMT

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.

Re: How to pull in Excel sheet names

Reeza — Mon, 13 Jul 2020 17:55:24 GMT

Show the log with the error messages.

Re: How to pull in Excel sheet names

stippur — Mon, 13 Jul 2020 18:31:00 GMT

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.

Re: How to pull in Excel sheet names

Vince_SAS — Tue, 14 Jul 2020 11:42:22 GMT

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

Re: How to pull in Excel sheet names

Reeza — Mon, 13 Jul 2020 19:01:41 GMT

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.

Re: How to pull in Excel sheet names

Kurt_Bremser — Mon, 13 Jul 2020 19:18:50 GMT

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

Re: How to pull in Excel sheet names

stippur — Tue, 14 Jul 2020 05:33:00 GMT

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;

Re: How to pull in Excel sheet names

stippur — Tue, 14 Jul 2020 05:33:02 GMT

Thanks so much for your help.

Re: How to pull in Excel sheet names

Kurt_Bremser — Tue, 14 Jul 2020 05:41:37 GMT

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

Re: How to pull in Excel sheet names

himself — Tue, 29 Jun 2021 20:31:47 GMT

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?