topic Re: SET statment for Excel file in SAS Studio

SET statment for Excel file

marianhabesland — Thu, 14 Sep 2017 15:13:59 GMT

I am supposed to create a LIBNAME statement to connect SAS to an Excel workbook named Diseases.xslx. There is a worksheet names FluData that may appear in the SAS library as FluData$

LIBNAME PubHlth EXCEL "C:\Diseases.xlsx";

DATA WORK.FluAnalysis;

SET [this needs to be specified];

RUN;

Is the SET statement PubHealth.Diseases or PubHealth.FluData$?

Re: SET statment for Excel file

RW9 — Thu, 14 Sep 2017 15:42:56 GMT

First, don't code all in uppercase, it makes code harder to read.

If you need to know the names then open the library you create in the first line in the explorer - this will show you all the datasets available.

Re: SET statment for Excel file

marianhabesland — Thu, 14 Sep 2017 15:57:56 GMT

The code provided to me was in all caps, so that's why it is that way. This question is hypothetical, so it is not actual data I have. The question is basically what you in general would put in the SET statement if you want to open an Excel file, whether it is the worksheet called FluData$ or the workbook called diseases.

Re: SET statment for Excel file

RW9 — Thu, 14 Sep 2017 16:13:36 GMT

Well, hypothertically, I would put in the set statement whatever is created by SAS when it reads the Excel file, what else would you like me to say? Libname excel creates a libname to and Excel file, and basically does a proc import on what it finds and can process within the Excel file. If you file is a graph you will not get anything, if there are tabs of tabulated data then one dataset for each tab would be created, and tab name would be created a SAS compliant name or use literal naming, e.g. 'xyz'n.

Re: SET statment for Excel file

Cynthia_sas — Sat, 16 Sep 2017 13:38:49 GMT

Hi:
The answer to your question depends on the LIBNAME statement you use to point to the Excel file. In your code, you show the use of the Excel engine, with the Excel engine, you would need to use the $ in the sheet name, as shown in this paper http://www2.sas.com/proceedings/sugi31/020-31.pdf which illustrates the use of the EXCEL engine. Here's what is shown on that page (I recommend reading the paper if you are going to use the EXCEL engine, though):

On the other hand, if you switched to the XLSX engine, as described here: http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/ then you do NOT need to use a $ in the sheetname.

Switching to the XLSX engine simplifies your code, if you can do it. If you are using SAS Studio or SAS University Edition and you have .XLSX files, then you could use the XLSX LIBNAME engine.

cynthia