libname db2lib db2 ssid=db2;
     data payroll;
        update db2lib.payroll db2lib.payroll2;
        by idnum;
     run;

SAS software assumes that the variables in SAS data sets correspond to the columns in DBMS tables. If the variables and columns do not match, you can use the DROP= or KEEP= data set options to specify only the SAS variables and DBMS columns that do correspond. For details on the BY statement, see the lesson Merging SAS Data Sets. To learn more about the UPDATE statement, see SAS Language Reference: Dictionary.

Example 4: Updating DBMS Data You can also write directly to a DBMS, updating rows, columns, and tables with the SQL procedure. The following example adds a new row to the DB2 table Superv. libname db2lib db2 ssid=db2; proc sql; insert into db2lib.superv values('1588','NY','FA'); quit; For more information on PROC SQL, see SAS Procedures Guide. Example 5: Creating a DBMS Table You can create new tables in a DBMS with the SQL procedure. This example creates the ORACLE table Gtforty by retrieving data from the Staff and Payroll tables. libname oraclib oracle user=cestmoi password=sesame path=oracpath preserve_col_names=yes; proc sql dquote=ansi; create table oraclib.gtforty as select lname as LastName, fname as FirstName, salary as Salary format=dollar10.2 from oraclib.staff a, oraclib.payroll b where (a.idnum eq b.idnum) and (salary gt 40000); quit; DQUOTE= is a PROC SQL option that specifies whether PROC SQL treats values within double quotation marks as a character string or as a column name or table name. For more information on this option, see the section on SAS Names and Support for DBMS Names in SAS/ACCESS Software for Relational Databases. For more information on PROC SQL, see SAS Procedures Guide.