 |
|
| Browsing and Updating IMS Data |
Browsing and Updating IMS Data with the SQL Procedure
| Using the SQL Procedure |
The SQL procedure enables you to retrieve and update data from IMS databases. You can retrieve and browse IMS data by specifying a view descriptor in the SQL procedure's SELECT statement.
To update the data, you can specify view descriptors in the SQL procedure's INSERT, DELETE, and UPDATE statements. The specified view descriptor can access data from only one IMS database path. You must use a PCB that provides you with the appropriate level of access (insert, replace, delete, or all) for the segments that you want to update before you can edit the IMS data.
The following list summarizes these SQL procedure statements:
| SELECT |
retrieves, manipulates, and displays data from IMS databases. A SELECT statement is usually referred to as a query because it queries the database for information. |
| DELETE | |
| INSERT | |
| UPDATE |
If you want to use the SQL procedure to join or access more than one IMS database, you must use a PSB in your view descriptors that includes a PCB for each database to be accessed. Each view descriptor to be joined must use the same PSB. If you join two view descriptors that reference different paths in the same database, each view descriptor must reference in the PSB (that refers to the same database) a different PCB by using the PCB Index field. That is, to access the same database using different view descriptors in any SAS procedure, you must include multiple PCBs for that database.
When using PROC SQL, notice that the data is displayed in the SAS Output window in the SAS windowing environment and written to the SASLIST ddname in batch mode, interactive line mode, and noninteractive mode. This procedure displays output data automatically without the PRINT procedure and executes without a RUN statement when a SQL procedure statement is submitted.
| Retrieving and Updating Data with the SQL Procedure |
Note: The
following PROC SQL examples assume the ACCTDBD database has not been updated
by the earlier SAS/FSP examples. ![[cautionend]](../../../../common/61925/HTML/default/images/cautend.gif)
You can use the SELECT statement to browse IMS data that is described by a view descriptor. The query in the following example retrieves all the observations in the IMS ACCTDBD database that are described by the VLIB.CUSTINFO view descriptor.
options linesize=132; proc sql; title2 'IMS Data Retrieved by a PROC SQL query'; select * /* An asterisk means select all variables */ from vlib.custinfo;
The OPTIONS statement is used to reset the default output width to 132 columns. The following output displays the query's output. Note that PROC SQL displays labels, which are the IMS item names. In Version 7 and later, the item names are also the SAS variable names, as shown here.
Results of Retrieving IMS Data with a PROC SQL Query
The SAS System
IMS Data Retrieved by a PROC SQL query
SOC_SEC_
NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2
CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE
----------------------------------------------------------------------------------------------------------------------
667-73-8275 WALLS, HOOPER J. 4525 CLARENDON RD
RAPIDAN VA USA 22215-5600 803-657-3098 803-645-4418
434-62-1234 SUMMERS, MARY T. 4322 LEON ST.
GORDONSVILLE VA USA 26001-0670 803-657-1687
436-42-6394 BOOKER, APRIL M. 9712 WALLINGFORD PL.
GORDONSVILLE VA USA 26001-0670 803-657-1346
434-62-1224 SMITH, JAMES MARTIN 133 TOWNSEND ST.
GORDONSVILLE VA USA 26001-0670 803-657-3437
178-42-6534 PATTILLO, RODRIGUES 9712 COOK RD.
ORANGE VA USA 26042-1650 803-657-1346 803-657-1345
156-45-5672 O'CONNOR, JOSEPH 235 MAIN ST.
ORANGE VA USA 26042-1650 803-657-5656 803-623-4257
657-34-3245 BARNHARDT, PAMELA S. RT 2 BOX 324
CHARLOTTESVILLE VA USA 25804-0997 803-345-4346 803-355-2543
667-82-8275 COHEN, ABRAHAM 2345 DUKE ST.
CHARLOTTESVILLE VA USA 25804-0997 803-657-7435 803-645-4234
456-45-3462 LITTLE, NANCY M. 4543 ELGIN AVE.
RICHMOND VA USA 26502-3317 803-657-3566
234-74-4612 WIKOWSKI, JONATHAN S. 4356 CAMPUS DRIVE
RICHMOND VA USA 26502-5317 803-467-4587 803-654-7238 You can specify a WHERE clause as part of the SQL procedure's SELECT
statement to retrieve a subset of the database data. The following example
displays a list of customers who have accounts with the Richmond branch of
the bank:
title2 'IMS Data Retrieved by a WHERE Statement'; select * from vlib.custinfo where city='RICHMOND';
Notice that the PROC SQL statement is not repeated in this query. With the SQL procedure, you do not need to repeat the PROC SQL statement unless you submit another SAS procedure, a DATA step, or a QUIT statement between PROC SQL statements. The following output displays the customers of the Richmond branch who are described by VLIB.CUSTINFO.
Results of Retrieving IMS Data Using a WHERE Statement
The SAS System
IMS Data Retrieved Using a WHERE Statement
SOC_SEC_
NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2
CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE
----------------------------------------------------------------------------------------------------------------------
456-45-3462 LITTLE, NANCY M. 4543 ELGIN AVE.
RICHMOND VA USA 26502-3317 803-657-3566
234-74-4612 WIKOWSKI, JONATHAN S. 4356 CAMPUS DRIVE
RICHMOND VA USA 26502-5317 803-467-4587 803-654-7238
| Updating Data with the SQL Procedure |
You can use the UPDATE statement to update the data in an IMS database as was done earlier in this section using the FSEDIT procedure. Remember that when you reference a view descriptor in a SQL procedure statement, you are updating the IMS data that is described by the view descriptor, not the view descriptor itself. Use the WHERE clause to position the IMS engine on the database segment to be updated by specifying values for the key fields of parent segments.
The following UPDATE statements update the values that are contained in the last observation of VLIB.CUSTINFO:
update vlib.custinfo set zip_code = '27702-3317' where soc_sec_number = '234-74-4612'; update vlib.custinfo set addr_line_2 = '151 Knox St.' where soc_sec_number = '234-74-4612'; title2 'Updated Data in IMS ACCTDBD Database'; select * from vlib.custinfo where soc_sec_number = '234-74-4612';
The SELECT statement in this example retrieves and displays the updated data in the following output. (Because you are referencing a view descriptor, you use the SAS names for items in the UPDATE statement; the SQL procedure displays the variable labels as stored in the view.)
Results of Updating IMS Data with the UPDATE Statement
The SAS System
Updated Data in IMS ACCTDBD Database
SOC_SEC_
NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2
CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE
----------------------------------------------------------------------------------------------------------------------
234-74-4612 WIKOWSKI, JONATHAN S. 151 Knox St.
RICHMOND VA USA 27702-3317 803-467-4587 803-654-7238
| Inserting and Deleting Data with the SQL Procedure |
You can use the INSERT statement to add segments to an IMS database or use the DELETE statement to remove segments from an IMS database, as you did earlier in this section with the FSEDIT procedure. When inserting children under a parent segment, you must indicate the key values of the parent segments in the SET= statement. Use a view descriptor that describes the entire path of data down to the lowest segment to be inserted. In the following example, the root segment that contains the value 234-74-4612 for the SOC_SEC_NUMBER variable is deleted from the ACCTDBD database. Note that any child segments that exist under the parent segment in this example will also be deleted.
options linesize=132; proc sql; delete from vlib.custinfo where soc_sec_number = '234-74-4612'; title2 'Observation Deleted from IMS ACCTDBD Database'; select * from vlib.custinfo;
The SELECT statement then displays the data for VLIB.CUSTINFO in the following output.
Results of Deleting IMS Data with the DELETE Statement
The SAS System
Observation Deleted from IMS ACCTDBD Database
SOC_SEC_
NUMBER CUSTOMER_NAME ADDR_LINE_1 ADDR_LINE_2
CITY STATE COUNTRY ZIP_CODE HOME_PHONE OFFICE_PHONE
----------------------------------------------------------------------------------------------------------------------
667-73-8275 WALLS, HOOPER J. 4525 CLARENDON RD
RAPIDAN VA USA 22215-5600 803-657-3098 803-645-4418
434-62-1234 SUMMERS, MARY T. 4322 LEON ST.
GORDONSVILLE VA USA 26001-0670 803-657-1687
436-42-6394 BOOKER, APRIL M. 9712 WALLINGFORD PL.
GORDONSVILLE VA USA 26001-0670 803-657-1346
434-62-1224 SMITH, JAMES MARTIN 133 TOWNSEND ST.
GORDONSVILLE VA USA 26001-0670 803-657-3437
178-42-6534 PATTILLO, RODRIGUES 9712 COOK RD.
ORANGE VA USA 26042-1650 803-657-1346 803-657-1345
156-45-5672 O'CONNOR, JOSEPH 235 MAIN ST.
ORANGE VA USA 26042-1650 803-657-5656 803-623-4257
657-34-3245 BARNHARDT, PAMELA S. RT 2 BOX 324
CHARLOTTESVILLE VA USA 25804-0997 803-345-4346 803-355-2543
667-82-8275 COHEN, ABRAHAM 2345 DUKE ST.
CHARLOTTESVILLE VA USA 25804-0997 803-657-7435 803-645-4234
456-45-3462 LITTLE, NANCY M. 4543 ELGIN AVE.
RICHMOND VA USA 26502-3317 803-657-3566 - CAUTION:
- Use a WHERE clause in a DELETE statement in
the SQL procedure.
If you omit the WHERE clause from the DELETE statement in the SQL procedure, you delete the lowest level segment for each database path that is defined by the view descriptor in the IMS database. If the view descriptor describes only the root segment, the entire database will be deleted.
For more information about the SQL procedure, see the SAS SQL Procedure User's Guide.
| Updating Data with the MODIFY Statement |
The MODIFY statement extends the capabilities of the DATA step by enabling you to modify IMS data that is accessed by a view descriptor or a SAS data file without creating an additional copy of the file. To use the MODIFY statement with a view descriptor, you must have update privileges defined in the PCB associated with the view, even if your program doesn't intend to modify the data.
You can specify either a view descriptor or a SAS data file as the data set to be opened for update by using the MODIFY statement. In the following example, the data set to be opened for update is the view descriptor VLIB.CUSTINFO, which describes data in the IMS sample database ACCTDBD. See Example Data for the code used to generate this view descriptor and the access descriptor MYLIB.ACCOUNT. The updates made to VLIB.CUSTINFO will be used to change the data in the ACCTDBD database. In order to update VLIB.CUSTINFO, you create a SAS data set, MYDATA.PHONENUM, to supply transaction information.
The MODIFY statement updates the ACCTDBD database with data from the MYDATA.PHONENUM data set in the following example:
data vlib.custinfo work.phoneupd (keep=soc_sec_number home_phone office_phone) work.nossnumb (keep=soc_sec_number home_phone office_phone); modify vlib.custinfo mydata.phonenum; by soc_sec_number; select (_iorc_); when (%sysrc(_sok)) /* soc_sec_number found in ACCTDBD */ do; replace vlib.custinfo; output phoneupd; end; when (%sysrc(_dsenmr)) /* soc_sec_number not found in ACCTDBD */ do; _error_=0; output nossnumb; /* stores misses in NOSSNUMB */ end; otherwise /* traps unexpected outcomes */ do; put 'Unexpected error condition: _iorc_ = ' _iorc_; put 'for SOC_SEC_NUMBER=' soc_sec_number '. DATA step continuing.'; _error_=0; end; end; run;
For each iteration of the DATA step, SAS attempts to read one observation (or record) of the ACCTDBD database as defined by VLIB.CUSTINFO, based on SOC_SEC_NUMBER values supplied by MYDATA.PHONENUM. If a match on SOC_SEC_NUMBER values is found, the current segment data in ACCTDBD is replaced with the updated information in MYDATA.PHONENUM, then SOC_SEC_NUMBER, HOME_PHONE and OFFICE_PHONE are stored in the PHONEUPD data file. If the SOC_SEC_NUMBER value supplied by MYDATA.PHONENUM has no match in VLIB.CUSTINFO, the transaction information is written to the data file NOSSNUMB.
To further understand this type of processing, be aware that for each iteration of the DATA step (that is, each execution of the MODIFY statement), MYDATA.PHONENUM is processed sequentially. For each iteration, the current value of SOC_SEC_NUMBER is used to attach a WHERE clause to a request for an observation from VLIB.CUSTINFO as defined by the view. The engine then tries to generate a retrieval request with a qualified SSA from the WHERE clause. If the engine generates a qualified SSA, a GET UNIQUE call is issued, and data that is defined by the view is accessed directly. If the engine cannot generate a qualified SSA from the WHERE clause, a sequential pass of the database is required for each transaction observation in MYDATA.PHONENUM.
To print the PHONEUPD data file to see the SOC_SEC_NUMBER items that were updated, submit the following statements.
/* Print data set named phoneupd */ proc print data=work.phoneupd nodate; title2 'SSNs updated.'; run;
The results are shown in the following output:
Contents of the PHONEUPD Data File
The SAS System
SSNs updated.
SOC_SEC_
OBS NUMBER HOME_PHONE OFFICE_PHONE
1 667-73-8275 703-657-3098 703-645-4418
2 434-62-1234 703-645-441
3 178-42-6534 703-657-1346 703-657-1345
4 156-45-5672 703-657-5656 703-623-4257
5 657-34-3245 703-345-4346 703-355-5438
6 456-45-3462 703-657-3566 703-645-1212
To print the NOSSNUMB data set to see the SOC_SEC_NUMBER items that
were not updated submit the following statements.
/* Print data set named nossnumb */ proc print data=work.nossnumb nodate; title2 'SSNs not updated.'; run;
The results produced are shown in the following output:
Contents of the NOSSUNUMB Data File
The SAS System
SSNs not updated.
SOC_SEC_
OBS NUMBER HOME_PHONE OFFICE_PHONE
1 416-41-3162 703-657-3166 703-615-1212
|
|
Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.