Browsing and Updating with the SQL Procedure

The SQL procedure in SAS enables you to retrieve and update data from SYSTEM 2000 databases. You must have update authority in order to edit SYSTEM 2000 data. To retrieve and browse SYSTEM 2000 data, specify a view descriptor by using a SELECT statement in PROC SQL. To update the data, you can specify view descriptors in the INSERT, DELETE, and UPDATE statements in PROC SQL. The following 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 program retrieves and displays values in the SYSTEM 2000 database Employee. These values are described by the view descriptor Vlib.Emppos, if the previous updates using the SAS/FSP procedures have occurred (that is, you added the values Mary and Adkins to the programmer position.) Notice that PROC SQL prints the variable labels instead of the SAS variable names, and the data is displayed in the SAS output window. Notice also that the SELECT statement executes without using a RUN statement. PROC SQL executes when you submit it and displays output data automatically, without your having to use the PRINT procedure. The following output displays the results.

    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

                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 in the SELECT statement to subset the observations that you want to display. The following program requests data about employees who are technical writers. Notice that the PROC SQL statement is not repeated in this query because you do not need to repeat the PROC SQL statement unless you use another SAS procedure or DATA step between PROC SQL statements. Because you are referencing a view descriptor, you use the SAS names for items in the WHERE clause. The following output shows the data about the one employee who is a technical writer.

    title 'SYSTEM 2000 Data Output Subset by a 
       WHERE Clause';
    select *
       from vlib.emppos
       where position='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. You are updating the SYSTEM 2000 data described by the view descriptor. Therefore, if Mary Adkins, whose name you previously added to the unfilled programmer position, decided to change her position from programmer to technical writer, you could update the information about her position title and manager by using the following program. The following output displays the results.

    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

                    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 program, the values described by the view descriptor Vlib.Emppos for the employee whose last name is Adkins are deleted from the database Employee. The following output displays the results.

    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

 
            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 DELETE statement in PROC SQL, you delete all the data in the database accessed by the view descriptor.

For more information about the SQL procedure in SAS, see the Base SAS Procedures Guide.