Using a library to read Excel files - SAS Tutorial

- [Narrator] In addition to SAS data, you can also use libraries to access other types of data. For example, you can use the library with the XLSX engine to read data directly from Excel workbooks. I should point out that this engine requires a license for SAS/ACCESS to PC files. Remember that when SAS reads or writes data in a program, it must know where the data is located, and what format it's in. This time the LIBNAME statement specifies the XLSX engine, and the path includes the complete Excel workbook file name and extension. You can think of the Excel workbook as a collection of tables. Each individual worksheet is one table in that collection. There are two extra statements that you often use when you read Excel data. The first is the Options statement. A global statement for specifying system options. Excel doesn't have any rules for column headings, so they can be longer than 32 characters, and include spaces or other special symbols. When SAS reads the Excel data, we can force column names to adhere to strict SAS naming conventions by using the VALIDVARNAME=V7 system option. Technically, this enforces the column naming rules established in SAS seven. With this option set, SAS replaces any spaces or special symbols in column names with underscores and truncates names greater than 32 characters. When you define a connection to a data source, such as Excel or other data bases, it's a good practice to clear or delete the libref at the end of your program. While your library is active, it could create a lock that prevents others from accessing the file, or it could maintain an active connection to the data source that is unnecessary. To clear the library reference, you use the LIBNAME statement again, name the libref, and use the keyword clear. In this example, we use the options statement, to enforce SAS naming conventions for the columns. Then we create the xlclass library, with the XLSX engine, to read data from the class Excel workbook, located in S workshop data. The PROC Contents step is reading the class birthdate worksheet in the class workbook. At the end, we clear the xlclass libref. As you can see, it's easy to establish a library to an Excel workbook, and read or write data to Excel directly, without having to do an extra step to import or export the data.