*** Chapter 1;

data bbstats;
label atbats="At Bats";
input Player $19. atbats Hits BB;
datalines;
Christian Walker     271 97 36
Scott Wingo          240 81 44
Brady Thomas         231 73 23
Evan Marzilli        220 64 25
Robert Beary         211 61 12
Adrian Morales       249 70 30
Peter Mooney         254 71 44
Jake Williams        209 56 21
Jackie Bradley Jr.   162 40 22
;
run;

proc sql;
create table superstars AS
select *
from bbstats
where Hits GE 70 AND BB GE 25;
proc sql;
select *
from superstars
quit;


*** Chapter 2;

* Example with dates;

proc sql;
select date, units
from sashelp.countseries
where date between '25mar2005'd and '27sep2007'd;
quit;


*Slide 10;
data bbstats2;
label atbats="At Bats";
input Player $19. atbats Hits BB;
datalines;
Christian Walker     271 97 36
Scott Wingo          240 81 44
Brady Thomas         231 73 23
Evan Marzilli        220 64 25
Robert Beary         211 61 12
Adrian Morales       249 70 30
Peter Mooney         254 71 44
Jake Williams        209 56 21
Jackie Bradley Jr.   162 40 22
Scott Wingo          240 81 44
Ian Smithson         199 61 33
;
run;

proc sql;
 select player, atbats from bbstats2
 where player contains "ia"; 
quit;


proc sql;
 select player, atbats from bbstats2
 where upcase(player) contains "IA"; 
quit;

* The 'in' operator;

proc sql;
 select make, model, type, MSRP
 from sashelp.cars
 where origin in ('Asia', 'Europe');
quit;

proc sql;
 select make, model, type, MSRP
 from sashelp.cars
 where origin not in ('USA');
quit;

* The 'is missing' operator;
Proc sql;
 select sex, smoking_status
 from sashelp.heart
 where cholesterol is missing;
quit;

* The 'like' operator for string searches;

Proc sql;
 select name
 from sashelp.baseball
 where name like 'C%';
quit;

Proc sql;
 select name
 from sashelp.baseball
 where name like 'B_n%';
quit;

* the sounds-like operator;
Proc sql;
 select make, model
 from sashelp.cars
 where make =* 'outie';
quit;

Proc sql;
 select make, model
 from sashelp.cars
 where make =* 'audio';
quit;

* Averages with monetary amounts;

proc sql;
select product, avg(sales) as avgsales format=dollar11.2
from sashelp.shoes
GROUP BY Product;
quit;

* Counts with missing data;

Proc sql;
 select count(*) as rowcount
 from sashelp.heart;
quit;

Proc sql;
 select count(cholesterol) as Cholcount
 from sashelp.heart;
quit;

Proc sql;
 select count(distinct smoking_status) as smokelevels
 from sashelp.heart;
quit;