Managing Tables with SQL

1. Modify

2. Alter

3. Delete

01 Modifying Tables

Assign a libname using Sas University Edition.

myshortcuts is a second mounted shared folder

libname orion "/folders/myshortcuts/SasData/sql";

Make a copy of orion.sales

proc sql;
    create table sales as
    select * 
    from orion.sales
    ;
quit;    

Modifying Existing Rows

Use Update clause with set and where clauses

Example -- Give first-level sales representatives a 5% raise.

proc sql;
   update work.Sales
      set Salary=Salary * 1.05
      where Job_Title = "Sales Rep. I";
quit;

    

Example -- The company is reorganizing, and will use new descriptors for a person’s job mastery level.

A new column, Level, needs to be added to the work.Staff table to contain this information.

Then the data on these levels need to be added to the table, based on the rules:

If Current Job Title Ends in	The New Level Is
I	Apprentice
II	Journeyman
III	Master
IV	Mentor

Add a column, level, to the table

proc sql;
   create table Staff as
      select *, "" as Level length=10 
         from sales
    ;
reset outobs=25;
    select job_title,level from staff
    ;
quit;

Modify the table conditionally

proc sql;
   update work.Staff
      set Level=
         case (scan(Job_Title,-1))
             when 'I'   then 'Apprentice'
             when 'II'  then 'Journeyman'
             when 'III' then 'Master'
             when 'IV'  then 'Mentor'
             else ''
          end;
    reset outobs=25;
   select Employee_ID, Job_Title, Level
      from work.Staff;
quit;

Deleting rows from tables

Example -- Create a table work.staff containing only active employees

Create a copy of complete data set

proc sql;
   create table staff as
   select *
   from prg1.employee_payroll
   ;
quit;   
  

count number of active employees

proc sql;
    select count(*)
    from staff
    where employee_term_date is not missing
    ;
    select count(*)
    from staff
    ;
quit; 

proc sql;
   delete from Staff
      where Employee_Term_Date is not missing
;
quit;

proc sql;
    select count(*) 
    from staff
    ;
quit;