SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference

Browsing and Updating with the SQL Procedure

The SAS System's SQL procedure enables you to retrieve and update data from SYSTEM 2000 databases. You can retrieve and browse SYSTEM 2000 data by specifying a view descriptor in a PROC SQL SELECT statement. To update the data, you can specify view descriptors in the PROC SQL INSERT, DELETE, and UPDATE statements. You must be granted update authority before you can edit SYSTEM 2000 data. Here is a summary of these PROC SQL statements:

DELETE: deletes values from a SYSTEM 2000 database.
INSERT: inserts values in a SYSTEM 2000 database.
SELECT: retrieves and displays data from SYSTEM 2000 databases. A SELECT statement is usually referred to as a query because it queries the database for information.
UPDATE: updates values in a SYSTEM 2000 database.

The query in the following example retrieves and displays values in the SYSTEM 2000 EMPLOYEE database that are described by the VLIB.EMPPOS view descriptor, assuming that the previous updates with the SAS/FSP procedures have occurred (that is, you added the values Mary and Adkins to the programmer position.) Notice that the SQL procedure prints the variable labels instead of the SAS variable names and the data are displayed in the SAS OUTPUT window. Notice also that the SELECT statement executes without your using a RUN statement, because the SQL procedure displays output data automatically, without your using the PRINT procedure, and executes when you submit it.

    proc sql;
       title 'SYSTEM 2000 Data Output Using 
          a SELECT Statement';
    select *      
    /* Asterisk indicates 'select all items' */
       from vlib.emppos;

SYSTEM 2000 Data Output Using a PROC SQL Query displays the query's results.

SYSTEM 2000 Data Output Using a PROC SQL Query

SYSTEM 2000 Data Output Using a SELECT Statement LAST NAME FORENAME POSITION TITLE DEPARTMENT MANAGER ----------------------------------------------------------------- ADKINS MARY PROGRAMMER INFORMATION SY MYJ AMEER DAVID JR SALES REPRESE MARKETING VPB AMEER DAVID SR SALES REPRESE MARKETING VPB BOWMAN HUGH E. EXECUTIVE VICE-P CORPORATION CPW BROOKS RUBEN R. JR SALES REPRESE MARKETING MAS BROWN VIRGINA P. MANAGER WESTERN MARKETING OMG CAHILL JACOB MANAGER SYSTEMS INFORMATION SY JBM CANADY FRANK A. MANAGER PERSONNE ADMINISTRATION PRK CHAN TAI SR SALES REPRESE MARKETING TZR COLLINS LILLIAN MAIL CLERK ADMINISTRATION SQT FAULKNER CARRIE ANN SECRETARY CORPORATION JBM

As in the SAS/FSP procedures, you can specify a WHERE clause as part of the SELECT statement to subset the observations you want to display. The following example requests the employees that are technical writers.

    title 'SYSTEM 2000 Data Output Subset by a 
       WHERE Clause';
    select *
       from vlib.emppos
       where position='TECHNICAL WRITER';

Notice that the PROC SQL statement is not repeated in this query. With the SQL procedure, you do not need to repeat the PROC statement unless you use another SAS procedure or the DATA step between PROC SQL statements. Because you are referencing a view descriptor, you use the SAS names for items in the WHERE clause.SYSTEM 2000 Data Output Subset by a WHERE Clause displays the one employee who is a technical writer.

SYSTEM 2000 Data Output Subset by a WHERE Clause

SYSTEM 2000 Data Output Subset by a WHERE Clause LAST NAME FORENAME POSITION TITLE DEPARTMENT MANAGER ----------------------------------------------------------------- GIBSON MOLLY I. TECHNICAL WRITER INFORMATION SY JC

You can use the UPDATE statement to update SYSTEM 2000 data. Remember that when you reference a view descriptor in a PROC SQL statement, you are not updating the view descriptor, but rather the SYSTEM 2000 data described by the view descriptor. Suppose that Mary Adkins, whom you previously added to the unfilled programmer position, decided to change her position from programmer to technical writer. You could update her position title and manager as follows:

    update vlib.emppos
       set position='TECHNICAL WRITER'
       where lastname='ADKINS';
    update vlib.emppos
       set manager='JC'
       where lastname='ADKINS';
 
       title 'Updated VLIB.EMPPOS View Descriptor';
    select *
       from vlib.emppos;

Updated VLIB.EMPPOS View Descriptor displays the query's results.

Updated VLIB.EMPPOS View Descriptor

Updated VLIB.EMPPOS View Descriptor 1 LAST NAME FORENAME POSITION TITLE DEPARTMENT MANAGER ------------------------------------------------------------------- ADKINS MARY TECHNICAL WRITER INFORMATION SY JC AMEER DAVID SR SALES REPRESE MARKETING VPB AMEER DAVID JR SALES REPRESE MARKETING VPB BOWMAN HUGH E. EXECUTIVE VICE-P CORPORATION CPW BROOKS RUBEN R. JR SALES REPRESE MARKETING MAS BROWN VIRGINA P. MANAGER WESTERN MARKETING OMG CAHILL JACOB MANAGER SYSTEMS INFORMATION SY JBM CANADY FRANK A. MANAGER PERSONNE ADMINISTRATION PRK CHAN TAI SR SALES REPRESE MARKETING TZR COLLINS LILLIAN MAIL CLERK ADMINISTRATION SQT FAULKNER CARRIE ANN SECRETARY CORPORATION JBM

You can use the INSERT statement to add values to a SYSTEM 2000 database or the DELETE statement to remove values as described by a view descriptor. In the following example, the values described by the view descriptor VLIB.EMPPOS for the employee with the last name of Adkins are deleted from the EMPLOYEE database:

    delete from vlib.emppos
       where lastname='ADKINS';

       title 'Data Deleted from SYSTEM 2000 EMPLOYEE
         Database';
    select *
     from vlib.emppos;

VLIB.EMPPOS Data with an Observation Deleted displays the query's results.

VLIB.EMPPOS Data with an Observation Deleted

Data Deleted from SYSTEM 2000 EMPLOYEE Database 1 LAST NAME FORENAME POSITION TITLE DEPARTMENT MANAGER ----------------------------------------------------------------- ADKINS MARY AMEER DAVID SR SALES REPRESE MARKETING VPB AMEER DAVID JR SALES REPRESE MARKETING VPB BOWMAN HUGH E. EXECUTIVE VICE-P CORPORATION CPW BROOKS RUBEN R. JR SALES REPRESE MARKETING MAS BROWN VIRGINA P. MANAGER WESTERN MARKETING OMG CAHILL JACOB MANAGER SYSTEMS INFORMATION SY JBM CANADY FRANK A. MANAGER PERSONNE ADMINISTRATION PRK CHAN TAI SR SALES REPRESE MARKETING TZR COLLINS LILLIAN MAIL CLERK ADMINISTRATION SQT FAULKNER CARRIE ANN SECRETARY CORPORATION JBM

CAUTION:: You must use the WHERE clause in the DELETE statement. If you omit the WHERE clause from a SQL procedure DELETE statement, you will delete all the data in the database accessed by the view descriptor.

For more information on the SAS System SQL procedure, see the SAS Procedures Guide.

Chapter Contents
Previous
Next
Top of Page