%let name=sat;
filename odsout '.';

/*
Raw data from ...
http://mathforum.org/workshops/sum96/data.collections/datalibrary/SAT.2004.xls
*/
PROC IMPORT OUT=rawdata DATAFILE=".\sat.xls" DBMS=XLS REPLACE;
 RANGE='Sheet1$A5:AY55';
 GETNAMES=NO;
RUN;

data rawdata (drop=a b c d e f g h i j k l m n); set rawdata;
 F1=a; F2=b; F3=c; F4=d; F5=e; F6=F; F7=g; F8=h; F9=i; F10=j; F11=k; F12=l; F13=m; 
 F14=n; F15=o; F16=p; F17=q; F18=r; F19=s; F20=t; F21=u; F22=v; F23=w; F24=x; F25=y; F26=z;

 F27=aa; F28=ab; F29=ac; F30=ad; F31=ae; F32=aF; F33=ag; F34=ah; F35=ai; F36=aj; F37=ak; F38=al; F39=am; 
 F40=an; F41=ao; F42=ap; F43=aq; F44=ar; F45=as; F46=at; F47=au; F48=av; F49=aw; F50=ax; F51=ay; 
run;


data sat_data (keep = state year participation verbal math); 
 length state $50;
 set rawdata;
 format participation percentn7.0;

 state=f1;
 if state eq 'D.C.' then state='District of Columbia';

 year=2004;
 participation=f2;
 verbal       =f3;
 math         =f4;
 output;

 year=2003;
 participation=f6;
 verbal       =f7;
 math         =f8;
 output;

 year=2002;
 participation=f10;
 verbal       =f11;
 math         =f12;
 output;

 year=2001;
 participation=f14;
 verbal       =f15;
 math         =f16;
 output;

 year=2000;
 participation=.;
 verbal       =f18;
 math         =f19;
 output;

 year=1999;
 participation=.;
 verbal       =f21;
 math         =f22;
 output;

 year=1998;
 participation=f24;
 verbal       =f25;
 math         =f26;
 output;

 year=1997;
 participation=.;
 verbal       =f28;
 math         =f29;
 output;

 year=1996;
 participation=.;
 verbal       =f31;
 math         =f32;
 output;

 year=1995;
 participation=.;
 verbal       =f34;
 math         =f35;
 output;

 year=1994;
 participation=.;
 verbal       =f37;
 math         =f38;
 output;

 year=1993;
 participation=.;
 verbal       =f40;
 math         =f41;
 output;

 year=1992;
 participation=.;
 verbal       =f43;
 math         =f44;
 output;

 year=1991;
 participation=.;
 verbal       =f46;
 math         =f47;
 output;

/* 
1990 scores are *way* lower, and must have been to a different
sort of standard/difficulty test, therefore leave them out of 
the gif animation.
/*
 year=1990;
 participation=f49;
 verbal       =f50;
 math         =f51;
 output;
*/

run;

proc sort data=sat_data;
 by year state;
run;


%let dataset=sat_data;
%let y_var=verbal;
%let x_var=math;
%let size_var=participation;


libname mydata '.';
proc sql;
 create table plotdata as
 select * from &dataset;
quit; run;

data plotdata; set plotdata;
 if (participation eq .) then participation=.4;
run;

proc sql;
 create table annoyear as
 select unique year
 from plotdata;

 create table states as
 select unique fipnamel(state) as state, fipstate(state) as st
 from maps.us;
 create table plotdata as 
 select unique plotdata.*, states.st
 from plotdata left join states
 on plotdata.state=states.state;
quit; run;


options ls=200 nocenter;
proc print data=plotdata; 
 var year state st participation verbal math;
run;

data annolabels; set plotdata;
length text $30 color $8 style $35;
xsys='2'; ysys='2'; hsys='3'; when='a'; 
x=&x_var; y=&y_var; 
function='label'; style=''; position='5'; color='gray'; size=1.75; 
text=trim(left(st));
if st in ('NC') then do;
 color='red';
 size=size*1.25;
 style='albany amt/bold';
 end;
run;

data annoyear; set annoyear;
xsys='1'; ysys='1'; hsys='3'; x=85; y=88; position='5'; when='a';
function='label'; color='gray'; size=3.5; text=trim(left(year));
run;

data myanno; set annolabels annoyear;
run;

proc sort data=myanno out=myanno;
by year;
run;

proc sort data=plotdata out=plotdata;
by year;
run;





goptions xpixels=650 ypixels=600 hsize= vsize=;
goptions cback=white gunit=pct;
goptions noborder;

options dev=sasprtc printerpath=gif animduration=.6 animloop=1
 animoverlay=no animate=start center;

ods listing close;
ods html path=odsout body="&name..htm"
 (title="SAT Scores (SAS/Graph gifanim)")
 style=htmlblue;


goptions gunit=pct htitle=4 htext=3 ftitle="albany amt/bold" ftext="albany amt";

options nobyline;
title1 ls=1.5 "SAT Test Scores";

footnote1 j=left move=(+1,+0) h=2.5 c=gray 
 "Note: circle size represents participation rate (for years available)";
footnote2 j=left move=(+1,+0) h=2.5 c=gray 
 "(perhaps higher participation is a major factor in lower scores?)";

axis2 order=(450 to 650 by 50) minor=none value=(color=gray) offset=(0,0);
axis1 order=(450 to 650 by 50) minor=none value=(color=gray) offset=(0,0);
 
proc gplot data=plotdata;
by year;
bubble &y_var*&x_var=&size_var /
 anno=myanno
 vaxis=axis1 haxis=axis2
 autovref autohref cvref=graycc chref=graycc
 bsize=25
 des='' name="&name";
run;

quit;
ods html close;
ods listing;