Chapter 5 Creating and Managing Tables using PROC
Chapter 5: Creating and Managing Tables using PROC SQL © Spring 2012 Imelda Go, John Grego, Jennifer Lasecki and the University of South Carolina 1
Outline § § § Creating Tables Inserting Rows into Tables Integrity Constraints Updating Tables Altering Columns 2
Creating an Empty Table By Defining Columns § By Copying Column Definitions from another table § 3
Stat Lab 2012 § § § § Client Name Client Email Client Degree USC Client Department Date Gratis Consultant 4
Creating an Empty Table § By Defining Columns proc sql; create table work. lab 2012 (Client char(12), Email char(19), Degree char(8), USC char(1), Dept char(20), Date num format=mmddyy 10. label=‘Date of First Contact’, Gratis char(1), Consultant char(10)); quit; 5
Creating an Empty Table § By Defining Columns - SQL supports other data types, but PROC SQL simply converts them to either character or numeric - Width of CHAR column can be varied - FORMAT and LABEL is supported - proc sql; describe table work. lab 2011; quit; will print the table definition to the LOG 6
Creating an Empty Table § By copying the format of another table For the Stat Lab, it would make more sense to copy columns, column labels and formats directly from a 2011 table. 7
Stat Lab 2011 Name Email Degree USC Dept Date Gratis Consult ant Joe Bridges. J @dnr. sc. gov External N SCDNR 12/03/20 11 N John Grego Gina White whiteg@ Faculty biol. sc. ed u Y Biology 12/05/20 11 Y Wilma Sims Matthew King kingm 3@ Doctoral email. sc. edu Y Library& Info. Sci. 12/15/20 11 Y John Grego Y Medical School 12/16/20 11 Y Wilma Sims Wenkuan zhaow@ Zhao email. sc. edu Faculty © Spring 2012 Imelda Go, John Grego, Jennifer Lasecki and the University of South Carolina 8
Creating an Empty Table § By copying the format of another table proc sql; create table work. lab 2012 like admin. lab 2011; quit; proc sql; describe table work. lab 2012; quit; § Columns can be specified with DROP or KEEP 9
Creating a Table from a query n We have been using the Create Table statement periodically throughout the class to save results from queries as SAS data sets proc sql; create table sims 2011 as select * from admin. lab 2011 where consultant=‘Wilma Sims’; Quit; 10
Copying a Table Convenient for moving a table from a permanent location into WORK, or vice versa proc sql; create table work. lab 2011 as select * from admin. lab 2011; quit; § 11
Inserting Rows into a Table Using SET § Using VALUES § Using a query Some of these methods will seem terribly cumbersome, but can be useful to add a handful of new observations § 12
Inserting Rows into a Table § Using SET proc sql; insert into work. lab 2012 set client=‘Jane Lipschitz’, email=‘lipschitzj@dnr. sc. gov, degree=‘External’, USC=‘N’, Dept=‘SCDNR’, date=‘ 25 Jan 2012’d, Gratis=‘Y’, Consultant=‘John Grego’ set client=‘Gerry Bainbridge’, email=‘bainbrid@email. sc. edu, degree=‘Faculty’, USC=‘Y’, Dept=‘School of Music’, date=‘ 31 Jan 2012’d, Gratis=‘Y’, Consultant=‘John Grego’; select * from lab 2012; quit; 13
Inserting Rows into a Table § Using VALUES - An entire set of columns can be entered in order - A subset can be entered in a pre-specified order 14
Inserting Rows into a Table § Using VALUES proc sql; insert into work. lab 2012 values(‘Scott Tyler’, ’tylers@jonesctr. org’, ’External’, ’N’, ’JERC’, ’ 17 Jan 2012’d, ‘N’, ’John Grego’); select * from work. lab 2012; quit; 15
Inserting Rows into a Table § Using VALUES proc sql; insert into work. lab 2012 (Client, Consultant, Degree, USC, Date, Dept, Gratis) values(‘Erin Merrick’, ’Wilma Sims’, ’Masters’, ’Y’, ’ 07 Jan 2012’d, ’Environment’, ‘N’); select * from work. lab 2012; quit; 16
Inserting Rows into a Table § Using a query proc sql; insert into work. lab 2012 select * from admin. lab 2011 where month(date)=12; select * from work. lab 2012; quit; 17
Integrity Constraints n n Restrict data values that can be assigned to columns PROC DATASETS can create integrity constraints too, but only for existing data sets
Integrity Constraints n General Integrity Constraints – – – CHECK NOT NULL UNIQUE PRIMARY KEY (NOT NULL and UNIQUE) FOREIGN KEY (relational databases)
Integrity Constraints proc sql; create table work. lab 2012 (Client char(12) primary key, Email char(19), Degree char(8), USC char(1) check(USC in (‘N’ ’Y’ ’y’ ’n’), Dept char(20), Date num format=mmddyy 10. check(date between ’ 01 Jan 2012’d and ’ 31 Dec 2012’d), Gratis char(1), Consultant char(10) check(Consultant in (‘John Grego’, ’Wilma Sims’)); quit; 20
Integrity Constraints n Constraints can be viewed with the DESCRIBE TABLE CONSTRAINTS statement: proc sql; describe table constraints work. lab 2012; quit; 21
Integrity Constraints proc sql; insert into work. lab 2012 values(‘Erin Merrick’, ’merrickj@email. sc. edu’ , ’Masters’, ’Y’, ‘Environment’, ’ 07 Jan 2011’d, ’Y’, ’Wilma Sim’) values(‘Scott Tyler’, ’tylers@jonesctr. org’, ’External’, ’No’, ’JERC’, ’ 17 Jan 2012’d, ‘N’, ’John Grego’) values(‘Erin Merrick’, ‘Masters’, ’Y’, ‘Environment’, ’ 01 Feb 2012’d, ‘N’, ’Wilma Sims’) ; quit; 22
Integrity Constraints A separate CONSTRAINT statement can also be used to handle integrity constraints n Names are assigned to the constraints n proc sql; create table work. lab 2012 (Client char(12) primary key, Email char(19), Degree char(8), USC char(1) Dept char(20), Date num format=mmddyy 10. , Gratis char(1), Consultant char(10), constraint Check_USC check(USC in (‘N’ ’Y’ ’y’ ’n’)) ); quit; 23
Integrity Constraints By default, SAS will not accept any additional rows once it finds an error n Options for UNDO_POLICY n – – – n REQUIRED (the record is not added) NONE (the record is skipped) OPTIONAL (hybrid that inserts records when possible) Table constraints can be viewed using DESCRIBE 24
Updating Tables n Updating Existing Rows – With a common expression – With a conditional expression (similar to IFELSE construction) 25
Updating Tables n Use the SET expression to modify values n The WHERE clause can make the change conditional 26
Updating Tables-Example proc sql; create table lab 2012 pay like admin. lab 2011 pay; quit; proc sql; update lab 2012 pay set rate=rate*1. 05; quit; proc sql; update lab 2012 pay set rate=rate*1. 0013 where consultant='Director'; quit;
Updating Tables n Use the SET expression with a CASE clause for conditional changes to a variable n The use of CASE will be very familiar to those who have used IFELSE in either R or Excel n CASE can be used elsewhere in a PROC SQL clause 28
Updating Tables-Example proc sql; update lab 2012 set rate=rate*case when consultant=‘Director’ then 1. 05 when consultant=‘Manager’ then 1. 0375 else 1. 045 end;
Updating Tables n The update can be modified for greater efficiency proc sql; update lab 2012 set rate=rate*case consultant when ‘Director’ then 1. 05 when ‘Manager’ then 1. 0375 else 1. 045 end; 30
Updating Tables n DELETE FROM can be used to eliminate rows: proc sql; delete from lab 2012 where date lt ’ 01 Jan 2012’d; quit; 31
Updating Tables n ALTER TABLE can be used to update columns and column attributes n Options can be entered separately or simultaneously – ADD – MODIFY – DROP n MODIFY cannot change a column’s name 32
Updating Tables-Example proc sql; alter table lab 2012 add College char(20) label=‘College or School’, time format=hour 4. 1 modify date format=weekdate 31. quit;
Updating Tables n DROP TABLE can be used to delete a table proc sql; drop table admin. lab 2011; quit; 34
- Slides: 34