02 Altering Table Structure

Make a working copy of orion.employee_payroll

proc sql;
    create table employee_payroll as
    select *
    from orion.employee_payroll
    ;
quit;  
proc contents data=employee_payroll;run;

Add a column to work.employee_payroll

The alter table clause with add option

proc sql;
   alter table Employee_payroll
      add Bonus num format=comma10.2,
          Level char(3)
;
quit;
proc contents data=employee_payroll;
run;
 

Create a new table payroll and alter it to drop birth_date

The alter table clause with drop option

proc sql;
create table payroll as
select * from employee_payroll
;
alter table payroll
      drop birth_date
;
quit;
proc contents data=payroll;
run;

Alter the payroll table to add two new columns

proc sql;
   alter table payroll
      add Bonus num format=comma10.2,
          Level char(3)
;
quit;
proc contents data=payroll;
run;

Modify attributes of existing columns in a table.

You can alter a column’s length, informat, format, and label.

proc sql;
   alter table payroll
      modify Bonus num format=comma8.2,
         Level char(10)
         label='Employee Level';
quit;
proc contents data=payroll;
run;

Create sales_staff table

proc sql;
   create table Sales_staff as
      select Employee_ID, catx(', ',Last_Name, First_Name) 
             as Name,
             Job_Title, Salary, Gender,
            Birth_Date, Hire_Date,Country
            from orion.Sales
    order by Last_Name, First_Name
   ;
quit;

Alter the work.Sales_staff table:

Add new columns named First and Last.

Format the Birth_Date column with the MMDDYY10. format.

proc sql;
    alter table sales_staff
    add first char(10),last char(25)
    modify birth_date format mmddyy10.
    ;
quit;
proc contents data=sales_staff;
run;

Populate the new columns

The STRIP function returns a character string with all leading and trailing blanks removed.

proc sql;
    update sales_staff
    set first=strip(scan(name,2,",")),
        last=strip(scan(name,1,","))
        ;
    reset outobs=10;
    select name,first,last
    from sales_staff;
quit;