BookmarkSubscribeRSS Feed
marianhabesland
Calcite | Level 5

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

marianhabesland
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 899 views
  • 0 likes
  • 3 in conversation