Proc Contents and Libname

[Kurt_Bremser]

Open the xlsx file with Excel (if it actually exists) to see which spreadsheets are contained in it.

[Cynthia_sas · Accepted solution]

Hi:
One way to see the names of the sheets in the XLSX file is to use the XLSX LIBNAME engine. For example, we have a multi-sheet workbook called STORM.XLSX in the EPG1V2 data folder. So with this LIBNAME XLSX (below) statement, you could run the PROC CONTENTS to see the names of all the SHEETS. And one of the sheets is named storm_2017, so this code would work to do the PROC CONTENTS and then print some rows from the storm_2017 sheet:
libname xl_storm xlsx 's:\workshop\EPG1V2\data\storm.xlsx';  /* windows */

**OR;

libname xl_storm xlsx '/home/<userID>/EPG1V2/data/storm.xlsx'; /* SAS OnDemand */

proc contents data=xl_storm._all_;
run;

proc print data=xl_storm.storm_2017 (obs=20);
run;
libname xl_storm clear;

The other way to see the names is to open the file with Excel. However, the XLSX file won't be opened on the SAS OnDemand server because it is a Linux server and it doesn't have Excel. so the PROC CONTENTS method is the programmatic way to see the sheets in the workbook.
Cynthia

[Sassy_lady]

Yes, thank you again. I changed the libname filepath to "~/EPG1V2/data/" instead of the "s:workshop/EPG1V2/data" since I am using SAS studio. 

[tom_grant]

Hello,

You need to replace 

s:/workshop/data/storm.xlsx 

 in the libname statement with the path to the storm.xlsx file in your environment, which I suspect (form previous screenshot) would be /home/u60771588/EPG1V2/data/storm.xlsx or ~//EPG1V2/data/storm.xlsx ( the ~ symbol will automatically point to YOUR home directory, /home/u60771588).

Anytime you need to find the exact path to a folder or data table, you can right-click on the folder or table from Server Files & Folders tab & then select "Properties" - this will show you the path & you can copy & paste that into your code.

[Sassy_lady]

Yes, thank you. I changed the libname filepath to "~/EPG1V2/data/" and it worked.

[Alexo2025]

Yes, it is my question also!

Thank you!

[Jay_Cao]

there is a typo in your "proc content" statement, "class_birthdat" is the wrong spelling, it is missing "e".