DBLOAD Procedure in SAS and SYSTEM 2000
Using the DBLOAD Procedure
The DBLOAD procedure runs in batch and interactive line mode and enables you to create and load a SYSTEM 2000 database from a SAS data set. You can create the database definition only and execute one or more incremental loads at a later time.
PROC DBLOAD constructs SYSTEM 2000 statements to create the database definition. You
can load new logical entries into the database, or you can insert new records into
existing logical entries. The data can come from
a SAS data file, from a view created by using the SQL procedure, or from a SYSTEM 2000 database or another DBMS (using a view created by using the ACCESS procedure).
PROC DBLOAD associates each SAS variable in the input data with a SYSTEM 2000 item and assigns a default name, number, and
type to each item. By default, items are non-key at level 0. You can use the defaults or change the names, the status (key or non-key), and
the level, as necessary. When you are finished customizing the items, PROC DBLOAD
creates the SYSTEM 2000 database.
Compatibility with SAS 6
You can use SAS 6 and later SAS data files to create and load SYSTEM 2000
databases
by using PROC DBLOAD. However, beginning with SAS 7 data files, variables with names
that are longer than eight characters have their names truncated to eight characters
in the access and view descriptors created by PROC DBLOAD. The RENAME statement in
PROC DBLOAD can be used
to rename the variables in the SYSTEM 2000 database, but it does not change the variable names in the access and view descriptors. The truncated names must be used to access
the data described by the view descriptors.
Creating a SYSTEM 2000 Database
In this section, PROC DBLOAD is used to create the database Banking and load data
into it. In this new database, each logical entry contains data for one customer.
The ENTRY schema record at level 0 contains two items: the customer name and customer ID. The schema record at level 1 contains information about the customer's checking and savings
accounts. The schema record at level 2 contains information about the transactions
for each account.
After you create the database Banking, use the QUEST procedure to execute the DESCRIBE statement in SYSTEM 2000 to produce the following
output, which shows the database definition.
Banking Database Definition
SYSTEM RELEASE NUMBER 12.1
DATA BASE NAME IS BANKING
DEFINITION NUMBER 1
DATA BASE CYCLE NUMBER 18
1* CUSTNAME (CHAR X(20))
2* CUSTID (CHAR X(7))
100* RECORD_LEVEL_1 (RECORD)
101* ACCOUNT NUMBER (INTEGER NUMBER 9999 IN 100)
102* ACCOUNT TYPE (CHAR X IN 100)
200* RECORD_LEVEL_2 (RECORD IN 100)
201* TRANS TYPE (CHAR X IN 200)
202* TRANS AMOUNT (NON-KEY MONEY $9(7).99 IN 200)
203* TRANS DATE (DATE IN 200)Notice that the records are indented at three different levels to reflect the record
relationships. That is, record C200 is a descendant of record C100, which is a descendant of the level 0 record.
Loading the Input Data File
The input SAS data file that is used for the examples given here is shown in
Data File Trans.Banking. If you want to run the examples, make sure that you sort the observations before
you use PROC DBLOAD. Sorting the observations groups them by accounts for each customer,
which produces data in the sequence required for loading the three-level database
Banking.
Each observation in the input data file represents one transaction. For example, John Booker has four
transactions, two for each of his accounts. His name and account numbers are repeated
in each observation as shown in the following output.
The SAS Data File Trans.Banking
OBS CUSTNAME CUSTID ACCTNUM ACCTTYP TRANSTYP TRANSAMT TRANSDAT 1 BOOKER, JOHN 74-9838 8349 S D $40.00 05JUN89 2 LOPEZ, PAT 38-7274 9896 S D $15.67 23JUN89 3 JONES, APRIL 85-4941 4141 C W $213.78 29JUN89 4 BOOKER, JOHN 74-9838 8349 S I $34.76 30JUN89 5 MILLER, NANCY 07-6163 7890 S I $53.98 30JUN89 6 LOPEZ, PAT 38-7274 9896 S I $16.43 30JUN89 7 JONES, APRIL 85-4941 4141 C W $354.70 30JUN89 8 MILLER, NANCY 07-6163 7890 S D $1,245.87 01JUL89 9 JONES, APRIL 85-4941 4141 C D $2,298.65 01JUL89 10 MILLER, NANCY 07-6163 3876 C W $45.98 08JUL89 11 ROGERS, MIKE 96-5052 4576 C D $75.00 10JUL89 12 BOOKER, JOHN 74-9838 3673 C D $150.00 10JUL89 13 LOPEZ, PAT 38-7274 9896 S D $50.00 10JUL89 14 BOOKER, JOHN 74-9838 3673 C W $65.43 13JUL89 15 ROGERS, MIKE 96-5052 4576 C W $12.34 13JUL89 16 ROGERS, MIKE 96-5052 4576 C W $45.67 13JUL89 17 MILLER, NANCY 07-6163 3876 C D $56.79 14JUL89 18 ROGERS, MIKE 96-5052 4576 C W $12.16 15JUL89
After you sort the input data file by customer name and account type, PROC DBLOAD
loads data for each customer as a logical entry in the SYSTEM 2000 database. Redundant
data is reduced, which saves storage space. The logical entry for John
Booker would look like the following figure.
After you load the input data from Trans.Banking, run the following SYSTEM 2000
LIST
statement in PROC QUEST.
list c1, c101, c102, c201, c202;Here are the results.
Output from LIST Statement Run on the Database Banking
* CUSTNAME ACCOUNT NUMBER ACCOUNT TYPE TRANS TYPE TRANS AMOUNT *** * BOOKER, JOHN 3673 C D $150.00 * W $65.43 * 8349 S D $40.00 * I $34.76 * JONES, APRIL 4141 C W $213.78 * W $354.70 * D $2,298.65 * LOPEZ, PAT 9896 S D $15.67 * I $16.43 * D $50.00 * MILLER, NANCY 3876 C W $45.98 * D $56.79 * 7890 S I $53.98 * D $1,245.87 * ROGERS, MIKE 4576 C D $75.00 * W $12.34 * W $45.67 * W $12.16
Notice the values shown for John Booker. His name appears only one time, but he has
two account numbers and four transactions. Because the examples that use PROC DBLOAD
rank the data values into levels, you have a clear, logical view of the data.
Subsetting Input Data
To subset your input data, use the WHERE statement in SAS. Creating a subset of
the
input data is useful if you need to transfer only a portion of your SAS data to a
SYSTEM 2000 database. For example, you might want to include only observations in
which the value in a
variable is greater than a specified number.
The following program subsets the input data to include only those observations in
which the SAS variable ACCTNUM has a value greater than 4141. None of the items are
renamed or indexed,
and they are all at level 0.
Notice that you use the SAS variable name in the WHERE statement, not the SYSTEM 2000
item name. For information about
the syntax of the WHERE statement, see SAS Statements: Reference.
proc dbload dbms=s2k data=trans.banking;
s2kpw=mine;
dbn=banking;
s2kmode=m;
where acctnum > 4141;
load;
run;Subsetting Input Data
To subset your input data, use the WHERE statement in SAS. Creating a subset of
the
input data is useful if you need to transfer only a portion of your SAS data to a
SYSTEM 2000 database. For example, you might want to include only observations in
which the value in a
variable is greater than a specified number.
The following program subsets the input data to include only those observations in
which the SAS variable ACCTNUM has a value greater than 4141. None of the items are
renamed or indexed,
and they are all at level 0.
Notice that you use the SAS variable name in the WHERE statement, not the SYSTEM 2000
item name. For information about
the syntax of the WHERE statement, see SAS Statements: Reference.
proc dbload dbms=s2k data=trans.banking;
s2kpw=mine;
dbn=banking;
s2kmode=m;
where acctnum > 4141;
load;
run;Loading a SYSTEM 2000 Database
To create and load a SYSTEM 2000 database, use PROC DBLOAD with options and statements
that describe the SYSTEM 2000 database
that you want to create and the data that you want to load into the database. To load
the database Banking, use the following program. The function of each statement is explained in the section that follows.
JCL statements;
proc dbload dbms=s2k data=trans.banking;
s2kpw=mine;
dbn=banking;
accdesc=mylib.bank;
viewdesc=vlib.myview;
s2kmode=m;
rename acctnum='account number' 4= 'account type'
5='trans type' 6='trans amount'
7='trans date';
index 1=y 2=y 3=y 4=y transtyp=y 7=y;
level 3=1 4=1 5=2 6=2 transdat=2;
list all;
load;
run;JCL statements;
submit your statements
for execution under SAS.
proc dbload dbms=s2k data=trans.banking;invokes the DBLOAD procedure. The DBMS= option specifies the DBMS that you want to
load data into. The DATA= option specifies
the SAS data file that contains the data.
s2kpw=mine; issues the password that becomes the master password for the new database.
dbn=banking; identifies the database that you want to create. In this example, you create a SYSTEM
2000 database named
Banking.
accdesc=mylib.bank;specifies the access descriptor libref and member name. The access descriptor is created automatically by PROC DBLOAD. In this example,
the specified name is MyLib.Bank. The default access descriptor name is Work.Banking.ACCESS,
where Banking is the name of the database to be created. The access descriptor member
name must not already exist when you
are creating a new database.
viewdesc=vlib.myview;specifies the view descriptor libref and member name. The view descriptor is created automatically by PROC DBLOAD.
In this example, the specified
name is Vlib.MyView. The default view descriptor name is Work.Banking.VIEW, where
Banking is the name of the database to be created. The view descriptor member name
must not already exist when you are
creating a new database.
s2kmode=m;creates the new database in a Multi-User environment. The default, S, specifies a single-user environment. For a Multi-User session, the new database files can be allocated when the session is initialized or dynamically
allocated during execution by using the ALLOC command in SYSTEM 2000, Release 12.0 and later. For a single-user job, you must allocate
the database files in the JCL for the job, or dynamically allocate the database files
using the S2KDBCnt file.
rename acctnum='account number' 4= 'account type'
5='trans type' 6='trans amount' 7='trans date';changes the names of the last five items. In a RENAME statement, always use a SAS
variable on the left side of the equal sign (=). You can use either the SAS variable
name or its positional equivalent as shown in the LIST statement output (Output 6.4).
You can rename as many items as you want in one RENAME statement.
index 1=y 2=y 3=y 4=y transtyp=y 7=y; defines items as key (indexed). In this INDEX statement, all items except TRANS AMOUNT
are key items.
TRANS AMOUNT is not listed, so it defaults to non-key.
In an INDEX statement, always use a SAS variable on the left side of the equal sign
(=). You can use either the SAS variable name or its positional equivalent as shown
in the LIST statement output below. You
can index as many items as you want in one INDEX statement.
level 3=1 4=1 5=2 6=2 transdat=2;changes the level number of an item. In this LEVEL statement, ACCOUNT NUMBER and ACCOUNT
TYPE become
items in a level 1 record; TRANS TYPE, TRANS AMOUNT, and TRANS DATE become items in a level 2 record. PROC DBLOAD automatically defines the schema record
names RECORD_LEVEL_1 and RECORD_LEVEL_2, respectively, and assigns appropriate component numbers.
In a LEVEL statement, always use a SAS variable on the left side of the equal sign
(=). You can use either the SAS variable name or its positional equivalent as shown
in the LIST statement output below. You
can change the level number for as many items as you want in one LEVEL statement.
list all;lists the items, levels, and index settings.
load; run;executes PROC DBLOAD and creates and loads the database.
LIST Statement Output
Command ===>
PROC DBLOAD for SYSTEM 2000 - OPTIONS FOLLOW:
Input data set= TRANS BANKING DATA
View descriptor= VLIB MYVIEW VIEW
Access descriptor= MYLIB BANK ACCESS
Database name= BANKING
S2KMODE= M
Label option= N
Create option= N
S2KLOAD= N
------------SAS NAME---LEVEL---INDEX---COMPONENT NAME----
1 CUSTNAME YES CUSTNAME
2 CUSTID YES CUSTID
3 ACCTNUM 1 YES ACCOUNT NUMBER
4 ACCTTYP 1 YES ACCOUNT TYPE
5 TRANSTYP 2 YES TRANS TYPE
6 TRANSAMT 2 TRANS AMOUNT
7 TRANSDAT 2 YES TRANS DATE To load additional logical entries into an existing SYSTEM 2000 database, invoke
PROC DBLOAD and specify the input data file and the appropriate view descriptor. The
view descriptor contains the database name, the component names, levels, and so on.
It also contains the password for the database and the
access mode (single-user or Multi-User). You can use a WHERE clause in SAS to limit the input. However, a SYSTEM 2000 where-clause in the view descriptor does not affect an incremental load.
To perform an incremental load with PROC DBLOAD, use the following program. In this
example, the data file is Trans.IncLoad. The function of each statement is explained
immediately following the program.
JCL statements;
proc dbload dbms=s2k data=trans.incload;
viewdesc=vlib.myview;
load;
run;JCL statements;
submit your statements
for execution under SAS.
proc dbload dbms=s2k data=trans.incload;invokes the DBLOAD procedure. The DBMS= option specifies the DBMS that you want to
load data into. The DATA= option specifies
the SAS data file that contains the data.
viewdesc=vlib.myview;specifies the existing view descriptor libref and member name.
load; run;executes PROC DBLOAD and loads the database.
Adding New Logical Entries versus Updating Existing Logical Entries
Incremental loads can either insert new logical entries or append new records
to
existing logical entries. Both types of incremental loading are performed the same way, as shown in the previous example. How does the SYSTEM
2000 engine know which action to perform?
If you issue an S2KLOAD statement, the input observations are treated as new logical
entries. Several observations can be collected to form each logical entry, but they
are all new entries. The observations must be sorted in order to achieve
the correct result.
If you do not issue the S2KLOAD statement, your results are based on the order of
the observations and whether the view descriptor contains a BY key. A BY key identifies
the placement of inserted data records in an incremental load.
See Using a BY Key. When using a BY key, it is best (less ambiguous) if your view descriptor and the
BY key begin at level 0, even if you are loading records only at a lower level.
Copyright © SAS Institute Inc. All rights reserved.