** Chapter 3 Extra examples;

* More complicated WHERE condition;

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

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

PROC SQL;
select a.name, quiz, test, 10*quiz as adjquiz
from a, b2
where a.name=b2.name and calculated adjquiz<b2.test;
quit;


* In-Line View that references multiple tables;

PROC SQL;
select a.name, overavg  
label="Overall Course Average" format=4.2

from (select a.name,  (0.30*(10*quiz) + 0.70*test) as overavg from a, b2)
where a.name=b2.name;
quit;


* Chapter 4;

*intersect all;


data google;
input name $ residence $16.;
datalines;
John Columbia, SC
Laura Cambridge, MA
Melissa Los Angeles, CA
Michael Orlando, FL
Joseph London, England
Melissa Los Angeles, CA
Mark Napierville, IL
John Washington, DC
;
run;


data bing2;
input name $ city $17.;
datalines;
Mark Napierville, IL
Joseph London, England
Michael Orlando, FL
Melissa Los Angeles, CA
VG Yerevan, Armenia
Melissa Los Angeles, CA
;
run;

proc sql;
select * from google
intersect
select * from bing2; 
quit;


proc sql;
select * from google
intersect all
select * from bing2; 
quit;

proc sql;
select * from google
intersect all corr
select * from bing2; 
quit;