SAS Educational Consultancy SYS-ED SYSED Computer Education Techniques

SAS Educational Consultancy SYS-ED SYSED Computer Education Techniques

SAS Application Development

SAS Training Sitemap

Submit SAS Questions Knowledge Transfer SAS Platform Schedule
Definition of Service Delivery Medium Web-based Training Services
SAS Software Platform

Data Analytics and Business Intelligence

Operational Challenge Database Access from SAS SAS/ACCESS Translation to SQL
Developing SAS Applications with Business and Data Analysis Apache Hadoop Project and SAS Best Practices and Guidelines
Copyright Acknowledgement

Operational Challenge

Business intelligence systems access data warehouses, data marts, and databases and provide the capability to examine data from historical, current, and predicative viewpoints through a variety of analysis and reports. Properly implemented, SAS can provide greater precision and analysis to decision making.

The challenge is to utilize SAS for accessing data from IBM mainframe systems software and enterprise databases and efficiency analyze and produce real-time reports on a variety of operating systems and devices.

Database Access from SAS

There are two ways to connect an IBM Db2 database from the SAS software platform. The connection can be made using the libname engine or directly to the database using the connect statement in the SQL procedure.

When connecting to the database using the libname engine, SAS automatically translates SAS data access application code to SQL. Translation to SQL means that SAS/ACCESS processes the SAS application code and then generates the appropriate SQL to access the database.

When connecting directly to the database using the connect statement, the explicit SQL pass-through can be used. Explicit SQL pass-through is a mechanism which allows SQL to pass unaltered directly to the database server. Explicit SQL pass-through is useful for adding database-only operations to a SAS application and is accessible using the SQL procedure - proc sql.

Most SAS procedures and data steps use the SAS/ACCESS SQL translation engine.

Example: SQL Translation for Print Procedure

When the same proc print procedure is executed using SAS/ACCESS against a Db2 database, the request is translated into SQL for processing by Db2.

/* SQL generated or proc print */
select “state”,”serialno”
from hrecs

order by state;

This is the SQL generated for the proc print statement. Since the ORDER BY option is used in the SELECT statement, the proc sort would not be required.

SAS/ACCESS Translation to SQL

SAS/ACCESS translation to SQL is used for SAS data access functionality and joining data from multiple data sources. It can be included in a procedure or data step such as proc freq and proc summary. There is application portability with different relational databases.

The explicit SQL pass-through is used when Db2 database processing steps are executed from a SAS application or there is a requirement for a Db2-specific application.

Client Requirements

Developing SAS Applications for Business and Data Analysis

The SAS examples and exercises demonstrate and explain the Add-in for Microsoft Office, utilization of AJAX, and debugging.

Add-In for Microsoft Office
The MS Office ribbon bar technology groups similar items for a consistent user experience with charting and graphing options. For SAS Enterprise Guide, there is a simplified user interface with context-sensitive menus and toolbars. Report linking can be performed to a report section within the same report.

AJAX in SAS Web Report Studio
There are additional drag-and-drop capabilities and resizing of onscreen objects. Partial screen refresh and context-sensitive menus have been incorporated.

Apache Hadoop Project and SAS

The SAS platform complements Apache Hadoop. SAS support for Hadoop is part of a Big Data strategy which includes information management and high performance analytics: grid, in-database and in-memory computing. There is a scarcity of Hadoop tools for developing and managing Hadoop deployments. SAS data and analytics management continues to be developed for integration with Hadoop.

The benefits of the SAS platform and Big Data information management promoted by SAS Institute include:
  • SAS visual tools for Hadoop data access and usage.
  • Application of data quality capabilities.
  • Data Integration Studio transformations for building job flows.
  • SAS UDFs: user-defined functions for deployment within HDFS and access through the Apache ecosytem.

Best Practices and Guidelines

Accessing VSAM Datasets

A common issue with SAS is accessing VSAM datasets by reading records with sequential and keyed direct access, adding, updating and erasing records. The INFILE statement options and related automatic variables are required to process VSAM datasets and utilize the return code of each type of I/O operation. With VSAM default access is sequential in SAS, this code is the same as for a QSAM flat file.



Infile KSDSFILE End=Endfile ;

Input  @1 ssn             $9.


lstname        $10.


frstname       $10.


homeaddress    $25.

Any INFILE statement OPTION can be used such as END= option.

SAS also can be used to access VSAM dataset directly. It is important to verify the return code to which the feedback is assigned. It also is a requirement to initialize the variable for the return code and the automatic variable _ERROR_ to zero. The return code is assigned after the INPUT statement is executed.

Infile KSDSFILE Key=id FDBK=retcode ;

 Input@ ;


Infile KSDSFILE Key=id Fdbk=retcode ;

Input @ ;


Set KEYS ;

If retcode NE 0 Then

Do ;

Put 'Record not found ' id= retcode= ;

          retcode = 0 ;

          _Error_ = 0 ; 

          End ;

          Else Output ;

Hybrid Operating Environments

Guidelines are provided for using SAS software in hybrid operating environments to generate web-based query and reporting.

  • How to code and utilize SAS procedures for data manipulation, information storage, information retrieval, statistical analysis, and report writing.
  • How to utilize the Java client, XML Mapper, for importing and exporting XML documents to the SAS platform.
  • How to use ODS: Output Delivery System for reporting, report formatting, and report delivery in order to:

1. Capture data output from any Base procedure into a SAS dataset for additional processing or reporting.

2. Create and separate datasets for a subgroup.

Copyright Acknowledgement

SAS software is a registered trademark of SAS Institute.

Db2 is a registered trademark of IBM Corporation.