PROC DB2EXT :: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Overview

The DB2EXT procedure creates SAS data sets from DB2 under z/OS data. PROC DB2EXT runs interactively, noninteractively, and in batch mode. The generated data sets are not password protected. However, you can edit the saved code to add password protection.

PROC DB2EXT ensures that all SAS names that are generated from DB2 column values are unique. A numeric value is appended to the end of a duplicate name. If necessary, the procedure truncates the name when appending the numeric value.

Syntax

Here is the syntax for the DB2EXT procedure.

PROC DB2EXT <options>;

FMT column-number-1='SAS-format-name-1'
<... column-number-n='SAS-format-name-n'>;

RENAME column-number–1='SAS-name–1'
<... column-number–n='SAS-name–n'>;

SELECT DB2–SQL-statement;

EXIT;

PROC DB2EXT Statement Options

IN=SAS-data-set

specifies a mapping data set that contains information such as DB2 names, SAS variable names, and formats for input to PROC DB2EXT. This option is available for use only with previously created mapping data sets. You cannot create new mapping data sets with DB2EXT.

OUT=SAS-data-set | libref.SAS-data-set

specifies the name of the SAS data set that is created. If you omit OUT=, the data set is named "work.DATAn", where n is a number that is sequentially updated. The data set is not saved when your SAS session ends. If a file with the name that you specify in the OUT= option already exists, it is overwritten. However, you receive a warning that this is going to happen.

SSID=subsystem-name

specifies the name of the DB2 subsystem that you want to access. If you omit SSID=, the subsystem name defaults to DB2. The subsystem name defaults to the subsystem that is defined in the DB2SSID= option. It defaults to DB2 only if neither the SSID= option nor the DB2SSID= option are specified.

FMT Statement

FMT column-number-1='SAS-format-name-1'
<... column-number-n='SAS-format-name-n'>;

The FMT statement assigns a SAS output format to the DB2 column that is specified by column-number. The column-number is determined by the order in which you list the columns in your SELECT statement. If you use SELECT *, the column-number is determined by the order of the columns in the database. You must enclose the format name in single quotation marks. You can specify multiple column formats in a single FMT statement.

RENAME Statement

RENAME column-number-1='SAS-name-1'
<... column-number-n='SAS-name-n'>;

The RENAME statement assigns the SAS-name to the DB2 column that is specified by column-number. The column-number is determined by the order in which you list the columns in your SELECT statement. If you use SELECT *, the column-number is determined by the order of the columns in the database.

You can rename multiple columns in a single RENAME statement.

SELECT Statement

SELECT DB2-SQL-statement;

The DB2-SQL-statement defines the DB2 data that you want to include in the SAS data set. You can specify table names, column names, and data subsets in your SELECT statement. For example, this statement selects all columns from the Employee table and includes only employees whose salary is greater than $40,000.

select * from employee where salary > 40000;

EXIT Statement

EXIT;

The EXIT statement terminates the procedure without further processing.

Examples

This code creates a SAS data set named MyLib.NoFmt that includes three columns from the DB2 table EmplInfo. The RENAME statement changes the name of the third column that is listed in the SELECT statement (from firstname in the DB2 table to fname in the SAS data set.

/* specify the SAS library where the SAS data set is to be saved */

libname mylib 'userid.xxx';

proc db2ext ssid=db25 out=mylib.nofmt;
   select employee, lastname, firstname from sasdemo.emplinfo;
   rename 3=fname;
run;

This code uses a mapping file to specify which data to include in the SAS data set and how to format that data.

/* specify the SAS library where the SAS data set is to be saved */
libname mylib 'userid.xxx';

/* specify the SAS library that contains the mapping data set */
libname inlib 'userid.maps';

proc db2ext in=inlib.mapping out=mylib.mapout ssid=db25;
run;

The DB2EXT Procedure