topic Re: help reading an excel file with multiple sheets in SAS Studio

help reading an excel file with multiple sheets

telligent — Thu, 28 Sep 2023 04:41:54 GMT

I tried two ways. Using proc import, I can get one sheet if I leave off the sheet=, but with sheet=

I get ERROR: Couldn't find sheet in spreadsheet. So, I don't know how to figure out what SAS thinks the sheet names are.

options validvarname=v7;

FILENAME REFFILE FILESRVC FOLDERPATH='/Users/xxxxxxx' FILENAME='2018_National_Awardees.xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.Awardees_2018;

sheet='2021 Age and Race-Ethnicity';

GETNAMES=YES;

RUN;

Second, I tried this:

libname test XLSX "/Users/xxxxxxx/2018_National_Awardees.xlsx";

And I get

WARNING: No matching members in directory.

Re: help reading an excel file with multiple sheets

Kurt_Bremser — Thu, 28 Sep 2023 06:03:02 GMT

/Users at the start of your path lets me suspect MacOS. SAS does not run on this system.

If you use a Mac with SAS Studio, you have to upload the file first and use the path on the SAS server.

Re: help reading an excel file with multiple sheets

telligent — Thu, 28 Sep 2023 17:31:28 GMT

The files have been uploaded to SAS studio. I am still having trouble reading them. And I am on a Windows 11 machine but working in Viya in the cloud.

Re: help reading an excel file with multiple sheets

Kurt_Bremser — Thu, 28 Sep 2023 18:15:06 GMT

Make sure that you use exact spelling for file and path names. Are you sure (e.g.) it is "Users" and not "users"?

UNIX (and therefore Linux) is case sensitive.

Right-click on the file in the SAS Studio navigation window and copy the path from there.

Re: help reading an excel file with multiple sheets

Tom — Thu, 28 Sep 2023 18:54:34 GMT

I doubt that the 2021 sheet is in the 2018 workbook.

Re: help reading an excel file with multiple sheets

telligent — Thu, 28 Sep 2023 22:15:16 GMT

Thank you. That fixed the proc import. The libname method is still not working, so I won't accept solution immediately. I will if I don't get any input on that.

Re: help reading an excel file with multiple sheets

Kurt_Bremser — Fri, 29 Sep 2023 05:16:54 GMT

When the FILENAME pointing to the file service works, then I doubt that you can create a LIBNAME, as you need a physical path in the operating system's file system for that, which the Viya file service does not provide.