data a;
input name $ quiz;
datalines;
Brad 7
Amy 9
Li 9
;
run;

data b;
input name $ test;
datalines;
Amy 87
Li 86
Sean 54
Sophie 92
;
run;

*Slide 3;
proc sql;
select * from a,b; quit;

*Slide 7;
proc sql;
select a.name, quiz, test from a, b
where a.name=b.name;
quit;

proc sql;
select a.name, quiz, test from a inner join b
on a.name=b.name;
quit;

*Slide 9;
proc sql;
select a.name as quizname, b.name as testname, quiz, test from a, b
where quizname=testname;
quit;

*Many-to-many example;

data a1;
input name $ quiz;
datalines;
Brad 7
Amy 9
Amy 10
Li 9
;
run;

data b1;
input name $ test;
datalines;
Amy 87
Amy 99
Li 86
Sean 54
Sophie 92
;
run;

proc sql;
select a1.name, quiz, test from a1 inner join b1
on a1.name=b1.name;
quit;


*Slide 11;
data hospitnew;
input patient date date9. pulse temp bps lastname $10.;
datalines;
2004101 03NOV2005 73 98.3 88 Jones
2004101 10NOV2005 77 98.5 82 Jones
2004101 17NOV2005 75 98.2 85 Jones
2004102 03NOV2005 83 98.0 98 Montgomery
2004102 10NOV2005 81 98.5 94 Montgomery
2004103 27OCT2005 77 99.3 78 Thomas
2004103 03NOV2005 76 98.5 79 Thomas
2004103 17NOV2005 79 99.2 75 Thomas
2004104 10NOV2005 72 98.9 83 Darhouse
;
run;

proc sql;
select * from hospitnew;
quit;

data dosing;
input patient date date9. @19 med $5. doses amt unit $2.;
datalines;
2004102 03NOV2005 Med A 3 1.4 mg
2004102 10NOV2005 Med A 2 2.4 mg
2004103 02NOV2005 Med B 2 2.5 mg
2004103 09NOV2005 Med B 1 3.1 mg
2004103 16NOV2005 Med B 3 2.8 mg
2004104 10NOV2005 Med A 3 3.6 mg
2004105 01NOV2005 Med B 2 2.5 mg
2004105 08NOV2005 Med B 2 1.9 mg
2004105 15NOV2005 Med B 1 3.7 mg
;
run;

proc sql;
select * from dosing;
quit;

proc sql;
create table both as
select a.patient, a.date format date7. as date, a.pulse, 
      b.med, b.doses, b.amt format=4.1
from hospitnew a inner join dosing b
on (a.patient=b.patient) and (a.date=b.date)
order by patient,date;

select * from both;
quit;

*Slide 12;

*group by date;

proc sql;
create table both as
select a.date format date7. as date, avg(a.pulse) label="Average Daily Pulse" as avgPulse, 
     count(b.patient) label="No. of Patients", sum(b.doses) label="Total Daily Doses" as NumDose, sum(b.amt) format=4.1 label="Total Amount (mg)" as Totamt 
from hospitnew a inner join dosing b
on (a.patient=b.patient) and (a.date=b.date)
group by a.date
order by a.date;

select * from both;
quit;

*group by patient;

proc sql;
create table both as
select a.patient as patient, avg(a.pulse) label="Average Patient Pulse" as avgPulse, 
     count(b.date) label="No. of Visits", sum(b.doses) label="Total Patient Doses" as NumDose, sum(b.amt) format=4.1 label="Total Patient Amount (mg)" as Totamt 
from hospitnew a inner join dosing b
on (a.patient=b.patient) and (a.date=b.date)
group by a.patient
order by a.patient;

select * from both;
quit;

* Example related to Slide 13;
* Natural join;

DATA math;
INPUT firstname :$15. lastname :$15. score;
datalines;
Kaitlin Jackson 78
Kaitlin Simmons 45
Emma Jackson 89
Emma Smith 79
Roger Smith 67
Pete Rosen 98
;
run;
DATA science;
INPUT firstname :$15. lastname :$15. grade $;
datalines;
Kaitlin Jackson C
Kaitlin Simmons C+
Kaitlin Smith A
Emma Jackson A
Emma Thomas B
Emma Smith A
Roger Smith D+
Pete Rosen B+
Thomas Smith B
;
run;

PROC SQL;
SELECT * 
FROM math NATURAL JOIN science;
quit;

*Be careful with natural joins;
*What would have happened had the third column in each of these tables been named "grade"?;

*Slide 16;
proc sql;
select a.name, quiz, test from a 
left join b
on a.name=b.name;
quit;

*Slide 17;
proc sql;
select b.name, quiz, test from a 
right join b on a.name=b.name;
quit;

*Slide 18;
proc sql;
select * from a full join b
on a.name=b.name;
quit;

*Slide 20;
data a;
input Name $ Major $18.;
datalines;
Shan Statistics
Iris Biostatistics
Tim Actuarial Sciences
;
run;

data b;
input Name $ School $31.;
datalines;
Iris University of Missouri
Tim University of New Mexico
Shan North Carolina State University
;
run;

proc sort data=a; by name; run;
proc sort data=b; by name; run;

data c; 
merge a b; 
by name; 
run;

proc print data=c;
run;

proc sql; 
select a.name, major, school from a, b 
where a.name=b.name 
order by name; 
quit;

*Slide 22;
data b;
input Name $ School $;
datalines;
Iris Mizzou
Tim UNM
Josh KSU
;
run;

proc sort data=b; by name; run;

data c; 
merge a b; 
by name; 
run;

proc print data=c;
run;

proc sql; 
select a.name, major, school from a full join b 
on a.name=b.name order by name;
quit;

* Trying selecting all columns;
proc sql; 
select * from a full join b 
on a.name=b.name order by name;
quit;

*Slide 24;
proc sql;
title "Table Merged";
select coalesce(a.name, b.name) as name, major, school
from a full join b
on a.name=b.name;
quit;
title;



*Slide 29;
data lib;
input LibSys $12. State $ TotCirc LocGvt;
datalines;
Haleyville   AL 67031 12822
Jasper       AL 187072 74289
Suniton      AL 39401 12026
Ashland City AL 60994 21350
Athens       IL 27366 22976
Freeburg     IL 218749 26519
Pembroke     IL 19200 526
Heermance    NY 160316 48199
Greenville   NY 131019 60863
Haines Falls NY 38734 11471
;
proc sql;
select state, avg(LocGvt) as average, sum(TotCirc>150000) 
as large, sum(TotCirc<150000) 
as small from lib group by state;
quit;

proc sql;
select state, average  
label="Mean Local Government Support" format=dollar12.2, small/(small+large) as prop format=percent5.2 label= "Small Library Percentage" 
from (select state, avg(LocGvt) as average, sum(TotCirc>150000) 
as large, sum(TotCirc<150000) 
as small from lib group by state)
order by average;
quit;