data bbstats; label atbats="At Bats"; input Player $19. Position $ atbats Hits BB; datalines; Christian Walker Infield 271 97 36 Scott Wingo Infield 240 81 44 Brady Thomas Infield 231 73 23 Evan Marzilli Outfield 220 64 25 Robert Beary Infield 211 61 12 Adrian Morales Infield 249 70 30 Peter Mooney Infield 254 71 44 Jake Williams Outfield 209 56 21 Jackie Bradley Jr. Outfield 162 40 22 ; run; *Slide 12; proc sql; select player, atbats from bbstats; quit; *Slide 13; proc sql; select player, hits/atbats as avg from bbstats; quit; *Slide 15; proc sql; select player, atbats from bbstats where atbats > 230; quit; proc sql; select player, atbats from bbstats where hits > 70; quit; proc sql; select player, atbats from bbstats where Position='Infield'; quit; *Slide 18; proc sql; select player, atbats from bbstats order by atbats desc; quit; *Slide 19; proc sql; select player, atbats from bbstats order by 2 desc; quit; *Slide 20; proc sql; select player, position, atbats from bbstats order by position, atbats desc; quit; 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; *Slide 3; proc sql; select player, atbats, hits, bb from bbstats; quit; proc sql; select * from bbstats; quit; *Check order with PROC CONTENTS; proc contents data=bbstats order=varnum; run; *slide 5; proc sql feedback; select * from bbstats; quit; *Slide 7; proc sql outobs=5; select player, atbats from bbstats; quit; proc sql inobs=5; select player, atbats from bbstats; quit; *Slide 10; 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 Scott Wingo 240 81 44 ; run; proc sql; select distinct player, atbats, hits, bb from bbstats; quit; *Slide 14; proc sql; select player, atbats from bbstats where atbats between 162 and 215; quit; *Slide 15; proc sql; select player, atbats from bbstats where atbats not between 162 and 215; quit; *Slide 17; proc sql; select player, atbats from bbstats where upcase(player) contains "IA"; quit; *Slide 19; proc sql; select player, atbats from bbstats where player in ("Christian Walker", "Jake Williams"); quit; *Slide 22; proc sql; select player, atbats from bbstats where player like "Ja%"; quit; proc sql; select player, atbats from bbstats where player like "%ia%"; quit; proc sql; select player, atbats from bbstats where player like "%ia__"; quit; *Slide 25; proc sql; select player, hits/atbats as avg from bbstats where calculated avg > .300; quit; *Slide 27; proc sql; title "Averages for 2011 USC Gamecocks"; select player label="Player Name", hits/atbats as avg label="Average" format=4.3 from bbstats; quit; *Slide 29; proc sql; select player, "Average is:", hits/atbats as avg from bbstats; quit; *Slide 33; data bbstats; label atbats="At Bats"; input Player $19. Position $ atbats Hits BB; datalines; Christian Walker Infield 271 97 36 Scott Wingo Infield 240 81 44 Brady Thomas Infield 231 73 23 Evan Marzilli Outfield 220 64 25 Robert Beary Infield 211 61 12 Adrian Morales Infield 249 70 30 Peter Mooney Infield 254 71 44 Jake Williams Outfield 209 56 21 Jackie Bradley Jr. Outfield 162 40 22 ; run; proc sql; select sum(atbats) as totalatbats, sum(hits) as totalhits from bbstats; quit; *Slide 34; proc sql; select position, sum(atbats) as totalatbats, sum(hits) as totalhits from bbstats group by position; quit; *Slide 36; proc sql; select count (*) as count from bbstats; quit; *Slide 37; proc sql; select count (position) as count from bbstats; quit; *Slide 38; proc sql; select count (distinct position) as count from bbstats; quit; *Slide 40; proc sql; select position, sum(atbats) as totalatbats, sum(hits) as totalhits from bbstats group by position having totalhits > 160; quit; *Slide 42; proc sql; select position, sum(atbats) as totalatbats, sum(hits) as totalhits from bbstats group by position having totalhits > (select sum(hits) from bbstats where position="Outfield"); quit; proc sql; select position, avg(CrRBI) as avgCrRBI from sashelp.baseball group by position having (avg(CrRBI) < (select avg(CrRBI) as avgCrRBI from sashelp.baseball)) order by avgCrRBI; quit; proc sql; select position, avg(CrRBI) as avgCrRBI from sashelp.baseball group by position having (avg(CrRBI) > (select avg(CrRBI) as avgCrRBI from sashelp.baseball)) order by avgCrRBI; quit; proc sql; select Name, Position, CrRBI from sashelp.baseball where CrRBI > (select avg(CrRBI) as avgCrRBI from sashelp.baseball) order by position, CrRBI; quit; *Slide 44; data AtBats; input Player $11. atbats; datalines; Walker 271 Wingo 240 Thomas 231 Marzilli 220 Beary 211 Morales 249 Mooney 254 Williams 209 Bradley Jr. 162 ; run; data Playerposition; input Player $11. Position $ ; datalines; Walker Infield Wingo Infield Thomas Infield Marzilli Outfield Beary Infield Morales Infield Mooney Infield Williams Outfield Bradley Jr. Outfield ; run; proc sql; select player, atbats from atbats where "Infield"= (select position from playerposition where atbats.player=playerposition.player); quit; *Slide 48; proc sql noexec; select position, atbats, hits from bbstats; quit; *Slide 49; proc sql; validate select position, atbats, hits from bbstats; quit;