*Slide 5;
proc sql; 
create table work.lab2012
(Client char(12), Email char(19), Degree char(8), USC char(1), Dept char(20), Date num format=mmddyy10. label='Date of First Contact', Gratis char(1), Consultant char(10));
quit;

*Slide 6;
proc sql; 
describe table work.lab2012; 
quit; 

*Slide 8;

data lab2011;
input client $12. @14 email $19. @34 degree $8. @43 usc $1. @45 dept $20. @66 date mmddyy10. 
      @77 gratis $1. @79 consultant $10.;
	  format date date9.;
datalines;
Joe Bridges  BridgesJ@dnr.sc.gov External N SCDNR                12/03/2011 N John Grego
Gina White   whiteg@biol.sc.edu  Faculty  Y Biology              12/05/2011 Y Wilma Sims
Matthew King kingm3@email.sc.edu Doctoral Y Library & Info. Sci. 12/15/2011 Y John Grego
Wenkuan Zhao zhaow@email.sc.edu  Faculty  Y Medical School       12/16/2011 Y Wilma Sims
;
run;

proc sql;
select * from lab2011;
quit;

*Slide 9;
proc sql; 
create table work.lab2012
like lab2011;
quit;

proc sql; 
describe table work.lab2012;
quit;

*Slide 10;
proc sql; create table sims2011 as 
select * from lab2011 where consultant='Wilma Sims';
quit;

*Slide 11;
proc sql;
create table work.lab2011 as 
select * from lab2011;
quit;

*Moving table from one library into the work library;
proc sql;
create table work.baseball as 
select * from sashelp.baseball;
quit;

*Slide 13;
* The set clauses may give you problems;
* Client names are too long for original format--we could use select * to change work.lab2012 first;
proc sql; 
insert into work.lab2012
set client='Jane Lipschitz', email='lipschitzj@dnr.sc.gov', degree='External', USC='N', Dept='SCDNR', date='25Jan2012'd, Gratis='Y', Consultant='John Grego' 
set client='Gerry Bainbridge', email='bainbrid@email.sc.edu', degree='Faculty', USC='Y', Dept='School of Music', date='31Jan2012'd, Gratis='Y', Consultant='John Grego'; 
select * from lab2012; 
quit;

*Fixing field widths;

data lab2012;
input client $12. @14 email $19. @34 degree $8. @43 usc $1. @45 dept $20. @66 date mmddyy10. 
      @77 gratis $1. @79 consultant $10.;
	  format date date9.;
datalines;
Joe Bridges  BridgesJ@dnr.sc.gov External N SCDNR                12/03/2011 N John Grego
Gina White   whiteg@biol.sc.edu  Faculty  Y Biology              12/05/2011 Y Wilma Sims
Matthew King kingm3@email.sc.edu Doctoral Y Library & Info. Sci. 12/15/2011 Y John Grego
Wenkuan Zhao zhaow@email.sc.edu  Faculty  Y Medical School       12/16/2011 Y Wilma Sims
;
run;

proc sql;
alter table work.lab2012 
 modify Client char(16) 
 modify Email char(21);
quit;


proc sql; 
insert into work.lab2012
set client='Jane Lipschitz', email='lipschitzj@dnr.sc.gov', degree='External', USC='N', Dept='SCDNR', date='25Jan2012'd, Gratis='Y', Consultant='John Grego' 
set client='Gerry Bainbridge', email='bainbrid@email.sc.edu', degree='Faculty', USC='Y', Dept='School of Music', date='31Jan2012'd, Gratis='Y', Consultant='John Grego'; 
select * from lab2012; 
quit;



*Slide 15;
proc sql; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'N', 'JERC', '17Jan2012'd, 'N', 'John Grego');
select * from work.lab2012; 
quit;

*Slide 16;
proc sql; 
insert into work.lab2012
(Client, Consultant, Degree, USC, Date, Dept, Gratis)
values('Erin Merrick', 'Wilma Sims', 'Masters', 'Y',  '07Jan2012'd,  'Environment', 'N');
select * from work.lab2012; 
quit;

*Slide 17;
proc sql; 
insert into work.lab2012
select * from lab2011 where month(date)=12;
select * from work.lab2012; quit;

************************************;

*Slide 19;
proc sql; create table work.lab2012
(Client char(12) primary key, Email char(21) not null, Degree char(8), USC char(1) check(USC in ('N' 'Y' 'y' 'n')), Dept char(20), Date num format=mmddyy10. check(date between '01Jan2012'd and '31Dec2012'd), Gratis char(1), Consultant char(10) check(Consultant in ('John Grego','Wilma Sims')));
quit;

*Slide 20;
proc sql;
describe table constraints work.lab2012;
quit;

*Slide 21;
*Wrong date;
proc sql; 
insert into work.lab2012
values('Erin Merrick','merrickj@email.sc.edu' ,'Masters', 'Y',  'Environment','07Jan2011'd,'Y','Wilma Sims');
quit;

*See constraint labels;
proc sql;
describe table constraints work.lab2012;
quit;

*Wrong name;
proc sql; 
insert into work.lab2012
values('Erin Merrick','merrickj@email.sc.edu' ,'Masters', 'Y',  'Environment','07Jan2012'd,'Y','Wilma Sim');
quit;

*email address is missing;
proc sql; 
insert into work.lab2012
(Client, Consultant, Degree, USC, Date, Dept, Gratis)
values('Erin Merrick', 'Wilma Sims', 'Masters', 'Y',  '07Jan2012'd,  'Environment', 'N');
quit;


*Good record;
proc sql; 
insert into work.lab2012
values('Erin Merrick','merrickj@email.sc.edu' ,'Masters', 'Y',  'Environment','07Jan2012'd,'Y','Wilma Sims');
select * from lab2012; quit;

*No is truncated--entry works;
proc sql; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'No', 'JERC', '17Jan2012'd, 'N', 'John Grego');
select * from lab2012; quit;

*Primary key violation;
proc sql; 
insert into work.lab2012
values('Erin Merrick', 'merrickj@email.sc.edu', 'Masters', 'Y', 'Environment', '01Feb2012'd, 'N', 'Wilma Sims') ;
quit; 

*Slide 22;
proc sql; create table work.lab2012
(Client char(12) primary key, Email char(21), Degree char(8), USC char(1), Dept char(20), Date num 
format=mmddyy10., Gratis char(1), Consultant char(10), constraint Check_USC check(USC in ('N' 'Y' 'y' 'n')) );
quit;

proc sql;
describe table constraints work.lab2012;
quit;

* A "composite key" constraint;
* this is a primary key constraint on more than one column;

proc sql; create table work.lab2012
(Client char(12), Email char(21), Constraint uniqueNmEm primary key (Client, Email), Degree char(8), USC char(1) check(USC in ('N' 'Y' 'y' 'n')), Dept char(20), Date num format=mmddyy10. check(date between '01Jan2012'd and '31Dec2012'd), Gratis char(1), Consultant char(10) check(Consultant in ('John Grego','Wilma Sims')));
quit;

*One insertion;
proc sql; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'N', 'JERC', '17Jan2012'd, 'N', 'John Grego');
select * from work.lab2012; 
quit;

*identical insertion fails;
proc sql; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'N', 'JERC', '17Jan2012'd, 'N', 'John Grego');
select * from work.lab2012; 
quit;

*insertion with same name but different email address succeeds;
proc sql; 
insert into work.lab2012
values('Scott Tyler', 'scott_t@coolmail.com', 'External', 'N', 'DHEC', '24Jan2012'd, 'N', 'John Grego');
select * from work.lab2012; 
quit;


*Slide 23;

*first insertion is good, second has a violation;
proc sql; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'N', 'JERC', '17Jan2012'd, 'N', 'John Grego')
values('Erin Merrick', 'merrickj@email.sc.edu', 'Masters', 'S', 'Environment', '01Feb2012'd, 'N', 'Wilma Sims') ;
quit; 

* use UNDO_POLICY=NONE to allow the earlier successful insertions to be retained;

proc sql UNDO_POLICY=NONE; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'N', 'JERC', '17Jan2012'd, 'N', 'John Grego')
values('Erin Merrick', 'merrickj@email.sc.edu', 'Masters', 'S', 'Environment', '01Feb2012'd, 'N', 'Wilma Sims') ;
quit;
* Need a separate PROC SQL step to print the result because of the forced NOEXEC due to the violation in the PROC SQL step above;
proc sql;
select * from lab2012;
quit;

*Slide 26;

data lab2011pay;
input client $12. @14 Hrs 2.0 @17 Rate comma3.0 @21 Consultant $8.;
datalines;
SCDDT         5 $50 Grad 
SCDDT        10 $80 Director
LCHS         15 $50 Manager
CK LLC        1 $80 Director
;
run;

proc sql; create table lab2012pay like
lab2011pay;
quit;

proc sql;
insert into lab2012pay
values('SCDFE',40,50,'Grad')
values('SCDFE',10,50,'Manager')
values('CTN',3.5,80,'Director');
quit;

proc sql;
update lab2012pay
set rate=rate*1.05;
select * from lab2012pay; quit;

*Reset lab2012pay table;
proc sql; create table lab2012pay like
lab2011pay;
quit;
proc sql;
insert into lab2012pay
values('SCDFE',40,50,'Grad')
values('SCDFE',10,50,'Manager')
values('CTN',3.5,80,'Director');
quit;

proc sql;
update lab2012pay
set rate=rate*1.0013 where consultant='Director';
select * from lab2012pay; quit;



*Slide 28;

*Reset lab2012pay table;
proc sql; create table lab2012pay like
lab2011pay;
quit;
proc sql;
insert into lab2012pay
values('SCDFE',40,50,'Grad')
values('SCDFE',10,50,'Manager')
values('CTN',3.5,80,'Director');
quit;

proc sql;
update lab2012pay
set rate=rate*case
when consultant='Director' then 1.05
when consultant='Manager' then 1.0375
else 1.045
end;
select * from lab2012pay; quit;



*Slide 29;

*Reset lab2012pay table;
proc sql; create table lab2012pay like
lab2011pay;
quit;
proc sql;
insert into lab2012pay
values('SCDFE',40,50,'Grad')
values('SCDFE',10,50,'Manager')
values('CTN',3.5,80,'Director');
quit;

proc sql;
update lab2012pay
set rate=rate*case consultant
when 'Director' then 1.05
when 'Manager' then 1.0375
else 1.045
end;
select * from lab2012pay; quit;


*Slide 30;
data lab2011;
input client $12. @14 email $19. @34 degree $8. @43 usc $1. @45 dept $20. @66 date mmddyy10. 
      @77 gratis $1. @79 consultant $10.;
	  format date date9.;
datalines;
Joe Bridges  BridgesJ@dnr.sc.gov External N SCDNR                12/03/2011 N John Grego
Gina White   whiteg@biol.sc.edu  Faculty  Y Biology              12/05/2011 Y Wilma Sims
Matthew King kingm3@email.sc.edu Doctoral Y Library & Info. Sci. 12/15/2011 Y John Grego
Wenkuan Zhao zhaow@email.sc.edu  Faculty  Y Medical School       12/16/2011 Y Wilma Sims
;
run;

proc sql; 
create table work.lab2012
like lab2011;
quit;

proc sql;
alter table work.lab2012 
 modify Client char(16) 
 modify Email char(21);
quit;

proc sql; 
insert into work.lab2012
set client='Jane Lipschitz', email='lipschitzj@dnr.sc.gov', degree='External', USC='N', Dept='SCDNR', 
date='25Jan2012'd, Gratis='Y', Consultant='John Grego' 
set client='Gerry Bainbridge', email='bainbrid@email.sc.edu', degree='Faculty', USC='Y', Dept='School of 
Music', date='31Jan2012'd, Gratis='Y', Consultant='John Grego'; 
select * from lab2012; 
quit;

proc sql; 
insert into work.lab2012
values('Scott Tyler', 'tylers@jonesctr.org', 'External', 'N', 'JERC', '17Jan2012'd, 'N', 'John Grego');
select * from work.lab2012; 
quit;

proc sql; 
insert into work.lab2012
(Client, Consultant, Degree, USC, Date, Dept, Gratis)
values('Erin Merrick', 'Wilma Sims', 'Masters', 'Y',  '07Jan2012'd,  'Environment', 'N');
select * from work.lab2012; 
quit;

proc sql; 
insert into work.lab2012
select * from lab2011 where month(date)=12;
select * from work.lab2012; quit;

proc sql;
delete from lab2012
where date lt '01Jan2012'd; 
select * from work.lab2012; 
quit;

*Slide 32;
proc sql;
alter table lab2012
add College char(20) label='College or School', time num format=hour4.1
modify date format=weekdate31.;
select * from lab2012;
quit;

proc sql;
update lab2012
set College='Music', time='9:30't
where Client='Gerry Bainbridge';
select * from lab2012;
quit;

proc sql;
update lab2012
set College='Music', time='9:30 pm't
where Client='Gerry Bainbridge';
select * from lab2012;
quit;

*Slide 33;
proc sql;
drop table lab2011;
quit;