Chapter 2 Creating and Managing Tables using PROC

Chapter 2: 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 General Integrity Constraints – – CHECK NOT NULL UNIQUE PRIMARY KEY (NOT NULL and UNIQUE)

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; 19

Integrity Constraints n Constraints can be viewed with the DESCRIBE TABLE CONSTRAINTS statement: proc sql; describe table constraints work. lab 2012; quit; 20

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; 21

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; 22

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 23

Updating Tables n Updating Existing Rows – With a common expression – With a conditional expression (similar to IFELSE construction) 24

Updating Tables n Use the SET expression to modify values n The WHERE clause can make the change conditional 25

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 27

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; 29

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; 30

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 31

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; 33

Dictionaries DICTIONARY. TABLES contains meta data about tables and views § DICTIONARY. COLUMNS contains meta data about columns in tables § Can be compared to PROC CONTENTS § © Spring 2012 Imelda Go, John Grego, Jennifer Lasecki and the University of Imelda Go, John Grego, Jennifer. South Lasecki, 2011 34 Carolina 34

Dictionaries To find what variables are available, use DESCRIBE TABLE § A specific query proc sql; select memname, memtype, nobs, nvar, num_character, num_numeric, filesize, crdate from dictionary. tables where libname='WORK'; quit; § © Spring 2012 Imelda Go, John Grego, Jennifer Lasecki and the University of Imelda Go, John Grego, Jennifer. South Lasecki, 2011 35 Carolina 35