/* Problem 1 */

* Part (a); 

proc sql;
create table work.baseball2 as
select name, team, league, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position, salary
from sashelp.baseball;
quit;


*Part (b); 

proc sql;
insert into work.baseball2
(name, team, league, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position)
values('Ruth, Babe', 'New York', 'American', 22, 8399, 2873, 714, 2174, 2214, 2062, 'LF');
insert into work.baseball2
(name, team, league, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position)
values('Clemente, Roberto', 'Pittsburgh', 'National', 18, 9454, 3000, 240, 1416, 1305, 621, 'RF');
select * from work.baseball2;
quit;

*Part (c); 

* Answers will vary;

* One possible approach to creating integrity constraints;

proc sql;
create table baseball2_constr 
(name char(18) primary key, team char(14), league char(8) check(league in ('National' 'American')), YrMajor num not null, CrAtBat num, CrHits num, CrHome num, CrRuns num, CrRbi num, CrBB num, position char(8), salary num);
quit;

* Trying a few insertions that fail;
proc sql;
insert into work.baseball2_constr
(name, team, league, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position)
values('Hitchcock, David', 'Atlanta', 'National', 22, 3333, 1111, 212, 1555, 1344, 543, 'RF');
quit;

proc sql;
insert into work.baseball2_constr
(name, team, league, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position)
values('Hitchcock, David', 'Baltimore', 'American', 22, 3333, 1111, 212, 1555, 1344, 543, 'RF');
* fails primary key constraint;
quit;

proc sql;
insert into work.baseball2_constr
(name, team, league, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position)
values('Hitchcock, Matthew', 'Baltimore', 'Eastern', 22, 3333, 1111, 212, 1555, 1344, 543, 'RF');
* fails League check constraint;
quit;

proc sql;
insert into work.baseball2_constr
(name, team, league, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB, position)
values('Hitchcock, Brendan', 'Baltimore', 'American', 3333, 1111, 212, 1555, 1344, 543, 'RF');
* fails YrMajor not null constraint;
quit;



* Part (d);

proc sql;
alter table work.baseball2
add modsal num label="Modern Salary Equivalent in Thousands" format=dollar8.;
quit;

proc sql;
update work.baseball2
set modsal = case 
when position='1B' or position='2B' or position='SS' or position='3B' then salary*6
when position='C' then salary*5
when position='LF' or position='CF' or position='RF' or position='DH' then salary*8 
else salary*1
end;
select * from work.baseball2;
quit;



/* Problem 2 */

DATA CTL2014;
INFILE CARDS DLM='09'X;
INPUT Name :$24. Gender $ City :$10. State $ Rating $ RatingDate MMDDYY10. RatingType $;
CARDS;
Anderson, Stephen R.	M	Blythewood	SC	4.0	12/31/2015	C
Bigg, Dave 	M	Lexington	SC	4.0	12/31/2015	C
Eckstorm, Andy 	M	Columbia	SC	3.5	12/31/2015	C
Fernandez, Michael 	M	Columbia	SC	3.0	12/31/2014	C
Feuerstein, Eric 	M	Blythewood	SC	4.0	12/31/2015	C
Godish, Donald L.	M	Columbia	SC	3.0	12/31/2015	C
Graybill, Wesley M.	M	Columbia	SC	3.5	12/31/2015	C
Heath, Jeff 	M	Blythewood	SC	3.5	12/31/2015	C
Imholz, Mark 	M	Blythewood	SC	4.0	12/31/2015	C
Kuhn, Stanley 	M	Blythewood	SC	4.0	12/31/2015	C
Lowery, Mark A.	M	Columbia	SC	3.0	12/31/2015	C
Mantai, Michael 	M	Lexington	SC	4.0	12/31/2015	C
May, John 	M	Columbia	SC	4.5	12/31/2015	C
May, Kenneth Ronald	M	Blythewood	SC	3.0	12/31/2015	C
McCormick, Ryan S	M	Blythewood	SC	3.5	12/31/2015	C
Miller, Charles 	M	Columbia	SC	3.0	12/31/2015	C
Nelson, Derek 	M	Lexington	SC	4.5	12/31/2014	C
Phillips, Mike 	M	Blythewood	SC	4.0	12/31/2015	C
Rinehart, Rene P.	M	FPO	AP	4.0	12/31/2015	C
Siau, Christopher T	M	Columbia	SC	3.5	12/31/2015	C
Stubblefield, Harry 	M	Lexington	SC	3.0	12/31/2015	C
Tyson, Craig 	M	Blythewood	SC	4.0	12/31/2015	C
van der Schyff, Petrus 	M	West Columbia	SC	3.5	12/31/2015	C
Young, Glenn W	M	Blythewood	SC	4.0	12/31/2015	C
Aull, Taylor 	M	Columbia	SC	4.5	12/31/2014	C
Collins, David 	M	Columbia	SC	4.5	12/31/2015	C
Dieter, Tuck 	M	Pawleys Island	SC	4.5	12/31/2014	C
Doty, Logan 	M	Columbia	SC	4.0	12/31/2015	C
Houston, Donovan A.	M	Elgin	SC	4.0	12/31/2015	C
Keenan, Palmer 	M	Columbia	SC	4.0	12/31/2015	C
May, John 	M	Columbia	SC	4.5	12/31/2015	C
Oamil, Billy Lyle 	M	Lexington	SC	4.0	12/31/2015	C
Price, Russell D.	M	Winnsboro	SC	4.0	12/31/2014	C
Rowe, Nicholas 	M	Columbia	SC	4.5	12/31/2015	C
Royce, William 	M	Columbia	SC	4.0	12/31/2015	C
Slapnik, Michael 	M	Lexington	SC	4.0	12/31/2015	C
Tiller, Cauthen 	M	Camden	SC	4.5	12/31/2015	C
Utheim, Keith 	M	Cayce	SC	4.0	12/31/2015	C
;
run;

DATA CTL2015;
INFILE CARDS DLM='09'X;
INPUT Name :$24. Gender $ City :$10. State $ Rating $ RatingDate MMDDYY10. RatingType $;
CARDS;
Anderson, Stephen R.	M	Blythewood	SC	4.0	12/31/2015	C
Bidwell, Stephen E.	M	Columbia	SC	4.5	12/31/2015	C
Camps, Juan 	M	Columbia	SC	4.0	12/31/2015	C
Feuerstein, Eric 	M	Blythewood	SC	4.0	12/31/2015	C
Hitchcock, David 	M	Columbia	SC	4.0	12/31/2015	C
Houston, James Philip	M	Elgin	SC	4.0	12/31/2015	C
Imholz, Mark 	M	Blythewood	SC	4.0	12/31/2015	C
Junis, Steven 	M	Columbia	SC	4.0	12/31/2015	C
Kuhn, Stanley 	M	Blythewood	SC	4.0	12/31/2015	C
Mantai, Michael 	M	Lexington	SC	4.0	12/31/2015	C
May, John 	M	Columbia	SC	4.5	12/31/2015	C
Mobley, John T.	M	Columbia	SC	4.0	12/31/2015	C
Nguyen, Khue D.	M	Columbia	SC	4.0	12/31/2015	C
Tanner, Mike D	M	Blythewood	SC	4.0	12/31/2015	C
Truitt, David Newton	M	Columbia	SC	4.0	12/31/2015	C
Tyson, Craig 	M	Blythewood	SC	4.0	12/31/2015	C
Young, Glenn W	M	Blythewood	SC	4.0	12/31/2015	C
Atim, Alexandru Gabriel	M	Columbia	SC	3.0	12/28/2015	S
Banks, Tymir Rabeen	M	Columbia	SC	3.5	12/31/2015	C
Barker, Michael Craig	M	Blythewood	SC	3.5	12/31/2015	C
Best, Curry 	M	Columbia	SC	3.0	12/31/2015	C
Bower, Andrew 	M	Columbia	SC	3.5	12/31/2015	C
Bramblett, Justin 	M	Columbia	SC	3.5	12/31/2015	C
Burchstead, Michael 	M	Columbia	SC	3.5	12/31/2015	C
Chesoni, Solomon N.	M	Blythewood	SC	3.5	12/31/2015	C
Daylami, Taylor Francis	M	Columbia	SC	3.0	05/01/2015	S
DeBorde, Cameron Phillip	M	Columbia	SC	2.5	05/18/2015	S
Eckstorm, Andy 	M	Columbia	SC	3.5	12/31/2015	C
Estes, Joey 	M	Columbia	SC	3.5	12/31/2015	C
Failor, Kellen 	M	Columbia	SC	2.5	06/09/2015	S
Gillan, Dennis 	M	Blythewood	SC	3.5	12/31/2015	C
Godish, Donald L.	M	Columbia	SC	3.0	12/31/2015	C
Graybill, Wesley M.	M	Columbia	SC	3.5	12/31/2015	C
Hedgepath, Andrew 	M	Columbia	SC	3.0	12/31/2015	C
Henry, Kenneth 	M	Blythewood	SC	3.5	12/31/2015	A
Holland, Sam T	M	Columbia	SC	3.5	12/31/2015	C
Lambe, Scott 	M	Columbia	SC	3.0	12/31/2015	C
Levinson, Lucky 	M	Columbia	SC	3.5	12/31/2015	C
LHOSTE-CLOS, MATHIEU 	M	Columbia	SC	3.0	04/30/2015	S
Lowery, Mark A.	M	Columbia	SC	3.0	12/31/2015	C
Makris, Thomas Michael	M	Columbia	SC	3.5	12/31/2015	C
McCormick, Ryan S	M	Blythewood	SC	3.5	12/31/2015	C
Miller, Charles 	M	Columbia	SC	3.0	12/31/2015	C
OMalley, Tim 	M	Columbia	SC	3.5	12/31/2015	C
Pleasant, Mark Durant	M	Columbia	SC	3.0	12/31/2015	C
Powell, Steven 	M	Columbia	SC	3.0	12/31/2015	C
Siau, Christopher T	M	Columbia	SC	3.5	12/31/2015	C
Spong, Trey 	M	Winnsboro	SC	3.5	12/31/2015	C
Stubblefield, Harry 	M	Lexington	SC	3.0	12/31/2015	C
Terry, David 	M	Lexington	SC	3.5	12/31/2015	C
volz, thomas elliott	M	Columbia	SC	3.5	12/31/2015	C
Waite, John J	M	Columbia	SC	3.0	12/31/2015	C
Woodcock, Ron J	M	Columbia	SC	3.5	12/31/2015	C
;
run;

* part (a);

proc sql;
select name, rating, ratingdate format=MMDDYY10.
from ctl2014
intersect
select name, rating, ratingdate format=MMDDYY10.
from ctl2015
quit;

* part (b);

proc sql;
select name, rating, ratingdate format=MMDDYY10.
from ctl2014
intersect
select name, rating, ratingdate format=MMDDYY10.
from ctl2015
order by rating desc;
quit;

* part (c);

proc sql;
select name, rating, ratingdate format=MMDDYY10.
from ctl2015
except
select name, rating, ratingdate format=MMDDYY10.
from ctl2014
quit;

* part (d);

proc sql;
select name, rating, ratingdate format=MMDDYY10.
from ctl2014
union
select name, rating, ratingdate format=MMDDYY10.
from ctl2015
quit;

* part (e);

proc sql;
select name, rating, ratingdate format=MMDDYY10.
from ctl2014
union all
select name, rating, ratingdate format=MMDDYY10.
from ctl2015
quit;