This example illustrates
how a program can reestablish currency on a record to complete set
navigation after accessing a record that is not contained in the current
set occurrence.
In this example, a transaction
SAS data set, Work.Employee, supplies
a CALC key value for the OBTAIN of an EMPLOYEE record. COVERAGE records
are then obtained within the current EMP-COVERAGE set occurrence.
PLANCODE values from employee COVERAGE records provide links to INSURANCE-PLAN
records through a CALC key. Once current on INSURANCE-PLAN, the program
gathers data and uses a stored database key to return to the current
COVERAGE record. At that point, the next COVERAGE record in the current
set occurrence of EMP-COVERAGE can be obtained. The output data set
consists of observations that contain employee, coverage, and related
insurance plan data. The numbers in the program correspond to the
numbered comments following the program.
1 *options $idmdbug;
2 data work.employee;
input empnum $4.;
datalines;
0007
0471
0000
0301
0004
;
data work.empplan;
drop covdbkey empnum;
3 infile empss01 idms func=func record=recname
ikey=ckey ikeylen=keyl errstat=stat
sequence=seq set=inset area=subarea
dbkey=dkey;
/* BIND records to be accessed */
4 if _n_ = 1 then do;
func = 'BIND';
recname = 'EMPLOYEE';
input;
if stat ne '0000' then go to staterr;
recname = 'INSURANCE-PLAN';
input;
if stat ne '0000' then go to staterr;
recname = 'COVERAGE ;
input;
if stat ne '0000' then go to staterr;
end;
/* OBTAIN EMPLOYEE record using CALC key */
/* value */
5 set work.employee;
func = 'OBTAIN';
seq = ' ';
inset = ' ';
ckey = empnum;
keyl = 4;
recname = 'EMPLOYEE';
input @;
if stat not in ('0000', '0326') then go to
staterr;
if stat = '0000' then do;
input @1 employee_id 4.0
@5 firstname $char10.
@15 lastname $char15.;
/* OBTAIN COVERAGE records for EMPLOYEE */
6 seq = 'FIRST';
do while (stat = '0000');
func = 'OBTAIN';
keyl = 0;
ckey = ' ';
dkey = ' ';
recname = 'COVERAGE';
inset = 'EMP-COVERAGE';
input @;
if stat not in ('0000', '0307') then go
to staterr;
if stat = '0000' then do;
input @13 type $1.
@14 plancode $3.;
/* ACCEPT CURRENT database key */
7 func = 'ACCEPT';
seq = 'CURRENT';
dkey = ' ';
input;
if stat ne '0000' then go to staterr;
covdbkey = dkey;
/* FIND INSURANCE-PLAN using CALC */
8 func = 'FIND';
ckey = plancode;
keyl = 3;
seq = ' ';
recname = 'INSURANCE-PLAN';
inset = ' ';
dkey = ' ';
input;
if stat ne '0000' then go to
staterr;
/* OBTAIN CURRENT INSURANCE-PLAN */
/* record */
9 func = 'OBTAIN';
seq = 'CURRENT';
ckey = ' ';
keyl = 0;
recname = ' ';
subarea = ' ';
input @;
if stat ne '0000' then go to staterr;
input @4 company_name $45.
@105 group_number 6.0
@111 plndeduc PD5.2
@116 maxlfcst PD5.2
@121 famlycst PD5.2
@126 depcost PD5.2;
output;
/* FIND COVERAGE using stored */
/* database key */
10 func = 'FIND';
seq = ' ';
recname = 'COVERAGE';
dkey = covdbkey;
input;
if stat ne '0000' then go to staterr;
seq = 'NEXT';
end;
end;
end;
11 else do;
put 'WARNING: No EMPLOYEE record for CALC=
'ckey;
put 'WARNING: Execution continues with next
EMPLOYEE.';
_error_ = 0;
end;
12 _error_ = 0;
return;
13 staterr:
put @1 'ERROR: ' @10 func @17 'RETURNED
STATUS =' @37 stat;
put @1 'ERROR: INFILE parameter values are: ';
put @1 'ERROR: ' recname= ckey= keyl= seq=
inset= subarea= dkey=;
put @1 'ERROR: DATA step execution
terminating.';
_error_ = 0;
stop;
run;
proc print data=work.empplan;
title 'Employee Coverage and Plan Record
Information';
run;
| 1 |
See
Statements Common to All SAS/ACCESS DATA Step Examples for a description of the OPTIONS statement.
|
| 2 |
This DATA step execution
creates the transaction data set Work.Employee. The 4-byte character
variable EMPNUM contains CALC key values that are used to access EMPLOYEE
records directly by employee ID.
|
| 3 |
See
Statements Common to All SAS/ACCESS DATA Step Examples for a description of the INFILE statement.
|
| 4 |
See
Statements Common to All SAS/ACCESS DATA Step Examples for a description of the BIND RECORD statement.
|
| 5 |
The
current EMPNUM value from Work.Employee is used as a CALC
key to obtain an EMPLOYEE record from the database. KEYL specifies
the length of the CALC key. The INPUT @; statement
submits the call and places a hold on the input buffer so that the
status code can be checked. For any unexpected status code, execution
branches to the STATERR label. If the status code is 0000, the INPUT
statement maps data from the input buffer to the PDV and then releases
the buffer.
|
| 6 |
The
DO WHILE loop obtains COVERAGE records for the current employee in
the EMP-COVERAGE set. When all COVERAGE records in the set have been
obtained, the status code is set to 0307, and the loop terminates.
At that point, the DATA step obtains the next EMPLOYEE as specified
by the CALC value read from Work.Employee.
The INPUT
@; statement submits the OBTAIN FIRST/NEXT call and places
a hold on the input buffer while the status code is checked. For any
unexpected status codes, execution branches to the STATERR label.
For a successful OBTAIN call, the INPUT statement maps coverage information
from the input buffer to the specified variables in the PDV and releases
the input buffer. The PLANCODE variable now contains a CALC key value
that can be used to directly access related INSURANCE-PLAN record
information.
|
| 7 |
The
next DML call generated is an ACCEPT CURRENT, which takes the current
database key of the COVERAGE record and stores it in the variable
defined by the DBKEY= INFILE parameter, DKEY. The null INPUT statement
submits the ACCEPT call but does not place a hold on the input buffer
because ACCEPT returns no data. For any status code other than 0000,
execution branches to the STATERR label. For a successful ACCEPT call,
the value returned to DKEY is moved into variable COVDBKEY to be used
in a later call. By storing the database key of this record for later
use, the program can regain currency on the record.
|
| 8 |
Now
that the database key of the COVERAGE record is stored, a FIND call
is generated to locate and establish currency on the related INSURANCE-PLAN
record. The FIND call uses the CALC value stored in PLANCODE. To issue
this call, the DKEY field is set to blank. The null INPUT statement
submits the call to CA-IDMS but no hold is placed on the input buffer
because FIND does not return data. For any status code other than
0000, execution branches to the STATERR label.
|
| 9 |
After
the INSURANCE-PLAN record has been successfully located, an OBTAIN
CURRENT call is generated to request that the record be retrieved.
The INPUT @; statement submits the generated
call and places a hold on the input buffer so that the returned status
code can be checked. For any status code other than 0000, execution
branches to the STATERR label. For a successful OBTAIN, the INPUT
statement maps INSURANCE-PLAN data from the input buffer to the specified
variables in the PDV. At this point, an observation is written to
output data set
Work.EmpPlan that
contains related EMPLOYEE, COVERAGE, and INSURANCE-PLAN information.
|
| 10 |
Currency
must be re-established on the COVERAGE record so that the DO WHILE
loop can obtain the NEXT COVERAGE record in the current set occurrence
of EMP-COVERAGE. A FIND call is generated using the stored database
key in COVDBKEY. This call locates the correct COVERAGE record occurrence.
The null INPUT statement submits the generated call, but no hold is
placed on the input buffer since FIND establishes a position in the
database rather than returning data. For any status code other than
0000, execution branches to the STATERR label. If the FIND is successful,
currency has been re-established, and SEQ is assigned a value of NEXT
to generate OBTAIN NEXT COVERAGE.
|
| 11 |
This
group of statements enables execution to continue when no EMPLOYEE
record exists for the CALC value specified in the transaction data
set. In this case, an informative WARNING message is written to the
SAS log and _ERROR_ is reset to 0, which prevents the contents of
the PDV from being written to the SAS log.
|
| 12 |
At
this point, the STAT variable must have a value of 0307, which indicates
that all COVERAGE records for the specified EMPLOYEE have been accessed.
Since this code is nonzero, _ERROR_ is reset to 0, which prevents
the contents of the PDV from being written to the SAS log.
|
| 13 |
See
Statements Common to All SAS/ACCESS DATA Step Examples for a description of the STATERR statements.
|
The following output
shows a portion of the output from this program.
Re-establishing Currency on a Record
Employee Coverage and Plan Record Information
employee_
Obs id firstname lastname type plancode
1 7 MONTE BANK F 004
2 471 THEMIS PAPAZEUS F 003
3 471 THEMIS PAPAZEUS F 002
4 471 THEMIS PAPAZEUS M 001
5 301 BURT LANCHESTER D 004
6 301 BURT LANCHESTER F 003
7 301 BURT LANCHESTER F 002
8 301 BURT LANCHESTER M 001
9 4 HERBERT CRANE F 004
10 4 HERBERT CRANE F 003
11 4 HERBERT CRANE M 001
group_
Obs company_name number
1 TEETH R US 545598
2 HOLISTIC GROUP HEALTH ASSOCIATION 329471
3 HOMOSTASIS HEALTH MAINTENANCE PROGRAM 952867
4 PROVIDENTIAL LIFE INSURANCE 347815
5 TEETH R US 545598
6 HOLISTIC GROUP HEALTH ASSOCIATION 329471
7 HOMOSTASIS HEALTH MAINTENANCE PROGRAM 952867
8 PROVIDENTIAL LIFE INSURANCE 347815
9 TEETH R US 545598
10 HOLISTIC GROUP HEALTH ASSOCIATION 329471
11 PROVIDENTIAL LIFE INSURANCE 347815
Obs plndeduc maxlfcst famlycst depcost
1 50 0 5000 1000
2 200 0 200 200
3 0 0 900000 100000
4 0 100000 0 0
5 50 0 5000 1000
6 200 0 200 200
7 0 0 900000 100000
8 0 100000 0 0
9 50 0 5000 1000
10 200 0 200 200
11 0 100000 0 0