Chapter 5: Creating And Managing Tables Using Proc SQL

proc sql; create table work.newpayroll as select * from sasuser.payrollmaster;

proc sql; create table work.newpayroll like sasuser.payrollmaster;

proc sql; create table work.newpayroll copy sasuser.payrollmaster;

proc sql; create table work.newpayroll describe sasuser.payrollmaster;

proc sql; create table work.newpayroll as select * from sasuser.payrollmaster where jobcode='FA1';

proc sql; create work.newpayroll as select * from sasuser.payrollmaster where jobcode='FA1';

proc sql; create table work.newpayroll copy sasuser.payrollmaster where jobcode='FA1';

proc sql; create table work.newpayroll as sasuser.payrollmaster where jobcode='FA1';

It must be used with the REQUIRED integrity constraint.

It ignores the specified integrity constraints if any of the rows that you want to insert or update do not meet the constraint criteria.

It restores your table to its original state if any of the rows that you try to insert or update do not meet the specified integrity constraint criteria.

It allows rows that meet the specified integrity constraint criteria to be inserted or updated, but rejects rows that do not meet the integrity constraint criteria.

CHECK

NULL

UNIQUE

PRIMARY KEY

proc sql; delete rows from work.frequentflyers where milestraveled < 10000;

proc sql; drop rows from work.frequentflyers where milestraveled < 10000;

proc sql; drop table from work.frequentflyers where milestraveled < 10000;

proc sql; delete from work.frequentflyers where milestraveled < 10000;

proc sql; update work.frequentflyers set pointsearned=pointsearned* case if milestraveled < 10000 then 1.5 if milestraveled >= 10000 then 2 else 1 end;

proc sql; update work.frequentflyers set pointsearned=pointsearned* case when milestraveled < 10000 then 1.5 when milestraveled >= 10000 then 2 else 1 end;

proc sql; update work.frequentflyers set pointsearned=pointsearned* case if milestraveled < 10000 then pointsearned*1.5 if milestraveled >= 10000 then pointsearned*2 else 1 end;

proc sql; update work.frequentflyers set pointsearned=pointsearned* case if milestraveled < 10000 then pointsearned*1.5 if milestraveled >= 10000 then pointsearned*2 else pointsearned*1 end;

INSERT

LOAD

VALUES

CREATE TABLE

It allows you to update column attributes.

It allows you to add new columns in your table.

It allows you to drop columns in your table.

It allows you to change a character column to a numeric column.

proc sql; describe as select * from sasuser.payrollmaster;

proc sql; describe contents sasuser.payrollmaster;

proc sql; describe table sasuser.payrollmaster;

proc sql; describe * from sasuser.payrollmaster;

proc sql; create table work.names (FullName char(25), Age num);

proc sql; create table work.names as (FullName char(25), Age num);

proc sql; create work.names (FullName char(25), Age num);

proc sql; create table work.names set (FullName char(25), Age num);