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;
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.
I ran the proc contents and got this error message. Please advise.
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.
Thanks for your help.
You need to use the libref you create with the LIBNAME statement.
proc contents data=inexcel._all_;
run;
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
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
Don't ask new questions on old threads.
There is actually a more recent thread that actually answers your question.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.