options ls=80;

/*
  ca2008 data files at http://star.cde.ca.gov/star2008/Viewreport.asp
*/

proc import datafile="ca2008_1_csv_v3.txt" out=ca2008 dbms=csv;
run;

data use(drop=Mean_Scaled_Score);
   set ca2008(keep=County_Code Grade Mean_Scaled_Score Students_with_Scores Test rename = (Students_with_Scores=N));
   Score = Mean_Scaled_Score + 0;
run;

proc freq data=use;
  tables Test*Grade/list;
run;

data testc;
   set use(where=(Test='C'));
run;

proc summary nway data=testc;
   class County_Code Grade;
   var Score;
   weight N;
   output out=next mean=;
run;

proc sort data=next;
  by Grade;

proc rank data=next out=rnext;
  by Grade;
  var Score;
run;

proc sort data=rnext;
  by County_Code;
run;

data allgrades;
  set rnext;
  by County_Code;
  retain rank1-rank13;
  array ranks rank1-rank13;
  if first.County_Code then do i=1 to dim(ranks);
      ranks{i} = .;
      end;
  ranks{grade} = Score;
  if last.County_Code then do;
     avgrank = mean(of rank1-rank13);
     output;
     end;
run;

proc sort data=allgrades;
  by descending avgrank;
run;

proc print data=allgrades;
   var County_Code rank: avgrank;
run;

proc import datafile="ca2008entities_csv.txt" out=info dbms=csv;
run;

data info;
  set info(keep=County_Code County_Name);
  rename County_Name = County;
run;

proc sort data=info;
   by County_Code;
run;

data info;
   set info;
   by County_Code;
   if first.County_Code;
run;

proc sort data=allgrades(drop=rank1 rank12);
   by County_Code;
run;

data allgrades;
  merge allgrades info;
  by County_Code;
run;

proc sort data=allgrades;
  by descending avgrank;
run;

proc print data=allgrades;
  var County rank: avgrank;
run;

/* per capita income data set (D9) from 
    http://www.dof.ca.gov/HTML/FS_DATA/STAT-ABS/Toc_xls.htm 
  proc import doesn't seem to read these files, so open in a 
  spreadsheet program and use File -> Save As and choose CSV
*/

proc import datafile='D9.csv' replace out=income;
getnames=no;
run;

proc sort data=income out=income(rename=(var1=County var12=Income) keep=var1 var12);
by var1;
run;

proc sort data=allgrades;
by County;
run;

data both;
   merge allgrades income;
   by County;
run;

proc gplot data=both;
   plot avgrank * Income;
run;

  
proc sort data=next;
  by County_Code;

data now;
  merge next info;
  by County_Code;
  countynm = upcase(County);
  drop County;
run;

data calicounty;
  set maps.cntyname;
  where state=6;
run;

data calidata;
  set maps.uscounty;
  where state=6;
run;

proc sort data=now;
  by countynm;
run;

data both;
  merge now calicounty;
  by countynm;
run;

proc sort data=both;
  by grade;
run;

pattern1 color=cxffcccc;
pattern2 color=cxff9999;
pattern3 color=cxff6666;
pattern4 color=cxff3333;
proc gmap map=calidata data=both;
   by grade;
   id County;
   choro Score/levels=4;
run;