Write and run SAS programs in your web browser

SET statment for Excel file

Reply
Occasional Contributor
Posts: 8

SET statment for Excel file

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$?

Super User
Super User
Posts: 7,565

Re: SET statment for Excel file

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.  

Occasional Contributor
Posts: 8

Re: SET statment for Excel file

[ Edited ]

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.

Super User
Super User
Posts: 7,565

Re: SET statment for Excel file

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.

SAS Super FREQ
Posts: 8,782

Re: SET statment for Excel file

[ Edited ]

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

use_excel_engine.png

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

Ask a Question
Discussion stats
  • 4 replies
  • 124 views
  • 0 likes
  • 3 in conversation