Processing Database and Spreadsheet Data with SAS/ACCESS Software

Duration: 1.0 day CEU: 0.6

This course teaches you how to read Oracle, DB2, or Microsoft Access or Excel tables into your SAS programs using SAS/ACCESS software.

Learn how to

read data in relational databases and Excel workbooks using the LIBNAME statement and using the SQL Pass-Through Facility
create efficiency techniques for optimizing data access performance
join data using the DATA step and the SQL procedure
import Microsoft Access and Excel data using the Import Wizard, and export data using the Export Wizard.

Who should attend

SAS programmers

Prerequisites

Before attending this course, you should be comfortable programming in SAS and Structured Query Language (SQL). You can gain the SQL knowledge from the SQL Processing with SAS course. You can gain knowledge of SAS from the SAS Programming I: Essentials course. A working knowledge of your database is helpful.

Course Contents

Accessing Data in a Relational Database or Microsoft Excel Workbook

understanding databases
establishing the requirements to connect to a database
establishing the requirements to connect to an Excel workbook

Using a SAS/ACCESS LIBNAME Statement

connecting to a database table using SAS/ACCESS LIBNAME engines
connecting to an Excel workbook
explaining and applying Open Database Connectivity (ODBC)
using an embedded LIBNAME statement

Using the SQL Pass-Through Facility

passing queries to your database management system (DBMS)
passing non-queries to your DBMS
comparing the SQL Pass-Through Facility and the SAS/ACCESS LIBNAME engines

Joining Tables

combining tables using a DATA step merge
joining tables using the SQL Pass-Through Facility and the SQL procedure
joining tables from different databases

Importing and Exporting PC File Data

using the Import Wizard to read a Microsoft Access table and an Excel worksheet into a SAS data set
Using the IMPORT procedure to read a Microsoft Access table and an Excel worksheet into a SAS data set
Using the Export Wizard to write a SAS data set and an Oracle table to an Excel workbook
Using the EXPORT procedure to export a SAS data set to an Excel workbook

Creating and Updating SAS/ACCESS Access and View Descriptors (Self-Study)

creating and updating an access descriptor
creating and updating a view descriptor
converting access and view descriptors to SQL procedure views with the CV2VIEW procedure

Software Addressed

This course addresses the following software product(s): SAS/ACCESS. This course is appropriate for students who are using SAS Version 8 or SAS 9 software.

Course Materials

Students receive a hardcopy of the course notes and, in some courses, can choose to take home a copy of the course data.

U.S. Schedule

04MAR2009 San Francisco, CA

08MAY2009 Rockville, MD

Check for additional and updated schedule information online at support.sas.com/courses/accs.html.

Registration

To register for this course in the US, call 800-333-7660 or visit support.sas.com/training. This course is also available for on-site training, or you can create a custom course by combining material from several courses. For more details, contact SAS Education in Cary, NC at 919-531-7321 or send e-mail to training@sas.com.