/*IMPORTING 2019 NFL DATA*/
FILENAME REFFILE 'C:/Users/Zac Pungitore/Desktop/602 Stats for data analytics/Term Project/2019_NFLStats.xlsx';

PROC IMPORT DATAFILE=REFFILE
    DBMS=XLSX
    OUT=STATS.NFL2019 REPLACE;
    GETNAMES=YES;
RUN;
/*REMOVING * AND + FROM NAME COLUMN*/
DATA STATS.TECHDOC;
    SET STATS.NFL2019;
    Player=compress(player,'*');
    Player=compress(player,'+');
RUN;

/*Sorting Data by Scrimmage Yards*/
Proc sort data=stats.techdoc;
    by descending yscm;
run;

/*Where statements to filter by position*/
proc print data=stats.techdoc;
    where pos="qb";
run;
proc print data=stats.techdoc;
    where pos="rb";
run;
proc print data=stats.techdoc;
    where pos="wr";
run;
proc print data=stats.techdoc;
    where pos="te";
run;

/*where statements using and or*/
proc print data=stats.techdoc;
    where pos="RB" and RRTD>10 and YScm>1200;
run;
proc print data=stats.techdoc;
    where yds>500 and yds_1>1000;
run;
proc print data=stats.techdoc;
    where 800<yds_1<1000 or rrtd>15;
run;
proc print data=stats.techdoc;
    where att>80 and 60<rec<100;
run;
proc print data=stats.techdoc;
    where lng>50 or yds>1000;
run;

/*Special WHERE statements*/
proc print data=stats.techdoc;
    where POS is missing;
run;
proc print data=stats.techdoc;
    where rec between 80 and 120;
run;
proc print data=stats.techdoc;
    where player like "%Jones";
run;

/*Using macrovariables*/
%let pos=RB;
proc print data=stats.techdoc;
    where pos="&pos";
run;
proc means data=stats.techdoc;
    where pos="&pos";
run;
proc freq data=stats.techdoc;
    where pos="&pos";
run;

/*Drop and Keep columns*/
data stats.filteredTECHDOC;
    SET STATS.TECHDOC;
    where g=16;
    KEEP Player Age Pos G rec yds td att yds_1 td_1 yscm rrtd;
run;

/*using If/Then Statements to filter*/
DATA STATS.ifthenTECHDOC;
SET STATS.filteredTECHDOC;
    FORMAT POS $UPCASE2.;
    IF POS="QB" THEN DELETE;
    IF POS="WR" THEN DELETE;
    IF POS="TE" THEN DELETE;
    IF ATT>=40 THEN POS="RB";
    IF ATT<40 THEN DELETE;
    IF POS=" " THEN DELETE;
run;