How to delete rows from a DB2 table based on a variable value

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

/* V7:  How to delete rows from a DB2 table based on a variable value */

/* This example deletes all employees who work in Connecticut from the DB2
table STAFF.*/ 

libname mydb2lib db2 ssid=db2;

proc sql;
  delete from mydb2lib.staff
    where state='CT';
quit;

options obs=20;

proc print data=mydb2lib.staff;
title "AIRLINE.STAFF After Deleting 
       Connecticut Employees";
run;

/* Note:   If you omit a WHERE clause when you delete rows from a table, all
rows in the table are deleted.    
*/


/* V7: Create a dataset from DB2 data and add a column based your
query*/

/* This example creates the SAS data set WORK.HIGHWAGE from the DB2 table
PAYROLL and adds a new variable, CATEGORY. The CATEGORY variable is based
on the value of the salary column in the DB2 table PAYROLL. The PAYROLL
table is not modified. 
*/

libname mydb2lib db2 ssid=db2;     
                              
data highwage;                                            
  set mydb2lib.payroll(drop=sex birth hired);                 
  if salary>60000 then                                    
    CATEGORY="High";                                      
  else if salary<30000 then                               
    CATEGORY="Low";                                       
  else                                                    
    CATEGORY="Avg";                                       
run;                                                      


options obs=20;                                                          
proc print data=highwage;                                 
  title "Salary Analysis";                                
  format salary dollar10.2;            
run; 

/* V7: Query and join three DB2 tables */

/* Use the SQL procedure to join and query the DB2 tables MARCH, DELAY,
and FLIGHT. The query retrieves information on delayed international
flights during the month of March. 
*/

libname mydb2lib db2 ssid=db2;

title "Delayed International Flights in March";                
                                                               
proc sql;                                                      
  select distinct march.flight, march.dates, 
      delay format=2.0  
    from mydb2lib.march, mydb2lib.delay, 
      mydb2lib.internat     
  where march.flight=delay.flight and                          
    march.dates=delay.dates and           
and                                
    march.flight=internat.flight and                           
    delay>0                                                    
  order by delay descending; 
quit;    

/*
Note:   For optimized performance, the SAS/ACCESS engine passes the entire
join to the DBMS for processing.   

*/

/* V7:  Query DB2 data */ 

/*
In the following example, the libref MYDB2LIB is assigned with the DB2
engine to associate the libref with DB2 tables and views. The PRINT
procedure prints a phone list containing information for staff in New
Jersey from the DB2 table STAFF. Information for staff from states other
than New Jersey is not printed. The DB2 table STAFF is not modified. 

Note that you can use a libref that contains DBMS data in the DATA= option.
*/

libname mydb2lib db2 ssid=db2;

proc print data=mydb2lib.staff
  (keep lname fname hphone);
  where state = 'NJ';
  title 'New Jersey Phone List';
run;



/* V7:  Query DB2 data and add labels to the output */

/* 
This example uses the SQL procedure to query the DB2 table PAYROLL for
information on all flight attendants, ordered by JOBCODE and SERVICE. 
*/


libname mydb2lib db2 ssid=db2;

proc sql;
   title  'Service Years and Salary';
   title2 'for Flight Attendants';
   select idnum     label='ID Number',
          jobcode   label='Job Code',
          salary    label='Salary' 
                    format dollar7.,
          (today()-HIRED)/365.25 as service
                    label='Years Service' 
                    format 4.1,
          hired     label='Hire Date'
    from mydb2lib.payroll
    where jobcode like 'FA%'
    order by jobcode, service;
quit;

      

/* V7:  Query DB2 data and use the ORDER BY clause to order data */

/*
The next example uses the SQL procedure to query the DB2 table INTERNAT for
information on international flights with over 200 passengers. Note that
output can be sorted by using a PROC SQL query and that the TITLE, LABEL,
and FORMAT key words are not ANSI standard SQL; they are SAS extensions
that you can use in PROC SQL. 
*/

libname mydb2lib db2 ssid=db2;

proc sql;
   title  'Intnatl Flights by Flight Number';
   title2 'with Over 200 Passengers';
   select flight   label="Flight Number",
          dates    label="Departure Date" 
                   format DATE9.,
          dest     label="Destination",
          boarded  label="Number Boarded"
     from mydb2lib.internat
    where boarded > 200
    order by flight;
quit;



/* V7: Update a DB2 table */

/*
In addition to querying data, you can also update data directly in your
DBMS. You can update rows, columns, and tables by using the SQL procedure.

The following example adds a new row to the DB2 table SUPERV. 
*/

libname mydb2lib db2 ssid=db2;

proc sql;
insert into mydb2lib.superv
  values('1588','NY','FA');
quit;

proc print data=mydb2lib.superv;
  title "New Row in AIRLINE.SUPERV";
run;

/*
Note:   Depending on how your DBMS processes inserts, the new row might not
be added as the last physical row of the table.   
*/

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information