*Slide 2;
*Download Bacterial Water Quality and save as wq;
libname stat541 '/home/grego1/STAT 541'; run;
proc import out=work.wq
datafile="/home/grego1/STAT 541/EColi.xlsx"
dbms=XLSX replace;
run;

/* proc sql; create table wq as select * from stat541.Ecoli; quit; */

proc sql;
create table ecoli as select station, collection_date, EColi from wq;
quit;

proc sql;
create table fcoli as select station, collection_date, fecalcoli from wq;
quit;
proc sql;
create table entero as select station, collection_date, enterococci from wq;
quit;

proc sql inobs=5;
select * from Ecoli
outer union corr select * from Fcoli
outer union corr select * from Entero;
quit;

*Slide 4;
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 outobs=5;
 select player, atbats
 from bbstats;
quit;

*Slide 5 through Slide 10 largely work only on LISTING output;
*Slide 5;
proc sql outobs=5 double;
select player, atbats
from bbstats;
quit;

*Slide 6;
proc sql outobs= double number;
select player, atbats
from bbstats;
quit;

proc sql outobs=5 double nonumber;
select player, atbats
from bbstats;
quit;

*Slide 8;
data secscores;
input date mmddyy11. @12 wteam $17. @30 wscore lteam $17.  lscore;
format date worddate18.;
datalines;
11/19/2011 Alabama           45 Georgia Southern  21
11/19/2011 Arkansas          44 Mississippi State 17
11/19/2011 LSU               52 Ole Miss           3
11/19/2011 Tennessee         27 Vanderbilt        21
11/12/2011 South Carolina    17 Florida           12
11/12/2011 Vanderbilt        38 Kentucky           8
11/12/2011 Georgia           45 Auburn             7
11/12/2011 Arkansas          49 Tennessee          7
11/12/2011 LSU               42 Western Kentucky   9
11/12/2011 Louisiana Tech    27 Ole Miss           7
11/5/2011  Florida           26 Vanderbilt        21
11/5/2011  Georgia           63 New Mexico State  16
11/5/2011  Kentucky          30 Ole Miss          13
11/5/2011  Tennessee         24 Middle Tennessee   0
11/5/2011  Arkansas          44 South Carolina    28
11/5/2011  Mississippi State 55 Tennessee-Martin  17
11/5/2011  LSU                9 Alabama            6
10/29/2011 Arkansas          31 Vanderbilt        28
10/29/2011 Georgia           24 Florida           20
10/29/2011 Auburn            41 Ole Miss          23
10/29/2011 Mississippi State 28 Kentucky          16
10/29/2011 South Carolina    14 Tennessee          3
;
run;

proc sql;
title "Winning and Losing Teams with 2011 Matching Scores";
select winner.wscore label="Matching Score", winner.date format=worddate18. label="Date", winner.wteam label="Team with Winning Score", loser.date format=worddate18. label="Date", loser.lteam	label="Team with Losing Score"
from secscores as winner, secscores as loser
where winner.wscore=loser.lscore;
quit;

*Cuts off Mississippi;
proc sql flow=10;
title "Winning and Losing Teams with 2011 Matching Scores";
select winner.wscore label="Matching Score", winner.date format=worddate18. label="Date", winner.wteam label="Team with Winning Score", loser.date format=worddate18. label="Date", loser.lteam label="Team with Losing Score"
from secscores as winner, secscores as loser
where winner.wscore=loser.lscore;
quit;

proc sql flow=10 15 double;
title "Winning and Losing Teams with 2011 Matching Scores";
select winner.wscore label="Matching Score", winner.date format=worddate18. label="Date", winner.wteam label="Team with Winning Score", loser.date format=worddate18. label="Date", loser.lteam label="Team with Losing Score"
from secscores as winner, secscores as loser
where winner.wscore=loser.lscore;
quit;

*Slide 10;
proc sql outobs=6 double;
select player, atbats
from bbstats;
reset outobs= number nodouble;
select player, hits
from bbstats;
quit;

*Slide 11;
*Import Medicare database from webpage--name it meddb;

PROC IMPORT OUT= stat541.MedDB 
            DATAFILE= "/home/grego1/my_content/STAT541/MedDB.txt" 
            DBMS=DLM REPLACE;
     DELIMITER='09'x; 
     GETNAMES=YES;
     DATAROW=2; 
RUN;

proc sql;
create table meddb as select * from stat541.Meddb;
quit;

proc sql;
create table meddbX as select * from stat541.Meddb;
quit;

*Table sorted on provider;
proc sql;
create table meddbprov as select ssn, tos, dos, provider as provsort, claim from Meddbx order by provsort;
quit;

*indexes;

proc sql;
create index provider
on meddb(provider);
quit;

proc sql;
create index provsort
on meddbprov(provsort);
quit;

*STIMER--only one PROC SQL, but CPU time and Real time are disaggregated;
options msglevel=I;
proc sql stimer;
create table prov1 as select * from meddbx where provider between 200 and 420 order by provider;
create table prov2 as select * from meddb where provider between 60 and 280 order by provider;
create table prov3 as select * from meddb (idxwhere=yes) where provider between 120 and 340 order by provider;
create table prov4 as select * from meddbprov where provsort between 110 and 330 order by provsort;
quit;

proc sql;
drop table prov1, prov2, prov3, prov4;
quit;

*Slide 13;
*Create a view;
data ce1;
input ssn $11. code degree $9. Grade;
datalines;
123-11-5526 135 Bachelors 9
206-67-1454 135 Bachelors 10
323-50-2746 126 Masters   9
367-47-9580 126 Bachelors 9
564-94-6508 126 Bachelors 9
782-57-8284 135 Masters   10
354-83-9603 126 Masters   10
590-71-6170 135 Bachelors 8
060-76-2046 126 Bachelors 9
983-13-6455 135 Masters   9
638-03-0350 135 Bachelors 9
861-59-4706 126 Bachelors 9
;
run;

data hw1;
input ssn $11. code degree $9. Grade;
datalines;
367-47-9580 126 Bachelors 19
564-94-6508 126 Bachelors 19
060-76-2046 126 Bachelors 17
123-11-5526 135 Bachelors 20
206-67-1454 135 Bachelors 19
638-03-0350 135 Bachelors 19
861-59-4706 126 Bachelors 20
983-13-6455 135 Masters   19
782-57-8284 135 Masters   19
354-83-9603 126 Masters   19
590-71-6170 135 Bachelors 18
;
run;

proc sql;
create view Major_Comp as  select code, avg(ce1) as ce1avg label="CE 1" format=4.1, avg(hw1) as hw1avg label="HW 1" format=5.1 from 
(select ce1.code label="Major Code",ce1.degree label="Degree",ce1.grade as ce1 label="Computer Exercise 1", 
hw1.grade as hw1 label="Homework 1" 
from ce1 left join hw1 on ce1.ssn=hw1.ssn)
group by code;
quit;

proc sql;
describe table dictionary.tables;
quit;

proc sql;
select memname, memtype, nobs, nvar,num_character, num_numeric, filesize, crdate
from dictionary.tables
where libname='WORK';
quit;

proc sql;
select memname, memtype, nobs, nvar,num_character, num_numeric, filesize, crdate
from dictionary.tables
where libname='STAT541';
quit;

*This one may take a while to run;
proc sql;
select libname, memname, memtype, nobs, nvar,num_character, num_numeric, filesize, crdate
from dictionary.tables;
quit;