/* Note:  These example solutions do not contain comments, but      */
/*        YOUR solutions should contain LOTS of detailed comments!  */

* Problem 1(a);

PROC SQL;
 select team, sum(nruns) as totruns
 from sashelp.baseball
 where position="C" or position="2B" or position="SS" or position="CF"
 group by team
 order by totruns desc;
quit;

* Problem 1(b);

PROC SQL;
 select team, sum(nhome) as tothmrs
 from sashelp.baseball
 where position="RF" or position="LF" or position="CF"
 group by team
 order by tothmrs desc;
quit;

* Problem 1(c);

PROC SQL;
 select name, team, YrMajor, CrAtBat, CrHits, CrHome, CrRuns, CrRbi, CrBB
 from sashelp.baseball
 where YrMajor GE 12 and crhits GE 2000 and crruns GE 1000 and crrbi GE 1000;
quit;

* Problem 1(d);

PROC SQL;
 select name, team, crhits/cratbat as crbatavg label="Career Average" format=4.3
 from sashelp.baseball
 order by crbatavg desc;
quit;

* Problem 2(a);

DATA satscore;
INPUT state :$10. sat1990 sat2000;
datalines;
Alabama 1079 1114
Alaska 1015 1034
Arizona 1041 1044
Arkansas 1077 1117
California 1002 1015
Colorado 1067 1071
Delaware 1006 998
Florida 988 998
Georgia 951 974
Guam 975 997
Hawaii 985 1007
Idaho 1066 1081
Illinois 1089 1154
Indiana 972 999
Iowa 1172 1189
Kansas 1129 1154
Kentucky 1089 1098
Louisiana 1088 1120
Maine 991 1004
Maryland 1008 1016
Michigan 1063 1126
Minnesota 1110 1175
Missouri 1089 1149
Montana 1082 1089
Nebraska 1121 1131
Nevada 1022 1027
Ohio 1048 1072
Oklahoma 1095 1123
Oregon 1024 1054
Tennessee 1102 1116
Texas 979 993
Utah 1121 1139
Vermont 1000 1021
Virginia 997 1009
Washington 1024 1054
Wisconsin 1111 1181
Wyoming 1072 1090
;
run;

PROC SQL;
select satscore.state label="State Name", sat1990 label="SAT Scores for 1990", sat2000 label="SAT Scores for 2000", population_1990 label="Population for 1990", population_2000 label="Population for 2000", region label = "Region of U.S."
from satscore inner join sashelp.us_data
on satscore.state=statename;
quit;

* Problem 2(b);

PROC SQL;
select satscore.state label="State Name", sat1990 label="SAT Scores for 1990", sat2000 label="SAT Scores for 2000", population_1990 label="Population for 1990", population_2000 label="Population for 2000", region label = "Region of U.S."
from satscore left join sashelp.us_data
on satscore.state=statename;
quit;

PROC SQL;
select coalesce(satscore.state,statename) as staten label="State Name", sat1990 label="SAT Scores for 1990", sat2000 label="SAT Scores for 2000", population_1990 label="Population for 1990", population_2000 label="Population for 2000", region label = "Region of U.S."
from satscore right join sashelp.us_data
on satscore.state=statename;
quit;

PROC SQL;
select coalesce(satscore.state,statename) as staten label="State Name", sat1990 label="SAT Scores for 1990", sat2000 label="SAT Scores for 2000", population_1990 label="Population for 1990", population_2000 label="Population for 2000", region label = "Region of U.S."
from satscore full join sashelp.us_data
on satscore.state=statename;
quit;

* Problem 2(c);

PROC SQL;
select satscore.state label="State Name", sat1990 label="SAT Scores for 1990", sat2000 label="SAT Scores for 2000", population_1990 label="Population for 1990", population_2000 label="Population for 2000", region label = "Region of U.S."
from satscore inner join sashelp.us_data
on satscore.state=statename
where (sat1990 + 15) < sat2000 and population_1990 < population_2000;
quit;

* Problem 2(d);

PROC SQL;
create table allstates as
select coalesce(satscore.state,statename) as staten label="State Name", sat1990 label="SAT Scores for 1990", sat2000 label="SAT Scores for 2000", population_1990 label="Population for 1990", population_2000 label="Population for 2000", region label = "Region of U.S."
from satscore full join sashelp.us_data
on satscore.state=statename;
quit;

PROC SQL;
select avg(sat2000) as avgsat label="Average 2000 SAT by Region", region label = "Region of U.S."
from allstates
where region not is missing
group by region;
quit;