/*******************************************************************\ |Systemic Research Inc. | |Evaluation of Urban Systemic Initiative | |Data Extractor | |SAS PROGRAM I | |October 2001 -- February 2002 | |by Kazuaki Uekawa, Ph.D. | |Independent SAS consultant | |(kaz_uekawa@hotmail.com) | |http://www.src.uchicago.edu/users/ueka | |http://www.geocities.com/sastip | ********************************************************************/ /*Specify the working directory here e.g., %let WD=D:\systemic\ECB; */ %let WD=D:\systemic\ECB; libname here "&WD"; /*This is raw data folder. Raw data are most excel files that came with this program. e.g., %let WD2=D:\systemic\ECB; */ %let DD=D:\systemic\ECB; libname there "&WD"; /*Finally, this version can be run in any machine*/ /*with 4.2, I made a lot more data sets. THe data names ending with CITYYEAR and CITY*/ /*with 4.1, I am adding ACT and SAT data*/ /*I fixed everything and added new data also from a CD that Ana gave me. For LA, I used the version Anna sent me later Nov 7th*/ /*now I went back to old kids data because the latest one had a messed up data--due to a recent addition of Atlanta data*/ /*I changed my mind and did not do standardization of rubric variables. But Rubric9_1's matrix is a bit odd, though I forced it to range from 0 to 1*/ /*from 3.1, the role of this program is just to get data out. I got rid of all analysis and will have separate files for them*/ /*now version 2.1, I added rubric data*/ /*update, I now use emrolment3.xls*/ /*update from 1.3 to 1.4 I changed totalsci to allsci to shorten the names; otherwise, timeplot would not print them. ALso the excell data now is ...enrollment2.xls*/ /*update form 1.2 to 1.3. After conversation with Ana, I added passing rate*/ /*update from 1.1 to 1.2; I made the program more logical. In the previous version, some proc mean statements were a bit too messy looking Oct. 20th, 2001*/ /*Here, ACT data*/ %macro asdf (city=, token=,sheet=,sub=); PROC IMPORT OUT= &sub.AP&token DATAFILE= "&dd.\&city AP 2001.xls" DBMS=EXCEL2000 REPLACE; GETNAMES=NO; * SHEET="Math"; sheet="&sheet"; RUN; data &sub.AP&token.2;set &sub.AP&token; keep F1 year1 year2 year3 year4 year5 year6 year7; if F1 ="Num of tests taken/1,000 stu." or F1="Num of tests-taken/1,000 stu." or F1="Num of Above 3/ 1,000 students" or F1="Male" or F1="Female" or F1="AI/AN" or F1="A/PI" or F1="B/AA" or F1="H/L" or F1="W" or F1="Total" or F1="Scoring Above 3" or F1="Total Num of 11th & 12th students" ; Year1=f2 + 0; Year2=f3 + 0; Year3=f4 + 0; Year4=f5 + 0; Year5=f6 + 0; Year6=f7 + 0; Year7=f8 + 0; array Ys year1 year2 year3 year4 year5 year6 year7; do over Ys; if Ys < -9 then Ys=.; end; proc transpose data=&sub.AP&token.2 out=&sub.AP&token.2T; id F1; var Year1--year7 ;run; data &sub.AP&token.2T2;set &sub.AP&token.2T; length city $ 12; length CAP&sub.taker Ctotal_N1K_AP&sub.taker CAP&sub.score3 CTotal_N1K_AP&sub.score3 CMale_N1K_AP&sub.taker CFemale_N1K_AP&sub.taker CIndian_N1K_AP&sub.taker CAsian_N1K_AP&sub.taker CBlack_N1K_AP&sub.taker CHispanic_N1K_AP&sub.taker CWhite_N1K_AP&sub.taker Ctotal_grade11and12 $ 15; keep CAP&sub.taker Ctotal_N1K_AP&sub.taker CAP&sub.score3 CTotal_N1K_AP&sub.score3 CMale_N1K_AP&sub.taker CFemale_N1K_AP&sub.taker CIndian_N1K_AP&sub.taker CAsian_N1K_AP&sub.taker CBlack_N1K_AP&sub.taker CHispanic_N1K_AP&sub.taker CWhite_N1K_AP&sub.taker city year Ctotal_grade11and12 ; city="&city"; CAP&sub.taker=Total; CAP&sub.score3=Scoring_above_3; CTotal_N1K_AP&sub.score3=Num_of_Above_3___1_000_students; CMale_N1K_AP&sub.taker=Male; CFemale_N1K_AP&sub.taker=Female; CIndian_N1K_AP&sub.taker=AI_AN; CAsian_N1K_AP&sub.taker=A_PI; CBlack_N1K_AP&sub.taker=B_AA; CHispanic_N1K_AP&sub.taker=H_L; CWhite_N1K_AP&sub.taker=W; year=_N_; Ctotal_grade11and12=Total_Num_of_11th___12th_student; Ctotal_N1K_AP&sub.taker= Num_of_tests_taken_1_000_stu_; %mend asdf; %asdf (token=atlanta,city=Atlanta,sheet=Math,sub=math); %asdf (token=baltimore,city=Baltimore,sheet=Math,sub=math); %asdf (token=chicago,city=Chicago,sheet=Math,sub=math); %asdf (token=cleveland,city=Cleveland,sheet=Math,sub=math); %asdf (token=columbus,city=Columbus,sheet=Math,sub=math); %asdf (token=dallas,city=Dallas,sheet=Math,sub=math); %asdf (token=detroit,city=Detroit,sheet=Math,sub=math); %asdf (token=el_paso,city=El Paso,sheet=Math,sub=math); %asdf (token=fresno,city=Fresno,sheet=Math,sub=math); %asdf (token=jacksonville,city=Jacksonville,sheet=Math,sub=math); %asdf (token=los_angeles,city=Los Angeles,sheet=Math,sub=math); %asdf (token=memphis,city=Memphis,sheet=Math,sub=math); %asdf (token=miami_dade,city=Miami-Dade,sheet=Math,sub=math); %asdf (token=milwaukee,city=Milwaukee,sheet=Math,sub=math); %asdf (token=new_orleans,city=New Orleans,sheet=Math,sub=math); %asdf (token=new_york,city=New York,sheet=Math,sub=math); %asdf (token=philadelphia,city=Philadelphia,sheet=Math,sub=math); %asdf (token=phoenix,city=Phoenix,sheet=Math,sub=math); %asdf (token=san_antonio,city=San Antonio,sheet=Math,sub=math); %asdf (token=san_diego,city=San Diego,sheet=Math,sub=math); %asdf (token=st_louis,city=St. Louis,sheet=Math,sub=math); %asdf (token=atlanta,city=Atlanta,sheet=Science,sub=sci); %asdf (token=baltimore,city=Baltimore,sheet=Science,sub=sci); %asdf (token=chicago,city=Chicago,sheet=Science,sub=sci); %asdf (token=cleveland,city=Cleveland,sheet=Science,sub=sci); %asdf (token=columbus,city=Columbus,sheet=Science,sub=sci); %asdf (token=dallas,city=Dallas,sheet=Science,sub=sci); %asdf (token=detroit,city=Detroit,sheet=Science,sub=sci); %asdf (token=el_paso,city=El Paso,sheet=Science,sub=sci); %asdf (token=fresno,city=Fresno,sheet=Science,sub=sci); %asdf (token=jacksonville,city=Jacksonville,sheet=Science,sub=sci); %asdf (token=los_angeles,city=Los Angeles,sheet=Science,sub=sci); %asdf (token=memphis,city=Memphis,sheet=Science,sub=sci); %asdf (token=miami_dade,city=Miami-Dade,sheet=Science,sub=sci); %asdf (token=milwaukee,city=Milwaukee,sheet=Science,sub=sci); %asdf (token=new_orleans,city=New Orleans,sheet=Science,sub=sci); %asdf (token=new_york,city=New York,sheet=Science,sub=sci); %asdf (token=philadelphia,city=Philadelphia,sheet=Science,sub=sci); %asdf (token=phoenix,city=Phoenix,sheet=Science,sub=sci); %asdf (token=san_antonio,city=San Antonio,sheet=Science,sub=sci); %asdf (token=san_diego,city=San Diego,sheet=Science,sub=sci); %asdf (token=st_louis,city=St. Louis,sheet=Science,sub=sci); %asdf (token=atlanta,city=Atlanta,sheet=Computer,sub=comp); %asdf (token=baltimore,city=Baltimore,sheet=Computer,sub=comp); %asdf (token=chicago,city=Chicago,sheet=Computer,sub=comp); %asdf (token=cleveland,city=Cleveland,sheet=Computer,sub=comp); %asdf (token=columbus,city=Columbus,sheet=Computer,sub=comp); %asdf (token=dallas,city=Dallas,sheet=Computer,sub=comp); %asdf (token=detroit,city=Detroit,sheet=Computer,sub=comp); %asdf (token=el_paso,city=El Paso,sheet=Computer,sub=comp); %asdf (token=fresno,city=Fresno,sheet=Computer,sub=comp); %asdf (token=jacksonville,city=Jacksonville,sheet=Computer,sub=comp); %asdf (token=los_angeles,city=Los Angeles,sheet=Computer,sub=comp); %asdf (token=memphis,city=Memphis,sheet=Computer,sub=comp); %asdf (token=miami_dade,city=Miami-Dade,sheet=Computer,sub=comp); %asdf (token=milwaukee,city=Milwaukee,sheet=Computer,sub=comp); %asdf (token=new_orleans,city=New Orleans,sheet=Computer,sub=comp); %asdf (token=new_york,city=New York,sheet=Computer,sub=comp); %asdf (token=philadelphia,city=Philadelphia,sheet=Computer,sub=comp); %asdf (token=phoenix,city=Phoenix,sheet=Computer,sub=comp); %asdf (token=san_antonio,city=San Antonio,sheet=Computer,sub=comp); %asdf (token=san_diego,city=San Diego,sheet=Computer,sub=comp); %asdf (token=st_louis,city=St. Louis,sheet=Computer,sub=comp); %macro hi (sub=); data allcities⊂ set &sub.APatlanta2T2 &sub.APbaltimore2T2 &sub.APchicago2T2 &sub.APcleveland2T2 &sub.APcolumbus2T2 &sub.APdallas2T2 &sub.APdetroit2T2 &sub.APel_paso2T2 &sub.APfresno2T2 &sub.APjacksonville2T2 &sub.APlos_angeles2T2 &sub.APmemphis2T2 &sub.APmiami_dade2T2 &sub.APmilwaukee2T2 &sub.APnew_orleans2T2 &sub.APnew_york2T2 &sub.APphiladelphia2T2 &sub.APphoenix2T2 &sub.APsan_antonio2T2 &sub.APsan_diego2T2 &sub.APst_louis2T2; by city year; keep AP&sub.taker total_N1K_AP&sub.taker AP&sub.score3 Total_N1K_AP&sub.score3 Male_N1K_AP&sub.taker Female_N1K_AP&sub.taker Indian_N1K_AP&sub.taker Asian_N1K_AP&sub.taker Black_N1K_AP&sub.taker Hispanic_N1K_AP&sub.taker White_N1K_AP&sub.taker city year total_grade11and12; /*ideally one should use Y=input(X, best4.) instead of using *1 but it is not working. Perhaps Version 8.1's bug*/ AP&sub.taker=CAP&sub.taker*1; total_N1K_AP&sub.taker =Ctotal_N1K_AP&sub.taker*1; AP&sub.score3=CAP&sub.score3*1; Total_N1K_AP&sub.score3=CTotal_N1K_AP&sub.score3*1; Male_N1K_AP&sub.taker=CMale_N1K_AP&sub.taker*1; Female_N1K_AP&sub.taker=CFemale_N1K_AP&sub.taker*1; Indian_N1K_AP&sub.taker=CIndian_N1K_AP&sub.taker*1; Asian_N1K_AP&sub.taker =CAsian_N1K_AP&sub.taker*1; Black_N1K_AP&sub.taker=CBlack_N1K_AP&sub.taker*1; Hispanic_N1K_AP&sub.taker=CHispanic_N1K_AP&sub.taker*1; White_N1K_AP&sub.taker=CWhite_N1K_AP&sub.taker*1; total_grade11and12=1*Ctotal_grade11and12; run; *proc sort; *by city year; *run; %mend hi; %hi (sub=math); %hi (sub=sci); %hi (sub=comp); data allcities; merge allcitiesmath allcitiescomp allcitiessci ; /*here comp should not come last*/ by city year; if APmathtaker > -9 ; run; proc sort data=allcities out=allcities;by city year;run; *proc print data=compAPnew_orleans2T2 ; *run; proc sql; create table allcities2 as select *, sum(year) as flag from allcities group by city; /* proc print data=allcities2; var city year flag;run; */ data here.AP_CityYear; set allcities2; if flag=28 then do; if year=7 then year=2000; if year=6 then year=1999; if year=5 then year=1998; if year=4 then year=1997; if year=3 then year=1996; if year=2 then year=1995; if year=1 then year=1994; end; if flag=21 then do; if year=6 then year=2000; if year=5 then year=1999; if year=4 then year=1998; if year=3 then year=1997; if year=2 then year=1996; if year=1 then year=1995; end; if flag=15 then do; if year=5 then year=2000; if year=4 then year=1999; if year=3 then year=1998; if year=2 then year=1997; if year=1 then year=1996; end; if flag=6 then do; if year=1 then year=1998; if year=2 then year=1999; if year=3 then year=2000; end; /* proc print data=here.AP_CityYear; where city="Milwaukee"; var city year flag apmathtaker total_n1k_apmathtaker total_n1k_apscitaker total_n1k_apcomptaker total_n1k_apmathscore3 total_n1k_apsciscore3 total_n1k_apcompscore3 total_grade11and12 ;run; */ PROC EXPORT DATA= here.AP_CityYear OUTFILE= "&WD.\AP_CityYear.xls" DBMS=EXCEL2000 REPLACE;RUN; proc sort data=here.ap_cityyear out=apcityyear;by city year;run; proc transpose data=apcityyear out=ap_T; by city; id year; var APmathtaker total_N1K_APmathtaker APmathscore3 Total_N1K_APmathscore3 Male_N1K_APmathtaker Female_N1K_APmathtaker Indian_N1K_APmathtaker Asian_N1K_APmathtaker Black_N1K_APmathtaker Hispanic_N1K_APmathtaker White_N1K_APmathtaker total_grade11and12 APscitaker total_N1K_APscitaker APsciscore3 Total_N1K_APsciscore3 Male_N1K_APscitaker Female_N1K_APscitaker Indian_N1K_APscitaker Asian_N1K_APscitaker Black_N1K_APscitaker Hispanic_N1K_APscitaker White_N1K_APscitaker APcomptaker total_N1K_APcomptaker APcompscore3 Total_N1K_APcompscore3 Male_N1K_APcomptaker Female_N1K_APcomptaker Indian_N1K_APcomptaker Asian_N1K_APcomptaker Black_N1K_APcomptaker Hispanic_N1K_APcomptaker White_N1K_APcomptaker; run; data ap_T2;set ap_T; grow2000=_2000-_1999; grow1999=_1999-_1998; grow1998=_1998-_1997; grow1997=_1997-_1996; grow1996=_1996-_1995; grow1995=_1995-_1994; *proc print data=AP_T2; *var city _NAME_ grow1995 grow1996 grow1997 grow1998 grow1999 grow2000 ; *run; data AP_T3;set AP_T2; annual=mean(of grow1995 grow1996 grow1997 grow1998 grow1999 grow2000); *proc print data=ap_T3; *var city _name_ annual; *run; proc transpose data=ap_T3 out=ap_T4; id _name_; by city; var annual; run; *proc print data=ap_T4;run; data here.AP_City;set ap_T4; drop _NAME_; /*Here assign lables. If you want to changes the texts, use label list.xls, which comes with this program*/ label city ="City" year ="Year" APmathtaker ="Number of AP Math test taker " total_N1K_APmathtaker ="Number of AP Math test taker per 1,000 Students " APmathscore3 ="Number of AP Math test taker scoring higher than 3 " Total_N1K_APmathscore3 ="Number of AP Math test taker scoring higher than 3 per 1,000 Students " Male_N1K_APmathtaker ="Male: Number of AP Math test taker scoring higher than 3 per 1,000 Students " Female_N1K_APmathtaker ="Female: Number of AP Math test taker per 1,000 Students " Indian_N1K_APmathtaker ="Indian: Number of AP Math test taker per 1,000 Students " Asian_N1K_APmathtaker ="Asian: Number of AP Math test taker per 1,000 Students " Black_N1K_APmathtaker ="Black: Number of AP Math test taker per 1,000 Students " Hispanic_N1K_APmathtaker ="Hispanic: Number of AP Math test taker per 1,000 Students " White_N1K_APmathtaker ="White: Number of AP Math test taker per 1,000 Students " total_grade11and12 ="Population of Grade 11 and Grade 12 " APcomptaker ="Number of AP computer test taker " total_N1K_APcomptaker ="Number of AP computer test taker per 1,000 Students " APcompscore3 ="Number of AP Computer test taker per 1,000 Students " Total_N1K_APcompscore3 ="Number of AP computer test taker scoring higher than 3 per 1,000 Students " Male_N1K_APcomptaker ="Male: Number of AP Computer test taker scoring higher than 3 per 1,000 Students " Female_N1K_APcomptaker ="Female: Number of AP Computer test taker per 1,000 Students " Indian_N1K_APcomptaker ="Indian: Number of AP Computer test taker per 1,000 Students " Asian_N1K_APcomptaker ="Asian: Number of AP Computer test taker per 1,000 Students " Black_N1K_APcomptaker ="Black: Number of AP Computer test taker per 1,000 Students " Hispanic_N1K_APcomptaker ="Hispanic: Number of AP Computer test taker per 1,000 Students " White_N1K_APcomptaker ="White: Number of AP Computer test taker per 1,000 Students " APscitaker ="Number of Science test taker " total_N1K_APscitaker ="Number of AP science taker per 1,000 students " APsciscore3 ="Number of AP science taker scoring higher than 3 " Total_N1K_APsciscore3 ="Number of AP science taker scoring higher than 3 per 1,000 studnets " Male_N1K_APscitaker ="Male: Number of AP Science test taker scoring higher than 3 per 1,000 Students " Female_N1K_APscitaker ="Female: Number of AP Science test taker per 1,000 Students " Indian_N1K_APscitaker ="Indian: Number of AP Science test taker per 1,000 Students " Asian_N1K_APscitaker ="Asian: Number of AP Science test taker per 1,000 Students " Black_N1K_APscitaker ="Black: Number of AP Science test taker per 1,000 Students " Hispanic_N1K_APscitaker ="Hispanic: Number of AP Science test taker per 1,000 Students " White_N1K_APscitaker ="White: Number of AP Science test taker per 1,000 Students " ; proc contents data=here.AP_City; title1 "Contents of AP_CITY"; title2 "Case=City"; run; /* proc means data=here.AP_City; title1 "Descriptive Statistics of AP_CITY"; title2 "Case=City"; run;*/ PROC EXPORT DATA= here.AP_City OUTFILE= "&WD.\AP_City.xls" DBMS=EXCEL2000 REPLACE;RUN; /*Here, ACT data*/ PROC IMPORT OUT= ACT DATAFILE= "&dd.\USI ACT test scores.xls" DBMS=EXCEL2000 REPLACE; GETNAMES=YES; RUN; data act;set act; if city ne "Columbus_old"; ID=compress(trim(type) || trim(year)); *proc print data=act; *run; ************************************; /*CREATE CITY YEAR DATA*/ proc sort data=act out=actX; by city year;run; /* proc print data=actX; run;*/ %macro these (stat=); data &stat._ACT;set actX; if type="&stat"; keep year city Male_&stat Female_&stat Black_&stat Native_&stat White_&stat Chicano_&stat Asian_&stat Hispanic_&stat Total_&stat; Male_&stat=Male; Female_&stat=Female; Black_&stat=Black; Native_&stat=Native; White_&stat=White; Chicano_&stat=Chicano; Asian_&stat=Asian; Hispanic_&stat=Hispanic; Total_&stat=Total; %mend these; %these (stat=ACTtaker); %these (stat=ACTeng); %these (stat=ACTmath); %these (stat=ACTread); %these (stat=ACTsci); %these (stat=ACTcompo); data ACT_CityYear; merge ACTtaker_act ACTeng_act ACTmath_act ACTread_act ACTsci_act ACTcompo_act ; by city year; run; /* goptions dev=gif gunit=pct cback=white htitle=6 htext=3 ftext=swissb ctext=blue vsize=10 hsize=8; symbol interpol=join value=diamond height=3; proc gplot data=actcityyear; plot Total_actmath *year=city ; run; quit;*/ *************************************; proc sort data=act out=actS; by city;run; %MACRO chicago (type=); proc transpose data=actS out=act_&type;where type="&type";by city;id ID; var Male Female Black Native White Chicano Asian Hispanic Total ;run; %mend chicago; %chicago (type=ACTtaker); %chicago (type=ACTeng); %chicago (type=ACTmath); %chicago (type=ACTread); %chicago (type=ACTsci); %chicago (type=ACTcompo); data ACTall;merge act_ACTtaker act_ACTeng act_ACTmath act_ACTread act_ACTsci act_ACTcompo; by city; %macro michigan (male=); data ♂set actall; keep city &Male._ACTtaker1994 &Male._ACTeng1994 &Male._ACTmath1994 &Male._ACTread1994 &Male._ACTsci1994 &Male._ACTcompo1994 &Male._ACTtaker1995 &Male._ACTeng1995 &Male._ACTmath1995 &Male._ACTread1995 &Male._ACTsci1995 &Male._ACTcompo1995 &Male._ACTtaker1996 &Male._ACTeng1996 &Male._ACTmath1996 &Male._ACTread1996 &Male._ACTsci1996 &Male._ACTcompo1996 &Male._ACTtaker1997 &Male._ACTeng1997 &Male._ACTmath1997 &Male._ACTread1997 &Male._ACTsci1997 &Male._ACTcompo1997 &Male._ACTtaker1998 &Male._ACTeng1998 &Male._ACTmath1998 &Male._ACTread1998 &Male._ACTsci1998 &Male._ACTcompo1998 &Male._ACTtaker1999 &Male._ACTeng1999 &Male._ACTmath1999 &Male._ACTread1999 &Male._ACTsci1999 &Male._ACTcompo1999; /*select if*/ if _name_="&Male"; &Male._ACTtaker1994=ACTtaker1994;&Male._ACTeng1994=ACTeng1994;&Male._ACTmath1994=ACTmath1994; &Male._ACTread1994=ACTread1994;&Male._ACTsci1994=ACTsci1994;&Male._ACTcompo1994=ACTcompo1994; &Male._ACTtaker1995=ACTtaker1995;&Male._ACTeng1995=ACTeng1995;&Male._ACTmath1995=ACTmath1995; &Male._ACTread1995=ACTread1995;&Male._ACTsci1995=ACTsci1995;&Male._ACTcompo1995=ACTcompo1995; &Male._ACTtaker1996=ACTtaker1996;&Male._ACTeng1996=ACTeng1996;&Male._ACTmath1996=ACTmath1996; &Male._ACTread1996=ACTread1996;&Male._ACTsci1996=ACTsci1996;&Male._ACTcompo1996=ACTcompo1996; &Male._ACTtaker1997=ACTtaker1997;&Male._ACTeng1997=ACTeng1997;&Male._ACTmath1997=ACTmath1997; &Male._ACTread1997=ACTread1997;&Male._ACTsci1997=ACTsci1997;&Male._ACTcompo1997=ACTcompo1997; &Male._ACTtaker1998=ACTtaker1998;&Male._ACTeng1998=ACTeng1998;&Male._ACTmath1998=ACTmath1998; &Male._ACTread1998=ACTread1998;&Male._ACTsci1998=ACTsci1998;&Male._ACTcompo1998=ACTcompo1998; &Male._ACTtaker1999=ACTtaker1999;&Male._ACTeng1999=ACTeng1999;&Male._ACTmath1999=ACTmath1999; &Male._ACTread1999=ACTread1999;&Male._ACTsci1999=ACTsci1999;&Male._ACTcompo1999=ACTcompo1999; %mend michigan; %michigan (male=Male); %michigan (male=Female); %michigan (male=Black); %michigan (male=Native); %michigan (male=White); %michigan (male=Chicano); %michigan (male=Asian); %michigan (male=Hispanic); %michigan (male=Total); data ACTalldata;merge Male Female Black Native White Chicano Asian Hispanic Total;by city; /*construct some variables here*/ /*mean math and science score is prepared here*/ mean_ACTmath=mean (of total_ACTmath1994 total_ACTmath1995 total_ACTmath1996 total_ACTmath1997 total_ACTmath1998 total_ACTmath1999); mean_ACTsci=mean (of total_ACTsci1994 total_ACTsci1995 total_ACTsci1996 total_ACTsci1997 total_ACTsci1998 total_ACTsci1999); mean_ACTtaker=mean (of total_ACTtaker1994 total_ACTtaker1995 total_ACTtaker1996 total_ACTtaker1997 total_ACTtaker1998 total_ACTtaker1999); /*growth*/ /*ACTmath growth, mean of average annual growth*/ dif_total_ACTmath1995=total_ACTmath1995-total_ACTmath1994; dif_total_ACTmath1996=total_ACTmath1996-total_ACTmath1995; dif_total_ACTmath1997=total_ACTmath1997-total_ACTmath1996; dif_total_ACTmath1998=total_ACTmath1998-total_ACTmath1997; dif_total_ACTmath1999=total_ACTmath1999-total_ACTmath1998; growth_ACTmath=mean (of dif_total_ACTmath1995 dif_total_ACTmath1996 dif_total_ACTmath1997 dif_total_ACTmath1998 dif_total_ACTmath1999); /*science growth, mean of average annual growth*/ dif_total_ACTsci1995=total_ACTsci1995-total_ACTsci1994; dif_total_ACTsci1996=total_ACTsci1996-total_ACTsci1995; dif_total_ACTsci1997=total_ACTsci1997-total_ACTsci1996; dif_total_ACTsci1998=total_ACTsci1998-total_ACTsci1997; dif_total_ACTsci1999=total_ACTsci1999-total_ACTsci1998; growth_ACTsci=mean (of dif_total_ACTsci1995 dif_total_ACTsci1996 dif_total_ACTsci1997 dif_total_ACTsci1998 dif_total_ACTsci1999); /*test taker growth, mean of average annual growth*/ dif_total_ACTtaker1995=total_ACTtaker1995-total_ACTtaker1994; dif_total_ACTtaker1996=total_ACTtaker1996-total_ACTtaker1995; dif_total_ACTtaker1997=total_ACTtaker1997-total_ACTtaker1996; dif_total_ACTtaker1998=total_ACTtaker1998-total_ACTtaker1997; dif_total_ACTtaker1999=total_ACTtaker1999-total_ACTtaker1998; growth_ACTtaker=mean (of dif_total_ACTtaker1995 dif_total_ACTtaker1996 dif_total_ACTtaker1997 dif_total_ACTtaker1998 dif_total_ACTtaker1999); /*Gap between white and underrepresentative students*/ /*underrepresentative mean*/ /*To get the value for UR population, I am recaluculating a weight mean for the UR population, rather than using a mean of black score and hispanic score.*/ %macro tokyo (year); UR_ACTmath&year=((black_ACTmath&year*black_ACTtaker&year)+(hispanic_ACTmath&year*hispanic_ACTtaker&year))/ sum(of black_ACTtaker&year hispanic_ACTtaker&year); Gap_ACTmath&year=white_ACTmath&year-UR_ACTmath&year; UR_ACTsci&year=((black_ACTsci&year*black_ACTtaker&year)+(hispanic_ACTsci&year*hispanic_ACTtaker&year))/ sum(of black_ACTtaker&year hispanic_ACTtaker&year); Gap_ACTsci&year=white_ACTsci&year-UR_ACTsci&year; UR_ACTtaker&year=((black_ACTtaker&year*black_ACTtaker&year)+(hispanic_ACTtaker&year*hispanic_ACTtaker&year))/ sum(of black_ACTtaker&year hispanic_ACTtaker&year); Gap_ACTtaker&year=white_ACTtaker&year-UR_ACTtaker&year; /*Baltimore data does not have number of test takers, so I simply use black score to respresent UR population*/ /*Baltimore's UR population is primarily black*/ if city="Baltimore" then do; UR_ACTmath&year=black_ACTmath&year; Gap_ACTmath&year=white_ACTmath&year-UR_ACTmath&year; UR_ACTsci&year=black_ACTsci&year; Gap_ACTsci&year=white_ACTsci&year-UR_ACTsci&year; *UR_ACTtaker&year=black_ACTtaker&year; *Gap_ACTtaker&year=white_ACTtaker&year-URACTtaker&year; end; %mend tokyo; %tokyo (year=1994); %tokyo (year=1995); %tokyo (year=1996); %tokyo (year=1997); %tokyo (year=1998); %tokyo (year=1999); Gap_ACTmath=mean(of UR_ACTmath1994 UR_ACTmath1995 UR_ACTmath1996 UR_ACTmath1997 UR_ACTmath1998 UR_ACTmath1999); Gap_ACTsci=mean(of UR_ACTsci1994 UR_ACTsci1995 UR_ACTsci1996 UR_ACTsci1997 UR_ACTsci1998 UR_ACTsci1999); Gap_ACTtaker=mean(of UR_ACTtaker1994 UR_ACTtaker1995 UR_ACTtaker1996 UR_ACTtaker1997 UR_ACTtaker1998 UR_ACTtaker1999); /* proc print data=actalldata; title "Trends in Mathematics"; var city total_ACTmath1994 total_ACTmath1995 total_ACTmath1996 total_ACTmath1997 total_ACTmath1998 total_ACTmath1999 ; run; proc print data=actalldata; title "Trends in Science"; var city total_ACTsci1994 total_ACTsci1995 total_ACTsci1996 total_ACTsci1997 total_ACTsci1998 total_ACTsci1999 ; run; proc print data=actalldata; title "Trends in N of takers"; var city total_ACTtaker1994 total_ACTtaker1995 total_ACTtaker1996 total_ACTtaker1997 total_ACTtaker1998 total_ACTtaker1999 ; run; proc print data=actalldata; title "main derived statistics"; var city mean_ACTmath mean_ACTsci mean_ACTtaker growth_ACTmath growth_ACTsci growth_ACTtaker GAP_ACTmath GAP_ACTsci GAP_ACTtaker ;run; */ data ACTalldata; set ACTalldata; keep city total_ACTmath1994 total_ACTmath1995 total_ACTmath1996 total_ACTmath1997 total_ACTmath1998 total_ACTmath1999 total_ACTsci1994 total_ACTsci1995 total_ACTsci1996 total_ACTsci1997 total_ACTsci1998 total_ACTsci1999 total_ACTtaker1994 total_ACTtaker1995 total_ACTtaker1996 total_ACTtaker1997 total_ACTtaker1998 total_ACTtaker1999 mean_ACTmath mean_ACTsci mean_ACTtaker growth_ACTmath growth_ACTsci growth_ACTtaker GAP_ACTmath GAP_ACTsci GAP_ACTtaker; /*HERE, SAT DATA*/ /*HERE, SAT DATA*/ /*HERE, SAT DATA*/ /*Here, SAT data*/ PROC IMPORT OUT= SAT DATAFILE= "&dd.\USI SAT test scores.xls" DBMS=EXCEL2000 REPLACE; GETNAMES=YES; RUN; data sat;set sat; if city ne "Columbus_old"; ID=compress(trim(type) || trim(year)); *proc print data=act; *run; ************************************; /*CREATE CITY YEAR DATA*/ proc sort data=sat out=satX; by city year;run; /* proc print data=satX; run;*/ %macro these2 (stat=); data &stat._SAT;set satX; if type="&stat"; keep year city Male_&stat Female_&stat Native_&stat Asian_&stat Black_&stat Hispanic_&stat White_&stat Other_&stat No_Response_&stat Total_&stat; Male_&stat=Male; Female_&stat=Female; Native_&stat=Native; Asian_&stat=Asian; Black_&stat=Black; Hispanic_&stat=Hispanic; White_&stat=White; Other_&stat=Other; No_Response_&stat=No_Response; Total_&stat=Total; %mend these2; %these2 (stat=SATtaker); %these2 (stat=SATverb); %these2 (stat=SATmath); data SAT_CityYear; merge SATtaker_SAT SATverb_SAT SATmath_SAT ; by city year; run; data here.SAT_ACT_CityYear;merge SAT_CityYear ACT_CityYear; by city year; label Asian_ACTcompo ="Asian: ACT test score composit " Asian_ACTeng ="Asian: ACT English test score " Asian_ACTmath ="Asian: ACT Math test score " Asian_ACTread ="Asian: ACT Reading test score " Asian_ACTsci ="Asian: ACT Science test score " Asian_ACTtaker ="Asian: Number of ACT test takers " Asian_SATmath ="Asian: SAT Math test score " Asian_SATtaker ="Asian: Number of SAT test takers " Asian_SATverb ="Asian: SAT Verbal test score " Black_ACTcompo ="Black: ACT test score composit " Black_ACTeng ="Black: ACT English test score " Black_ACTmath ="Black: ACT Math test score " Black_ACTread ="Black: ACT Reading test score " Black_ACTsci ="Black: ACT Science test score " Black_ACTtaker ="Black: Number of ACT test takers " Black_SATmath ="Black: SAT Math test score " Black_SATtaker ="Black: Number of SAT test takers " Black_SATverb ="Black: SAT Verbal test score " Chicano_ACTcompo ="Chicano: ACT test score composit " Chicano_ACTeng ="Chicano: ACT English test score " Chicano_ACTmath ="Chicano: ACT Math test score " Chicano_ACTread ="Chicano: ACT Reading test score " Chicano_ACTsci ="Chicano: ACT Science test score " Chicano_ACTtaker ="Chicano: Number of ACT test takers " City ="City " Female_ACTcompo ="Female: ACT test score composit " Female_ACTeng ="Female: ACT English test score " Female_ACTmath ="Female: ACT Math test score " Female_ACTread ="Female: ACT Reading test score " Female_ACTsci ="Female: ACT Science test score " Female_ACTtaker ="Female: Number of ACT test takers " Female_SATmath ="Female: SAT Math test score " Female_SATtaker ="Female: Number of SAT test takers " Female_SATverb ="Female: SAT Verbal test score " Hispanic_ACTcompo ="Hispanic: ACT test score composit " Hispanic_ACTeng ="Hispanic: ACT English test score " Hispanic_ACTmath ="Hispanic: ACT Math test score " Hispanic_ACTread ="Hispanic: ACT Reading test score " Hispanic_ACTsci ="Hispanic: ACT Science test score " Hispanic_ACTtaker ="Hispanic: Number of ACT test takers " Hispanic_SATmath ="Hispanic: SAT Math test score " Hispanic_SATtaker ="Hispanic: Number of SAT test takers " Hispanic_SATverb ="Hispanic: SAT Verbal test score " Male_ACTcompo ="Male: ACT test score composit " Male_ACTeng ="Male: ACT English test score " Male_ACTmath ="Male: ACT Math test score " Male_ACTread ="Male: ACT Reading test score " Male_ACTsci ="Male: ACT Science test score " Male_ACTtaker ="Male: Number of ACT test takers " Male_SATmath ="Male: SAT Math test score " Male_SATtaker ="Male: Number of SAT test takers " Male_SATverb ="Male: SAT Verbal test score " Native_ACTcompo ="Native: ACT test score composit " Native_ACTeng ="Native: ACT English test score " Native_ACTmath ="Native: ACT Math test score " Native_ACTread ="Native: ACT Reading test score " Native_ACTsci ="Native: ACT Science test score " Native_ACTtaker ="Native: Number of ACT test takers " Native_SATmath ="Native: SAT Math test score " Native_SATtaker ="Native: Number of SAT test takers " Native_SATverb ="Native: SAT Verbal test score " No_Response_SATmath ="No Response: SAT math score " No_Response_SATtaker ="No Response: SAT test takers " No_Response_SATverb ="No Response: SAT verbal test score " Other_SATmath ="Others: SAT math score " Other_SATtaker ="Other: SAT test takers " Other_SATverb ="Other: SAT verbal test score " Total_ACTcompo ="ACT test score composit " Total_ACTeng ="ACT English test score " Total_ACTmath ="ACT Math test score " Total_ACTread ="ACT Reading test score " Total_ACTsci ="ACT Science test score " Total_ACTtaker ="Number of ACT test takers " Total_SATmath ="SAT Math test score " Total_SATtaker ="Number of SAT test takers " Total_SATverb ="SAT Verbal test score " White_ACTcompo ="White: ACT test score composit " White_ACTeng ="White: ACT English test score " White_ACTmath ="White: ACT Math test score " White_ACTread ="White: ACT Reading test score " White_ACTsci ="White: ACT Science test score " White_ACTtaker ="White: Number of ACT test takers " White_SATmath ="White: SAT Math test score " White_SATtaker ="White: Number of SAT test takers " White_SATverb ="White: SAT Verbal test score " Year ="Year " ; proc contents data=here.SAT_ACT_CityYear; title1 "Contents of SAT_ACT_CityYear"; title2 "Case=City Year"; run; /* proc means data=here.SAT_ACT_CityYear; title1 "Descriptive Statistics of SAT_ACT_CityYear"; title2 "Case=City Year"; run;*/ PROC EXPORT DATA= here.SAT_ACT_CityYear OUTFILE= "&WD.\SAT_ACT_CityYear.xls" DBMS=EXCEL2000 REPLACE;RUN; *************************************; proc sort data=sat out=satS; by city;run; %MACRO chicago (type=); proc transpose data=satS out=sat_&type;where type="&type";by city;id ID; var Male Female Native Asian Black Hispanic White Other No_Response Total ;run; %mend chicago; %chicago (type=SATtaker); %chicago (type=SATverb); %chicago (type=SATmath); data SATall;merge sat_SATtaker sat_SATverb sat_SATmath; by city; %macro michigan (male=); data &male.2;set SATall; keep city &Male._SATtaker1993 &Male._SATverb1993 &Male._SATmath1993 &Male._SATtaker1994 &Male._SATverb1994 &Male._SATmath1994 &Male._SATtaker1995 &Male._SATverb1995 &Male._SATmath1995 &Male._SATtaker1996 &Male._SATverb1996 &Male._SATmath1996 &Male._SATtaker1997 &Male._SATverb1997 &Male._SATmath1997 &Male._SATtaker1998 &Male._SATverb1998 &Male._SATmath1998 &Male._SATtaker1999 &Male._SATverb1999 &Male._SATmath1999 ; /*select if*/ if _name_="&Male"; &Male._SATtaker1993=SATtaker1993;&Male._SATverb1993=SATverb1993;&Male._SATmath1993=SATmath1993; &Male._SATtaker1994=SATtaker1994;&Male._SATverb1994=SATverb1994;&Male._SATmath1994=SATmath1994; &Male._SATtaker1995=SATtaker1995;&Male._SATverb1995=SATverb1995;&Male._SATmath1995=SATmath1995; &Male._SATtaker1996=SATtaker1996;&Male._SATverb1996=SATverb1996;&Male._SATmath1996=SATmath1996; &Male._SATtaker1997=SATtaker1997;&Male._SATverb1997=SATverb1997;&Male._SATmath1997=SATmath1997; &Male._SATtaker1998=SATtaker1998;&Male._SATverb1998=SATverb1998;&Male._SATmath1998=SATmath1998; &Male._SATtaker1999=SATtaker1999;&Male._SATverb1999=SATverb1999;&Male._SATmath1999=SATmath1999; %mend michigan; %michigan (male=Male); %michigan (male=Female); %michigan (male=Native); %michigan (male=Asian); %michigan (male=Black); %michigan (male=Hispanic); %michigan (male=White); %michigan (male=Other); %michigan (male=Total); data SATalldata;merge Male2 Female2 Native2 Asian2 Black2 Hispanic2 White2 Other2 Total2 ;by city; /*construct some variables here*/ /*mean math and verb score is prepared here*/ mean_SATmath=mean (of total_SATmath1993 total_SATmath1994 total_SATmath1995 total_SATmath1996 total_SATmath1997 total_SATmath1998 total_SATmath1999); mean_SATverb=mean (of total_SATverb1993 total_SATverb1994 total_SATverb1995 total_SATverb1996 total_SATverb1997 total_SATverb1998 total_SATverb1999); mean_SATtaker=mean (of total_SATtaker1993 total_SATtaker1994 total_SATtaker1995 total_SATtaker1996 total_SATtaker1997 total_SATtaker1998 total_SATtaker1999); /*growth*/ /*SATmath growth, mean of average annual growth*/ dif_total_SATmath1994=total_SATmath1994-total_SATmath1993; dif_total_SATmath1995=total_SATmath1995-total_SATmath1994; dif_total_SATmath1996=total_SATmath1996-total_SATmath1995; dif_total_SATmath1997=total_SATmath1997-total_SATmath1996; dif_total_SATmath1998=total_SATmath1998-total_SATmath1997; dif_total_SATmath1999=total_SATmath1999-total_SATmath1998; growth_SATmath=mean (of dif_total_SATmath1994 dif_total_SATmath1995 dif_total_SATmath1996 dif_total_SATmath1997 dif_total_SATmath1998 dif_total_SATmath1999); /*verb growth, mean of average annual growth*/ dif_total_SATverb1994=total_SATverb1994-total_SATverb1993; dif_total_SATverb1995=total_SATverb1995-total_SATverb1994; dif_total_SATverb1996=total_SATverb1996-total_SATverb1995; dif_total_SATverb1997=total_SATverb1997-total_SATverb1996; dif_total_SATverb1998=total_SATverb1998-total_SATverb1997; dif_total_SATverb1999=total_SATverb1999-total_SATverb1998; growth_SATverb=mean (of dif_total_SATverb1994 dif_total_SATverb1995 dif_total_SATverb1996 dif_total_SATverb1997 dif_total_SATverb1998 dif_total_SATverb1999); /*test taker growth, mean of average annual growth*/ dif_total_SATtaker1994=total_SATtaker1995-total_SATtaker1993; dif_total_SATtaker1995=total_SATtaker1995-total_SATtaker1994; dif_total_SATtaker1996=total_SATtaker1996-total_SATtaker1995; dif_total_SATtaker1997=total_SATtaker1997-total_SATtaker1996; dif_total_SATtaker1998=total_SATtaker1998-total_SATtaker1997; dif_total_SATtaker1999=total_SATtaker1999-total_SATtaker1998; growth_SATtaker=mean (of dif_total_SATtaker1994 dif_total_SATtaker1995 dif_total_SATtaker1996 dif_total_SATtaker1997 dif_total_SATtaker1998 dif_total_SATtaker1999); /*Gap between white and underrepresentative students*/ /*underrepresentative mean*/ /*To get the value for UR population, I am recaluculating a weight mean for the UR population, rather than using a mean of black score and hispanic score.*/ %macro tokyo (year); UR_SATmath&year=((black_SATmath&year*black_SATtaker&year)+(hispanic_SATmath&year*hispanic_SATtaker&year))/ sum(of black_SATtaker&year hispanic_SATtaker&year); Gap_SATmath&year=white_SATmath&year-UR_SATmath&year; UR_SATverb&year=((black_SATverb&year*black_SATtaker&year)+(hispanic_SATverb&year*hispanic_SATtaker&year))/ sum(of black_SATtaker&year hispanic_SATtaker&year); Gap_SATverb&year=white_SATverb&year-UR_SATverb&year; UR_SATtaker&year=((black_SATtaker&year*black_SATtaker&year)+(hispanic_SATtaker&year*hispanic_SATtaker&year))/ sum(of black_SATtaker&year hispanic_SATtaker&year); Gap_SATtaker&year=white_SATtaker&year-UR_SATtaker&year; %mend tokyo; %tokyo (year=1993); %tokyo (year=1994);%tokyo (year=1995);%tokyo (year=1996);%tokyo (year=1997);%tokyo (year=1998); %tokyo (year=1999); Gap_SATmath=mean(of UR_SATmath1993 UR_SATmath1994 UR_SATmath1995 UR_SATmath1996 UR_SATmath1997 UR_SATmath1998 UR_SATmath1999); Gap_SATverb=mean(of UR_SATverb1993 UR_SATverb1994 UR_SATverb1995 UR_SATverb1996 UR_SATverb1997 UR_SATverb1998 UR_SATverb1999); Gap_SATtaker=mean(of UR_SATtaker1993 UR_SATtaker1994 UR_SATtaker1995 UR_SATtaker1996 UR_SATtaker1997 UR_SATtaker1998 UR_SATtaker1999); /* proc print data=SATalldata; title "Trends in Mathematics"; var city total_SATmath1993 total_SATmath1994 total_SATmath1995 total_SATmath1996 total_SATmath1997 total_SATmath1998 total_SATmath1999 ; run; proc print data=SATalldata; title "Trends in verbal"; var city total_SATverb1993 total_SATverb1994 total_SATverb1995 total_SATverb1996 total_SATverb1997 total_SATverb1998 total_SATverb1999 ; run; proc print data=SATalldata; title "Trends in N of takers"; var city total_SATtaker1993 total_SATtaker1994 total_SATtaker1995 total_SATtaker1996 total_SATtaker1997 total_SATtaker1998 total_SATtaker1999 ; run; proc print data=SATalldata; title "main derived statistics"; var city mean_SATmath mean_SATverb mean_SATtaker growth_SATmath growth_SATverb growth_SATtaker GAP_SATmath GAP_SATverb GAP_SATtaker; run; */ data SATalldata; set SATalldata; keep city total_SATmath1993 total_SATmath1994 total_SATmath1995 total_SATmath1996 total_SATmath1997 total_SATmath1998 total_SATmath1999 total_SATverb1993 total_SATverb1994 total_SATverb1995 total_SATverb1996 total_SATverb1997 total_SATverb1998 total_SATverb1999 mean_SATmath mean_SATverb mean_SATtaker growth_SATmath growth_SATverb growth_SATtaker GAP_SATmath GAP_SATverb GAP_SATtaker; /*MERGE SAT and ACT*/ data here.SAT_ACT_City; merge satalldata actalldata;by city; PROC EXPORT DATA= here.SAT_ACT_City OUTFILE= "&WD.\SAT_ACT_City.xls" DBMS=EXCEL2000 REPLACE;RUN; /*FROM HERE, RUBRIC data*/ /*FROM HERE, RUBRIC data*/ /*FROM HERE, RUBRIC data*/ /*this inports rubric data*/ PROC IMPORT OUT= rubric DATAFILE= "&dd.\USI rubric variables.xls" DBMS=EXCEL2000 REPLACE; GETNAMES=YES; RUN; ods listing; data rubric1;length city $ 15; set rubric; data rubric2;set rubric1; *keep city /* cohort*/ Rubric1_1 Rubric1_2 Rubric1_2 Rubric2_1 Rubric2_2 Rubric3_1 Rubric3_2 Rubric3_3 Rubric4_1 Rubric4_2 Rubric4_3 Rubric5_1 Rubric5_2 Rubric5_3 Rubric6_1 Rubric6_2 Rubric7_1 Rubric7_2 Rubric7_3 Rubric8_1 Rubric8_2 Rubric8_3 Rubric8_4 Rubric9_1 Rubric9_2 Rubric9_3 Rubric9_4 Rubric10_1 Rubric10_2 Rubric10_3 Rubric11_1 Rubric11_2 Rubric11_3 Rubric11_4 Rubric11_5 Rubric12_1 Rubric12_2 Rubric12_3 Rubric12_4 Rubric12_5 Rubric13_1 Rubric13_2 Rubric13_3 Rubric14_1 Rubric14_2 Rubric14_3 Rubric14_4 ; %let func=mean;/*I use mean instead of SUM here because sometimes only two person's ratings are available.*/ Rubric1_1 =&func (of R1_1linda R1_1greta R1_1ana); Rubric1_2 =&func ( of R1_2linda R1_2greta R1_2ana ); Rubric2_1 =&func ( of R2_1linda R2_1greta R2_1ana ); Rubric2_2 =&func ( of R2_2linda R2_2greta R2_2ana ); Rubric3_1 =&func ( of R3_1linda R3_1greta R3_1ana ); Rubric3_2 =&func ( of R3_2linda R3_2greta R3_2ana ); Rubric3_3 =&func ( of R3_3linda R3_3greta R3_3ana ); Rubric4_1 =&func ( of R4_1linda R4_1greta R4_1ana ); Rubric4_2 =&func ( of R4_2linda R4_2greta R4_2ana ); Rubric4_3 =&func ( of R4_3linda R4_3greta R4_3ana ); Rubric5_1 =&func ( of R5_1linda R5_1greta R5_1ana ); Rubric5_2 =&func ( of R5_2linda R5_2greta R5_2ana ); Rubric5_3 =&func ( of R5_3linda R5_3greta R5_3ana ); Rubric6_1 =&func ( of R6_1linda R6_1greta R6_1ana ); Rubric6_2 =&func ( of R6_2linda R6_2greta R6_2ana ); Rubric7_1 =&func ( of R7_1linda R7_1greta R7_1ana ); Rubric7_2 =&func ( of R7_2linda R7_2greta R7_2ana ); Rubric7_3 =&func ( of R7_3linda R7_3greta R7_3ana ); Rubric8_1 =&func ( of R8_1linda R8_1greta R8_1ana ); Rubric8_2 =&func ( of R8_2linda R8_2greta R8_2ana ); Rubric8_3 =&func ( of R8_3linda R8_3greta R8_3ana ); Rubric8_4 =&func ( of R8_4linda R8_4greta R8_4ana ); Rubric9_1 =&func ( of R9_1linda R9_1greta R9_1ana ); Rubric9_2 =&func ( of R9_2linda R9_2greta R9_2ana ); Rubric9_3 =&func ( of R9_3linda R9_3greta R9_3ana ); Rubric9_4 =&func ( of R9_4linda R9_4greta R9_4ana ); Rubric10_1 =&func ( of R10_1linda R10_1greta R10_1ana ); Rubric10_2 =&func ( of R10_2linda R10_2greta R10_2ana ); Rubric10_3 =&func ( of R10_3linda R10_3greta R10_3ana ); Rubric11_1 =&func ( of R11_1linda R11_1greta R11_1ana ); Rubric11_2 =&func ( of R11_2linda R11_2greta R11_2ana ); Rubric11_3 =&func ( of R11_3linda R11_3greta R11_3ana ); Rubric11_4 =&func ( of R11_4linda R11_4greta R11_4ana ); Rubric11_5 =&func ( of R11_5linda R11_5greta R11_5ana ); Rubric12_1 =&func ( of R12_1linda R12_1greta R12_1ana ); Rubric12_2 =&func ( of R12_2linda R12_2greta R12_2ana ); Rubric12_3 =&func ( of R12_3linda R12_3greta R12_3ana ); Rubric12_4 =&func ( of R12_4linda R12_4greta R12_4ana ); Rubric12_5 =&func ( of R12_5linda R12_5greta R12_5ana ); Rubric13_1 =&func ( of R13_1linda R13_1greta R13_1ana ); Rubric13_2 =&func ( of R13_2linda R13_2greta R13_2ana ); Rubric13_3 =&func ( of R13_3linda R13_3greta R13_3ana ); Rubric14_1 =&func ( of R14_1linda R14_1greta R14_1ana ); Rubric14_2 =&func ( of R14_2linda R14_2greta R14_2ana ); Rubric14_3 =&func ( of R14_3linda R14_3greta R14_3ana ); Rubric14_4 =&func ( of R14_4linda R14_4greta R14_4ana ); /*forcing non-01 variables to range from 0 to 1*/ RUBRIC9_1=RUBRIC9_1/100; RUBRIC5_3=RUBRIC5_3/2; if city ne " "; /*you can standardize using the below lines*/ proc standard data=rubric2 out=rubric_standard1 /*mean=0 std=1*/; VAR Rubric1_1 Rubric1_2 Rubric1_2 Rubric2_1 Rubric2_2 Rubric3_1 Rubric3_2 Rubric3_3 Rubric4_1 Rubric4_2 Rubric4_3 Rubric5_1 Rubric5_2 Rubric5_3 Rubric6_1 Rubric6_2 Rubric7_1 Rubric7_2 Rubric7_3 Rubric8_1 Rubric8_2 Rubric8_3 Rubric8_4 Rubric9_1 Rubric9_2 Rubric9_3 Rubric9_4 Rubric10_1 Rubric10_2 Rubric10_3 Rubric11_1 Rubric11_2 Rubric11_3 Rubric11_4 Rubric11_5 Rubric12_1 Rubric12_2 Rubric12_3 Rubric12_4 Rubric12_5 Rubric13_1 Rubric13_2 Rubric13_3 Rubric14_1 Rubric14_2 Rubric14_3 Rubric14_4; run; data rubric_standard2;set rubric_standard1; *keep city Rubric1 Rubric2 Rubric3 Rubric4 Rubric5 Rubric6 Rubric7 Rubric8 Rubric9 Rubric10 Rubric11 Rubric12 Rubric13 Rubric14 Rubric1and3 R1_Curriculum_and_Instruction R2_Assessment R3_Student_Support R4_Teachers R5_Leadership ; Rubric1=mean(of Rubric1_1 Rubric1_2); Rubric2=mean(of Rubric2_1 Rubric2_2 ); Rubric3=mean(of Rubric3_1 Rubric3_2 Rubric3_3); Rubric4=mean( of Rubric4_1 Rubric4_2 Rubric4_3); Rubric5=mean( of Rubric5_1 Rubric5_2 Rubric5_3); Rubric6=mean( of Rubric6_1 Rubric6_2 ); Rubric7=mean( of Rubric7_1 Rubric7_2 Rubric7_3); Rubric8=mean(of Rubric8_1 Rubric8_2 Rubric8_3 Rubric8_4); Rubric9=mean(of Rubric9_1 Rubric9_2 Rubric9_3 Rubric9_4); Rubric10=mean(of Rubric10_1 Rubric10_2 Rubric10_3); Rubric11=mean(of Rubric11_1 Rubric11_2 Rubric11_3 Rubric11_4 Rubric11_5 ); Rubric12=mean(of Rubric12_1 Rubric12_2 Rubric12_3 Rubric12_4 Rubric12_5 ); Rubric13=mean(of Rubric13_1 Rubric13_2 Rubric13_3 ); Rubric14=mean(of Rubric14_1 Rubric14_2 Rubric14_3 Rubric14_4); Rubric1and3=mean(of Rubric1_1 Rubric1_2 Rubric3_1 Rubric3_2 Rubric3_3); Rubric2and4=mean(of Rubric2_1 Rubric2_2 Rubric4_1 Rubric4_2 Rubric4_3); /*new entry*/ Rubric8and9=mean(of Rubric8_1 Rubric8_2 Rubric8_3 Rubric8_4 Rubric9_1 Rubric9_2 Rubric9_3 Rubric9_4); R1_Curriculum_and_Instruction =mean(of Rubric1_1 Rubric5_1 Rubric5_2 Rubric6_1 Rubric12_4 Rubric12_5); R2_Assessment =mean(of Rubric3_3 Rubric12_2 Rubric12_3 Rubric13_1 Rubric13_2 Rubric13_3); R3_Student_Support =mean(of Rubric2_1 Rubric2_2 Rubric4_2 Rubric4_3 Rubric6_2 Rubric11_5); R4_Teachers =mean(of Rubric7_1 Rubric7_2 Rubric7_3 Rubric8_1 Rubric8_2 Rubric8_3 Rubric8_4 Rubric9_2 Rubric9_3 Rubric9_4); R5_Leadership =mean(of Rubric5_3 Rubric10_1 Rubric10_2 Rubric10_3 Rubric11_2 Rubric11_4 Rubric14_2); *proc sort; *by city; *run; label City =" City " Cohort =" Cohort " R1_1ana ="Rater 1: Elimination of Tracking " R1_1greta ="Rater 2: Elimination of Tracking " R1_1linda ="Rater 3: Elimination of Tracking " R1_2ana ="Rater 1: Upper Level Courses " R1_2greta ="Rater 2: Upper Level Courses " R1_2linda ="Rater 3: Upper Level Courses " R10_1ana ="Rater 1: Superintendent's Leadership and Tenure " R10_1greta ="Rater 2: Superintendent's Leadership and Tenure " R10_1linda ="Rater 3: Superintendent's Leadership and Tenure " R10_2ana ="Rater 1: USI Project Director's Position/Tenure in District Organizational Structure " R10_2greta ="Rater 2: USI Project Director's Position/Tenure in District Organizational Structure " R10_2linda ="Rater 3: USI Project Director's Position/Tenure in District Organizational Structure " R10_3ana ="Rater 1: USI Staff " R10_3greta ="Rater 2: USI Staff " R10_3linda ="Rater 3: USI Staff " R11_1ana ="Rater 1: Collaboration with Other Initiatives " R11_1greta ="Rater 2: Collaboration with Other Initiatives " R11_1linda ="Rater 3: Collaboration with Other Initiatives " R11_2ana ="Rater 1: Community or Parental Involvement " R11_2greta ="Rater 2: Community or Parental Involvement " R11_2linda ="Rater 3: Community or Parental Involvement " R11_3ana ="Rater 1: Partnerships with Higher Education " R11_3greta ="Rater 2: Partnerships with Higher Education " R11_3linda ="Rater 3: Partnerships with Higher Education " R11_4ana ="Rater 1: Partnerships with Industry and Business " R11_4greta ="Rater 2: Partnerships with Industry and Business " R11_4linda ="Rater 3: Partnerships with Industry and Business " R11_5ana ="Rater 1: Partnerships Directly Linked to Students " R11_5greta ="Rater 2: Partnerships Directly Linked to Students " R11_5linda ="Rater 3: Partnerships Directly Linked to Students " R12_1ana ="Rater 1: Required State Assessment " R12_1greta ="Rater 2: Required State Assessment " R12_1linda ="Rater 3: Required State Assessment " R12_2ana ="Rater 1: Local Standards-based Assessment Measures " R12_2greta ="Rater 2: Local Standards-based Assessment Measures " R12_2linda ="Rater 3: Local Standards-based Assessment Measures " R12_3ana ="Rater 1: Other Standard Measures Used " R12_3greta ="Rater 2: Other Standard Measures Used " R12_3linda ="Rater 3: Other Standard Measures Used " R12_4ana ="Rater 1: Using Assessment for Planning Instruction " R12_4greta ="Rater 2: Using Assessment for Planning Instruction " R12_4linda ="Rater 3: Using Assessment for Planning Instruction " R12_5ana ="Rater 1: Use of Student Test Results for Curriculum and Instruction Enhancement " R12_5greta ="Rater 2: Use of Student Test Results for Curriculum and Instruction Enhancement " R12_5linda ="Rater 3: Use of Student Test Results for Curriculum and Instruction Enhancement " R13_1ana ="Rater 1: Report Card System for Stakeholders " R13_1greta ="Rater 2: Report Card System for Stakeholders " R13_1linda ="Rater 3: Report Card System for Stakeholders " R13_2ana ="Rater 1: Data Collection Infrastructure " R13_2greta ="Rater 2: Data Collection Infrastructure " R13_2linda ="Rater 3: Data Collection Infrastructure " R13_3ana ="Rater 1: Use of Key Indicator Data for USI Program Interventions " R13_3greta ="Rater 2: Use of Key Indicator Data for USI Program Interventions " R13_3linda ="Rater 3: Use of Key Indicator Data for USI Program Interventions " R14_1ana ="Rater 1: Coordination Among Existing Funding Sources " R14_1greta ="Rater 2: Coordination Among Existing Funding Sources " R14_1linda ="Rater 3: Coordination Among Existing Funding Sources " R14_2ana ="Rater 1: Leverage of Funds " R14_2greta ="Rater 2: Leverage of Funds " R14_2linda ="Rater 3: Leverage of Funds " R14_3ana ="Rater 1: Use of Research Results " R14_3greta ="Rater 2: Use of Research Results " R14_3linda ="Rater 3: Use of Research Results " R14_4ana ="Rater 1: Technology and Telecommunications " R14_4greta ="Rater 2: Technology and Telecommunications " R14_4linda ="Rater 3: Technology and Telecommunications " R2_1ana ="Rater 1: Strategies to Address Needs of Special Populations " R2_1greta ="Rater 2: Strategies to Address Needs of Special Populations " R2_1linda ="Rater 3: Strategies to Address Needs of Special Populations " R2_2ana ="Rater 1: Targeted Programs for Underrepresented Minorities " R2_2greta ="Rater 2: Targeted Programs for Underrepresented Minorities " R2_2linda ="Rater 3: Targeted Programs for Underrepresented Minorities " R3_1ana ="Rater 1: Graduation Requirements More Rigorous Than the State " R3_1greta ="Rater 2: Graduation Requirements More Rigorous Than the State " R3_1linda ="Rater 3: Graduation Requirements More Rigorous Than the State " R3_2ana ="Rater 1: Graduation Requirements of at Least 3 Years Math and Science " R3_2greta ="Rater 2: Graduation Requirements of at Least 3 Years Math and Science " R3_2linda ="Rater 3: Graduation Requirements of at Least 3 Years Math and Science " R3_3ana ="Rater 1: Promotion Policy " R3_3greta ="Rater 2: Promotion Policy " R3_3linda ="Rater 3: Promotion Policy " R4_1ana ="Rater 1: Attendance Policy " R4_1greta ="Rater 2: Attendance Policy " R4_1linda ="Rater 3: Attendance Policy " R4_2ana ="Rater 1: Summer School " R4_2greta ="Rater 2: Summer School " R4_2linda ="Rater 3: Summer School " R4_3ana ="Rater 1: Tutoring or Saturday Academy " R4_3greta ="Rater 2: Tutoring or Saturday Academy " R4_3linda ="Rater 3: Tutoring or Saturday Academy " R5_1ana ="Rater 1: District Curriculum in Place in Math and Science " R5_1greta ="Rater 2: District Curriculum in Place in Math and Science " R5_1linda ="Rater 3: District Curriculum in Place in Math and Science " R5_2ana ="Rater 1: Curriculum Linked to State or National Content Standards " R5_2greta ="Rater 2: Curriculum Linked to State or National Content Standards " R5_2linda ="Rater 3: Curriculum Linked to State or National Content Standards " R5_3ana ="Rater 1: Significant and Increasing Number of Schools Implementing Standards-based Curriculum or Materials " R5_3greta ="Rater 2: Significant and Increasing Number of Schools Implementing Standards-based Curriculum or Materials " R5_3linda ="Rater 3: Significant and Increasing Number of Schools Implementing Standards-based Curriculum or Materials " R6_1ana ="Rater 1: Assessment Adequately Aligned to Curriculum and Standards " R6_1greta ="Rater 2: Assessment Adequately Aligned to Curriculum and Standards " R6_1linda ="Rater 3: Assessment Adequately Aligned to Curriculum and Standards " R6_2ana ="Rater 1: More Instructional Time in Math and Science " R6_2greta ="Rater 2: More Instructional Time in Math and Science " R6_2linda ="Rater 3: More Instructional Time in Math and Science " R7_1ana ="Rater 1: Policies Guiding Teacher Assignments " R7_1greta ="Rater 2: Policies Guiding Teacher Assignments " R7_1linda ="Rater 3: Policies Guiding Teacher Assignments " R7_2ana ="Rater 1: Uncertified Teachers Encouraged to Seek Certification " R7_2greta ="Rater 2: Uncertified Teachers Encouraged to Seek Certification " R7_2linda ="Rater 3: Uncertified Teachers Encouraged to Seek Certification " R7_3ana ="Rater 1: Teacher Certification Required by the District " R7_3greta ="Rater 2: Teacher Certification Required by the District " R7_3linda ="Rater 3: Teacher Certification Required by the District " R8_1ana ="Rater 1: Reward for Increasing Content Area Knowledge " R8_1greta ="Rater 2: Reward for Increasing Content Area Knowledge " R8_1linda ="Rater 3: Reward for Increasing Content Area Knowledge " R8_2ana ="Rater 1: Professional Development Content " R8_2greta ="Rater 2: Professional Development Content " R8_2linda ="Rater 3: Professional Development Content " R8_3ana ="Rater 1: Aligning Professional Development with Standards " R8_3greta ="Rater 2: Aligning Professional Development with Standards " R8_3linda ="Rater 3: Aligning Professional Development with Standards " R8_4ana ="Rater 1: Professional Development Offerings " R8_4greta ="Rater 2: Professional Development Offerings " R8_4linda ="Rater 3: Professional Development Offerings " R9_1ana ="Rater 1: PD Participation " R9_1greta ="Rater 2: PD Participation " R9_1linda ="Rater 3: PD Participation " R9_2ana ="Rater 1: Classroom Observation " R9_2greta ="Rater 2: Classroom Observation " R9_2linda ="Rater 3: Classroom Observation " R9_3ana ="Rater 1: Evaluation by Analysis of Student Scores " R9_3greta ="Rater 2: Evaluation by Analysis of Student Scores " R9_3linda ="Rater 3: Evaluation by Analysis of Student Scores " R9_4ana ="Rater 1: Professional Development Follow-Up " R9_4greta ="Rater 2: Professional Development Follow-Up " R9_4linda ="Rater 3: Professional Development Follow-Up " Rubric1 ="Equal Access " Rubric1_1 ="Elimination of Tracking " Rubric1_2 =" Upper Level Courses " Rubric10 ="Leadership, Governance and Management " Rubric10_1 ="Superintendent's Leadership and Tenure " Rubric10_2 ="USI Project Director's Position/Tenure in District Organizational Structure " Rubric10_3 ="USI Staff " Rubric11 ="Collaboration, Partnerships with Parents, Higher Education, Business and Partners for Students " Rubric11_1 ="Collaboration with Other Initiatives " Rubric11_2 ="Community or Parental Involvement " Rubric11_3 ="Partnerships with Higher Education " Rubric11_4 ="Partnerships with Industry and Business " Rubric11_5 ="Partnerships Directly Linked to Students " Rubric12 ="Assessment " Rubric12_1 ="Required State Assessment " Rubric12_2 ="Local Standards-based Assessment Measures " Rubric12_3 ="Other Standard Measures Used " Rubric12_4 ="Using Assessment for Planning Instruction " Rubric12_5 ="Use of Student Test Results for Curriculum and Instruction Enhancement " Rubric13 ="Accountability and Use of District Data " Rubric13_1 ="Report Card System for Stakeholders " Rubric13_2 ="Data Collection Infrastructure " Rubric13_3 ="Use of Key Indicator Data for USI Program Interventions " Rubric14 ="Coordination, Leverage of Resources, Use of Data and Technology " Rubric14_1 ="Coordination Among Existing Funding Sources " Rubric14_2 ="Leverage of Funds " Rubric14_3 ="Use of Research Results " Rubric14_4 ="Technology and Telecommunications " Rubric1and3 ="Rubric 1 and 3 combined " Rubric2 ="Student Support Programs " Rubric2_1 ="Strategies to Address Needs of Special Populations " Rubric2_2 ="Targeted Programs for Underrepresented Minorities " Rubric2and4 ="Rubric 2 and 4 combined " Rubric3 ="Graduation and Promotion Policy " Rubric3_1 ="Graduation Requirements More Rigorous Than the State " Rubric3_2 ="Graduation Requirements of at Least 3 Years Math and Science " Rubric3_3 ="Promotion Policy " Rubric4 ="Attendance and Safety Net Programs " Rubric4_1 ="Attendance Policy " Rubric4_2 ="Summer School " Rubric4_3 ="Tutoring or Saturday Academy " Rubric5 ="Standards Based Curriculum " Rubric5_1 ="District Curriculum in Place in Math and Science " Rubric5_2 ="Curriculum Linked to State or National Content Standards " Rubric5_3 ="Significant and Increasing Number of Schools Implementing Standards-based Curriculum or Materials " Rubric6 ="Assessment and M/S Instructional Time " Rubric6_1 ="Assessment Adequately Aligned to Curriculum and Standards " Rubric6_2 ="More Instructional Time in Math and Science " Rubric7 ="Teacher Qualifications " Rubric7_1 ="Policies Guiding Teacher Assignments " Rubric7_2 ="Uncertified Teachers Encouraged to Seek Certification " Rubric7_3 =" Teacher Certification Required by the District " Rubric8 ="Professional Development: Content Knowledge and Alignment with Standards " Rubric8_1 ="Reward for Increasing Content Area Knowledge " Rubric8_2 ="Professional Development Content " Rubric8_3 ="Aligning Professional Development with Standards " Rubric8_4 ="Professional Development Offerings " Rubric9 ="Professional Development Participation and Evaluation " Rubric9_1 ="PD Participation " Rubric9_2 ="Classroom Observation " Rubric9_3 ="Evaluation by Analysis of Student Scores " Rubric9_4 ="Professional Development Follow-Up " R1_Curriculum_and_Instruction ="Factor 1 Curriculum & Instruction (1.1,5.1,5.2,6.1,12.4,12.5;Reliability .697) " R2_Assessment ="Factor 2 Assessment (3.3,12.2,12.3,13.1,13.2,13.3;Reliability .716) " R3_Student_Support ="Factor 3 Student Support (2.1,2.2,4.2,4.3,6.2,11.5; Reliability .680) " R4_Teachers ="Factor 4 Qualifications And Professional Development (7.1,7.2,7.3,8.1,8.2,8.3,8.4,9.2,9.3,9.4;Reliability .691) " R5_Leadership ="Factor 5 Leadership And Management (5.3,10.1,10.2,10.3,11.2,11.4,14.2;Reliability .727) " Rubric8and9 ="Rubric 8 and 9 combined " ; run; proc standard data=rubric_standard2 out=rubric_standard2b /*mean=0 std=1*/; VAR Rubric1 Rubric2 Rubric3 Rubric4 Rubric5 Rubric6 Rubric7 Rubric8 Rubric9 Rubric10 Rubric11 Rubric12 Rubric13 Rubric14 Rubric1and3 Rubric2and4 R1_Curriculum_and_Instruction R2_Assessment R3_Student_Support R4_Teachers R5_Leadership ; run; proc sort data=rubric_standard2b out=here.rubric_City;by city;run; /*proc means data=here.rubric_City mean min max std maxdec=2; title "Rubric variables, summary statistics"; run;*/ proc contents data=here.rubric_City; title1 "Contents of rubric_city data"; title2 "Case=City"; run; /* proc means data=here.rubric_City; title1 "Descriptive Statistics of rubric_city data"; title2 "Case=City"; run; */ PROC EXPORT DATA= here.rubric_City OUTFILE= "&WD.\rubric_City.xls" DBMS=EXCEL2000 REPLACE;RUN; /* R1_1linda R1_2linda R1_1greta R1_2greta R1_1ana R1_2ana R2_1linda R2_2linda R2_1greta R2_2greta R2_1ana R2_2ana R3_1linda R3_2linda R3_3linda R3_1greta R3_2greta R3_3greta R3_1ana R3_2ana R3_3ana R4_1linda R4_2linda R4_3linda R4_1greta R4_2greta R4_3greta R4_1ana R4_2ana R4_3ana R5_1linda R5_2linda R5_3linda R5_1greta R5_2greta R5_3greta R5_1ana R5_2ana R5_3ana R6_1linda R6_2linda R6_1greta R6_2greta R6_1ana R6_2ana R7_1linda R7_2linda R7_3linda R7_1greta R7_2greta R7_3greta R7_1ana R7_2ana R7_3ana R8_1linda R8_2linda R8_3linda R8_4linda R8_1greta R8_2greta R8_3greta R8_4greta R8_1ana R8_2ana R8_3ana R8_4ana R9_1linda R9_2linda R9_3linda R9_4linda R9_1greta R9_2greta R9_3greta R9_4greta R9_1ana R9_2ana R9_3ana R9_4ana R10_1linda R10_2linda R10_3linda R10_1reta R10_2greta R10_3greta R10_1ana R10_2ana R10_3ana R11_1linda R11_2linda R11_3linda R11_4linda R11_5linda R11_1greta R11_2greta R11_3greta R11_4greta R11_5greta R11_1ana R11_2ana R11_3ana R11_4ana R11_5ana R12_1linda R12_2linda R12_3linda R12_4linda R12_5linda R12_1greta R12_2greta R12_3greta R12_4greta R12_5greta R12_1ana R12_2ana R12_3ana R12_4ana R12_5ana R13_1linda R13_2linda R13_3linda R13_1greta R13_2greta R13_3greta R13_1ana R13_2ana R13_3ana R14_1linda R14_2linda R14_3linda R14_4linda R14_1greta R14_2greta R14_3greta R14_4greta R14_1ana R14_2ana R14_3ana R14_4ana */ /*FROM HERE, KIDS data*/ /*FROM HERE, KIDS data*/ /*FROM HERE, KIDS data*/ /*FROM HERE, KIDS data*/ /*this imports excell data*/ PROC IMPORT OUT= enroll1 DATAFILE= "&dd.\USI math and science enrollment.xls" DBMS=EXCEL2000 REPLACE; GETNAMES=YES; RUN; /*the following is to get rid of redundant data as a subgroup TOTAL occurs twice. The following get rid of the second occurance. */ /*This may be a problem if totals coming from gender and total coming from race are different. The first one is from gender and this is what Linda recommends that we use. For some reasons, Detroit's 7th year and Phenix's 4th year had no total for gender. I borrowed this info from their racial total.*/ /*'nodupkey' means no duplicate key--suppresses duplicate (or redundant) observations. Without this, PROC SORT would just sort the data by BY-variables*/ proc sort nodupkey data=enroll1 out=enroll2; by city when subgroups; run; data enroll3; length city $ 15; set enroll2; if city ne ' ';/*takes observations that are entered in the excell sheet.*/ if when > -999; /*these, I am eliminating because they are older versions of data. I did not eliminate these until this point, so I don't get confused which city is not renewed*/ if city ne "Atlanta_old"; if city ne "New York_old"; if city ne "Memphis_old"; if city ne "Columbus_old"; if city ne "Los Angeles_old"; if city ne "Baltimore_old"; if city ne "Hypothetical"; /*ALTHOUGH THERE IS AN EXCEPTION TO THIS, whend data is 0, we assume that data is not entered. For example, Chicago data has 0 for completion. I replace these 0s with '.' (missing value) so 0 does not get into the demonator of a division, causing a zero division problem*/ if when = 0 then when=-999;/*I dont want this variable to be replace by 0 as 0 has a meaning (=base year)*/ array sandysandy _numeric_ ;do over sandysandy;/*giving a nickname 'assume' to all numeric variables in the data*/ if sandysandy =0 then sandysandy=.;/*this line replaces all 0s in numeric variables with dots that mean 'missing' in SAS*/ sandysandy=int(sandysandy); /*this line above gets integer of values. This solves a problem that some stats in the data has dismal points. Rounding would be better, though.*/ end; if when = -999 then when=0; /*According to Linda, Chicago and New York's 0 for algebra 1 classes are real*/ /*I process this not here, but dow below. Find a line where I do the following: if city = "New York" or city ="Chicago" then do; total_alge1E=0; end; */ %macro john (subgroupname=,XXX=); data &XXX;set enroll3; keep city when /*&XXX._subgroups */ &XXX._grade8 &XXX._grade9 &XXX._grade10 &XXX._grade11 &XXX._grade12 &XXX._alge1E &XXX._alge1C &XXX._alge2E &XXX._alge2C &XXX._geomE &XXX._geomC &XXX._calcE &XXX._calcC &XXX._totalmathE &XXX._totalmathC &XXX._bio1E &XXX._bio1C &XXX._chem1E &XXX._chem1C &XXX._phy1E &XXX._phy1C &XXX._allsciE &XXX._allsciC ; if subgroups =&subgroupname; *&XXX._city = city ; *&XXX._when = when ; *&XXX._subgroups = subgroups ; &XXX._grade8 = grade8 ; &XXX._grade9 = grade9 ; &XXX._grade10 = grade10 ; &XXX._grade11 = grade11 ; &XXX._grade12 = grade12 ; &XXX._alge1E = alge1E ; &XXX._alge1C = alge1C ; &XXX._alge2E = alge2E ; &XXX._alge2C = alge2C ; &XXX._geomE = geomE ; &XXX._geomC = geomC ; &XXX._calcE = calcE ; &XXX._calcC = calcC ; &XXX._totalmathE = totalmathE ; &XXX._totalmathC = totalmathC ; &XXX._bio1E = bio1E ; &XXX._bio1C = bio1C ; &XXX._chem1E = chem1E ; &XXX._chem1C = chem1C ; &XXX._phy1E = phy1E ; &XXX._phy1C = phy1C ; &XXX._allsciE = allsciE ; &XXX._allsciC = allsciC ; %mend john ; %john (subgroupname="Total",XXX=Total); %john (subgroupname="Male",XXX=Male); %john (subgroupname="Female",XXX=Female); %john (subgroupname="American Indian/Alaskan Native",XXX=Native); %john (subgroupname="Asian/Pacific Islander",XXX=Asian); %john (subgroupname="Black (Not Hispanic)",XXX=Black); %john (subgroupname="Hispanic",XXX=Hispanic); %john (subgroupname="White (Not Hispanic)",XXX=White); %john (subgroupname="Black",XXX=Other); data alldata;merge Total Male Female Native Asian Black Hispanic White Other; by city when ; /*here I am assigning 0 to New York and Chicago's algebra 1 enrollment*/ /*earlier, I replaced their 0 with dots, which significant missing value, for a convinience sake, but now I put real 0 back*/ if city = "New York" or city ="Chicago" then do; if total_alge1E=. then total_alge1E=0; end; /*get actual year data is about... varies depeding on cohort*/ if city= 'Hypothetical' or city='Chicago' or city='Baltimore' or city='Dallas' or city='Detroit' or city='El Paso' or city='Miami' or city='New York' or city='Phoenix' then do; /*give various cohort names for later convinience*/ cohort=1; cohort2=93; cohort_name="Cohort 93"; end; if city='Cleveland' or city='Columbus' or city='Fresno' or city='Los Angeles' or city='Memphis' or city='New Orleans' or city='Philadelphia' then do; cohort=2; cohort2=94; cohort_name="Cohort 94"; end; if city='Milwaukee' or city= 'St. Louis' or city= 'San Antonio' or city= 'San Diego' then do; cohort=3; cohort2=95; cohort_name="Cohort 95"; end; if city='Atlanta' or city='Jacksonville' then do; cohort=4; cohort2=97; cohort_name="Cohort 97"; end; if city='Houston' then do; cohort=5; cohort2=99; cohort_name="Cohort 99"; end; if cohort=1 then year=1993+when; if cohort=2 then year=1994+when; if cohort=3 then year=1995+when; if cohort=4 then year=1997+when; if cohort=5 then year=1999+when; /*derive statistics for underrepresentative group=sum of hispanic and black population*/ /*Indian added Jan 22, 2002*/ /*what happens when indian value is missing*/ %let ana=Black; %let dave=Hispanic; %let dave2=Native; UR_grade8 =sum(of &ana._grade8 &dave._grade8 &dave2._grade8 ); UR_grade9 =sum(of &ana._grade9 &dave._grade9 &dave2._grade9 ); UR_grade10 =sum(of &ana._grade10 &dave._grade10 &dave2._grade10 ); UR_grade11 =sum(of &ana._grade11 &dave._grade11 &dave2._grade11 ); UR_grade12 =sum(of &ana._grade12 &dave._grade12 &dave2._grade12 ); UR_alge1E =sum(of &ana._alge1E &dave._alge1E &dave2._alge1E ); UR_alge1C =sum(of &ana._alge1C &dave._alge1C &dave2._alge1C ); UR_alge2E =sum(of &ana._alge2E &dave._alge2E &dave2._alge2E ); UR_alge2C =sum(of &ana._alge2C &dave._alge2C &dave2._alge2C ); UR_geomE =sum(of &ana._geomE &dave._geomE &dave2._geomE ); UR_geomC =sum(of &ana._geomC &dave._geomC &dave2._geomC ); UR_calcE =sum(of &ana._calcE &dave._calcE &dave2._calcE ); UR_calcC =sum(of &ana._calcC &dave._calcC &dave2._calcC ); UR_totalmathE =sum(of &ana._totalmathE &dave._totalmathE &dave2._totalmathE ); UR_totalmathC =sum(of &ana._totalmathC &dave._totalmathC &dave2._totalmathC ); UR_bio1E =sum(of &ana._bio1E &dave._bio1E &dave2._bio1E ); UR_bio1C =sum(of &ana._bio1C &dave._bio1C &dave2._bio1C ); UR_chem1E =sum(of &ana._chem1E &dave._chem1E &dave2._chem1E ); UR_chem1C =sum(of &ana._chem1C &dave._chem1C &dave2._chem1C ); UR_phy1E =sum(of &ana._phy1E &dave._phy1E &dave2._phy1E ); UR_phy1C =sum(of &ana._phy1C &dave._phy1C &dave2._phy1C ); UR_allsciE =sum(of &ana._allsciE &dave._allsciE &dave2._allsciE ); UR_allsciC =sum(of &ana._allsciC &dave._allsciC &dave2._allsciC ); /*here, I derive variables that Jason and his team want*/ %macro KIM (Jason=); /*enrollment rate =enrollment/ population*/ &JASON._enroll_alge1=&JASON._alge1E/&JASON._grade8; /*instrumental variable*/&JASON._himathE=sum(of &JASON._alge2E &JASON._geomE &JASON._calcE); /*instrumental variable*/&JASON._grade9to12=sum (of &JASON._grade9 &JASON._grade10 &JASON._grade11 &JASON._grade12); &JASON._enroll_himath=&JASON._himathE/&JASON._grade9to12; *&JASON._grade8to12=sum(of &JASON._grade8 &JASON._grade9 &JASON._grade10 &JASON._grade11 &JASON._grade12);/*this was wrong*/ &JASON._enroll_allsci=&JASON._allsciE/ &JASON._grade9to12; /*passing rate=completion/enrollment*/ &JASON._passing_alge1=&JASON._alge1C/&JASON._alge1E; /*instrumental variable*/&JASON._himathC=sum(of &JASON._alge2C &JASON._geomC &JASON._calcC); &JASON._passing_himath=&JASON._himathC/&JASON._himathE; &JASON._passing_allsci=&JASON._allsciC/&JASON._allsciE; /*completion rate=completion/population*/ &JASON._compl_alge1=&JASON._alge1C/&JASON._grade8; /*instrumental variable*/&JASON._himathC=sum(of &JASON._alge2C &JASON._geomC &JASON._calcC); &JASON._compl_himath=&JASON._himathC/&JASON._grade9to12; &JASON._compl_allsci=&JASON._allsciC/&JASON._grade9to12; /*some times, proportion becomes more than 1--because kids may take more than one math or science classes, inflating the enrollment rate. I fix that here, so all of such values become 1*/ array &jason.systemic &JASON._enroll_alge1 &JASON._enroll_himath &JASON._enroll_allsci &JASON._passing_alge1 &JASON._passing_himath &JASON._passing_allsci &JASON._compl_alge1 &JASON._compl_himath &JASON._compl_allsci; do over &jason.systemic; if &jason.systemic > 1 then &jason.systemic=1; end; %mend kim ; %kim (jason=Total); %kim (jason=Male); %kim (jason=Female); %kim (jason=Native); %kim (jason=Asian); %kim (jason=Black); %kim (jason=Hispanic); %kim (jason=White); %kim (jason=Other); %kim (jason=UR);/*UR means underRepresentative group*/ /*get the statistics for underrepresentative group*/ %macro linda (ZZZ=); Gap_&ZZZ._alge1=White_&ZZZ._alge1-UR_&ZZZ._alge1; Gap_&ZZZ._himath=White_&ZZZ._himath-UR_&ZZZ._himath; GAP_&ZZZ._allsci=White_&ZZZ._allsci -UR_&ZZZ._allsci; %mend linda; %linda (ZZZ=enroll); %linda (ZZZ=passing); %linda (ZZZ=compl); /*also Dr. kim says that we don't use LA data from year 2000*/ getridof=0; if city ="Los Angeles" and year=2000 then getridof=1; if getridof =0;/*select only if this condition applies, meaning this get rid of LA's 2000 data*/ label Asian_alge1C ="Asian: Algebra 1 N. of students completed " Asian_alge1E ="Asian: Algebra 1 N. of Studdents enrolled " Asian_alge2C ="Asian: Algebra 2 N. of students completed " Asian_alge2E ="Asian: Algebra 2 N. of Studdents enrolled " Asian_allsciC ="Asian: Science N. of students completed " Asian_allsciE ="Asian: Scinece N. of Studdents enrolled " Asian_bio1C ="Asian: Biology N. of students completed " Asian_bio1E ="Asian: Biology N. of Studdents enrolled " Asian_calcC ="Asian: Calculus N. of students completed " Asian_calcE ="Asian: Calculus N. of studdents enrolled " Asian_chem1C ="Asian: Chemistry N. of students completed " Asian_chem1E ="Asian: Chemistry N. of studdents enrolled " Asian_compl_alge1 ="Asian: Algebra 1 Completion Rate " Asian_compl_allsci ="Asian: Science Completion Rate " Asian_compl_himath ="Asian: Higher level math Completion Rate " Asian_enroll_alge1 ="Asian: Algebra 1 Enrollment Rate " Asian_enroll_allsci ="Asian: Scinece Enrollment Rate " Asian_enroll_himath ="Asian: Higher level math Enrollment Rate " Asian_geomC ="Asian: Geometry N. of students completed " Asian_geomE ="Asian: Geometry N. of students enrolled " Asian_grade8 ="Asian: Number of 8th graders " Asian_grade9 ="Asian: Number of 9th graders " Asian_grade10 ="Asian: Number of 10th graders " Asian_grade11 ="Asian: Number of 11th graders " Asian_grade12 ="Asian: Number of 12th graders " Asian_grade9to12 ="Asian: Number of 9th-12th graders " Asian_himathC ="Asian: Higher level math N. of students completed " Asian_himathE ="Asian: Higher level math N. of students enrolled " Asian_passing_alge1 ="Asian: Algebra 1 Passing Rate " Asian_passing_allsci ="Asian: Science Passing Rate " Asian_passing_himath ="Asian: Higher level math Passing Rate " Asian_phy1C ="Asian: Physics N. of students completed " Asian_phy1E ="Asian: Physics N. of studdents enrolled " Asian_totalmathC ="Asian: All Math courses N. of students completed " Asian_totalmathE ="Asian: All Math courses N. of studdents enrolled " Black_alge1C ="Black: Algebra 1 N. of students completed " Black_alge1E ="Black: Algebra 1 N. of Studdents enrolled " Black_alge2C ="Black: Algebra 2 N. of students completed " Black_alge2E ="Black: Algebra 2 N. of Studdents enrolled " Black_allsciC ="Black: Science N. of students completed " Black_allsciE ="Black: Scinece N. of Studdents enrolled " Black_bio1C ="Black: Biology N. of students completed " Black_bio1E ="Black: Biology N. of Studdents enrolled " Black_calcC ="Black: Calculus N. of students completed " Black_calcE ="Black: Calculus N. of studdents enrolled " Black_chem1C ="Black: Chemistry N. of students completed " Black_chem1E ="Black: Chemistry N. of studdents enrolled " Black_compl_alge1 ="Black: Algebra 1 Completion Rate " Black_compl_allsci ="Black: Science Completion Rate " Black_compl_himath ="Black: Higher level math Completion Rate " Black_enroll_alge1 ="Black: Algebra 1 Enrollment Rate " Black_enroll_allsci ="Black: Scinece Enrollment Rate " Black_enroll_himath ="Black: Higher level math Enrollment Rate " Black_geomC ="Black: Geometry N. of students completed " Black_geomE ="Black: Geometry N. of students enrolled " Black_grade8 ="Black: Number of 8th graders " Black_grade9 ="Black: Number of 9th graders " Black_grade10 ="Black: Number of 10th graders " Black_grade11 ="Black: Number of 11th graders " Black_grade12 ="Black: Number of 12th graders " Black_grade9to12 ="Black: Number of 9th, 10th, 11th and 12th graders " Black_himathC ="Black: Higher level math N. of students completed " Black_himathE ="Black: Higher level math N. of students enrolled " Black_passing_alge1 ="Black: Algebra 1 Passing Rate " Black_passing_allsci ="Black: Science Passing Rate " Black_passing_himath ="Black: Higher level math Passing Rate " Black_phy1C ="Black: Physics N. of students completed " Black_phy1E ="Black: Physics N. of studdents enrolled " Black_totalmathC ="Black: All Math courses N. of students completed " Black_totalmathE ="Black: All Math courses N. of studdents enrolled " Female_alge1C ="Female: Algebra 1 N. of students completed " Female_alge1E ="Female: Algebra 1 N. of Studdents enrolled " Female_alge2C ="Female: Algebra 2 N. of students completed " Female_alge2E ="Female: Algebra 2 N. of Studdents enrolled " Female_allsciC ="Female: Science N. of students completed " Female_allsciE ="Female: Scinece N. of Studdents enrolled " Female_bio1C ="Female: Biology N. of students completed " Female_bio1E ="Female: Biology N. of Studdents enrolled " Female_calcC ="Female: Calculus N. of students completed " Female_calcE ="Female: Calculus N. of studdents enrolled " Female_chem1C ="Female: Chemistry N. of students completed " Female_chem1E ="Female: Chemistry N. of studdents enrolled " Female_compl_alge1 ="Female: Algebra 1 Completion Rate " Female_compl_allsci ="Female: Science Completion Rate " Female_compl_himath ="Female: Higher level math Completion Rate " Female_enroll_alge1 ="Female: Algebra 1 Enrollment Rate " Female_enroll_allsci ="Female: Scinece Enrollment Rate " Female_enroll_himath ="Female: Higher level math Enrollment Rate " Female_geomC ="Female: Geometry N. of students completed " Female_geomE ="Female: Geometry N. of students enrolled " Female_grade8 ="Female: Number of 8th graders " Female_grade9 ="Female: Number of 9th graders " Female_grade10 ="Female: Number of 10th graders " Female_grade11 ="Female: Number of 11th graders " Female_grade12 ="Female: Number of 12th graders " Female_grade9to12 ="Female: Number of 9th, 10th, 11th and 12th graders " Female_himathC ="Female: Higher level math N. of students completed " Female_himathE ="Female: Higher level math N. of students enrolled " Female_passing_alge1 ="Female: Algebra 1 Passing Rate " Female_passing_allsci ="Female: Science Passing Rate " Female_passing_himath ="Female: Higher level math Passing Rate " Female_phy1C ="Female: Physics N. of students completed " Female_phy1E ="Female: Physics N. of studdents enrolled " Female_totalmathC ="Female: All Math courses N. of students completed " Female_totalmathE ="Female: All Math courses N. of studdents enrolled " GAP_compl_allsci ="Science: Competion rate gap between underrepresented students and white students " GAP_enroll_allsci ="Science: Enrollment rate gap between underrepresented students and white students " GAP_passing_allsci ="Science: Passing rate gap between underrepresented students and white students " Gap_compl_alge1 ="Algebra 1: Completion rate gap between underrepresented students and white students " Gap_compl_himath ="Higher level math: Completion rate gap between underrepresented students and white students " Gap_enroll_alge1 ="Algebra 1: Enrollment rate gap between underrepresented students and white students " Gap_enroll_himath ="Higher level math: Completion rate gap between underrepresented students and white students " Gap_passing_alge1 ="Algebra 1: Passing rate gap between underrepresented students and white students " Gap_passing_himath ="Higher level math: Passing rate gap between underrepresented students and white students " Hispanic_alge1C ="Hispanic: Algebra 1 N. of students completed " Hispanic_alge1E ="Hispanic: Algebra 1 N. of Studdents enrolled " Hispanic_alge2C ="Hispanic: Algebra 2 N. of students completed " Hispanic_alge2E ="Hispanic: Algebra 2 N. of Studdents enrolled " Hispanic_allsciC ="Hispanic: Science N. of students completed " Hispanic_allsciE ="Hispanic: Scinece N. of Studdents enrolled " Hispanic_bio1C ="Hispanic: Biology N. of students completed " Hispanic_bio1E ="Hispanic: Biology N. of Studdents enrolled " Hispanic_calcC ="Hispanic: Calculus N. of students completed " Hispanic_calcE ="Hispanic: Calculus N. of studdents enrolled " Hispanic_chem1C ="Hispanic: Chemistry N. of students completed " Hispanic_chem1E ="Hispanic: Chemistry N. of studdents enrolled " Hispanic_compl_alge1 ="Hispanic: Algebra 1 Completion Rate " Hispanic_compl_allsci ="Hispanic: Science Completion Rate " Hispanic_compl_himath ="Hispanic: Higher level math Completion Rate " Hispanic_enroll_alge1 ="Hispanic: Algebra 1 Enrollment Rate " Hispanic_enroll_allsci ="Hispanic: Scinece Enrollment Rate " Hispanic_enroll_himath ="Hispanic: Higher level math Enrollment Rate " Hispanic_geomC ="Hispanic: Geometry N. of students completed " Hispanic_geomE ="Hispanic: Geometry N. of students enrolled " Hispanic_grade8 ="Hispanic: Number of 8th graders " Hispanic_grade9 ="Hispanic: Number of 9th graders " Hispanic_grade10 ="Hispanic: Number of 10th graders " Hispanic_grade11 ="Hispanic: Number of 11th graders " Hispanic_grade12 ="Hispanic: Number of 12th graders " Hispanic_grade9to12 ="Hispanic: Number of 9th, 10th, 11th and 12th graders " Hispanic_himathC ="Hispanic: Higher level math N. of students completed " Hispanic_himathE ="Hispanic: Higher level math N. of students enrolled " Hispanic_passing_alge1 ="Hispanic: Algebra 1 Passing Rate " Hispanic_passing_allsci ="Hispanic: Science Passing Rate " Hispanic_passing_himath ="Hispanic: Higher level math Passing Rate " Hispanic_phy1C ="Hispanic: Physics N. of students completed " Hispanic_phy1E ="Hispanic: Physics N. of studdents enrolled " Hispanic_totalmathC ="Hispanic: All Math courses N. of students completed " Hispanic_totalmathE ="Hispanic: All Math courses N. of studdents enrolled " Male_alge1C ="Male: Algebra 1 N. of students completed " Male_alge1E ="Male: Algebra 1 N. of Studdents enrolled " Male_alge2C ="Male: Algebra 2 N. of students completed " Male_alge2E ="Male: Algebra 2 N. of Studdents enrolled " Male_allsciC ="Male: Science N. of students completed " Male_allsciE ="Male: Scinece N. of Studdents enrolled " Male_bio1C ="Male: Biology N. of students completed " Male_bio1E ="Male: Biology N. of Studdents enrolled " Male_calcC ="Male: Calculus N. of students completed " Male_calcE ="Male: Calculus N. of studdents enrolled " Male_chem1C ="Male: Chemistry N. of students completed " Male_chem1E ="Male: Chemistry N. of studdents enrolled " Male_compl_alge1 ="Male: Algebra 1 Completion Rate " Male_compl_allsci ="Male: Science Completion Rate " Male_compl_himath ="Male: Higher level math Completion Rate " Male_enroll_alge1 ="Male: Algebra 1 Enrollment Rate " Male_enroll_allsci ="Male: Scinece Enrollment Rate " Male_enroll_himath ="Male: Higher level math Enrollment Rate " Male_geomC ="Male: Geometry N. of students completed " Male_geomE ="Male: Geometry N. of students enrolled " Male_grade8 ="Male: Number of 8th graders " Male_grade9 ="Male: Number of 9th graders " Male_grade10 ="Male: Number of 10th graders " Male_grade11 ="Male: Number of 11th graders " Male_grade12 ="Male: Number of 12th graders " Male_grade9to12 ="Male: Number of 9th, 10th, 11th and 12th graders " Male_himathC ="Male: Higher level math N. of students completed " Male_himathE ="Male: Higher level math N. of students enrolled " Male_passing_alge1 ="Male: Algebra 1 Passing Rate " Male_passing_allsci ="Male: Science Passing Rate " Male_passing_himath ="Male: Higher level math Passing Rate " Male_phy1C ="Male: Physics N. of students completed " Male_phy1E ="Male: Physics N. of studdents enrolled " Male_totalmathC ="Male: All Math courses N. of students completed " Male_totalmathE ="Male: All Math courses N. of studdents enrolled " Native_alge1C ="Native Am. Algebra 1 N. of students completed " Native_alge1E ="Native Am. Algebra 1 N. of Studdents enrolled " Native_alge2C ="Native Am. Algebra 2 N. of students completed " Native_alge2E ="Native Am. Algebra 2 N. of Studdents enrolled " Native_allsciC ="Native Am. Science N. of students completed " Native_allsciE ="Native Am. Scinece N. of Studdents enrolled " Native_bio1C ="Native Am. Biology N. of students completed " Native_bio1E ="Native Am. Biology N. of Studdents enrolled " Native_calcC ="Native Am. Calculus N. of students completed " Native_calcE ="Native Am. Calculus N. of studdents enrolled " Native_chem1C ="Native Am. Chemistry N. of students completed " Native_chem1E ="Native Am. Chemistry N. of studdents enrolled " Native_compl_alge1 ="Native Am. Algebra 1 Completion Rate " Native_compl_allsci ="Native Am. Science Completion Rate " Native_compl_himath ="Native Am. Higher level math Completion Rate " Native_enroll_alge1 ="Native Am. Algebra 1 Enrollment Rate " Native_enroll_allsci ="Native Am. Scinece Enrollment Rate " Native_enroll_himath ="Native Am. Higher level math Enrollment Rate " Native_geomC ="Native Am. Geometry N. of students completed " Native_geomE ="Native Am. Geometry N. of students enrolled " Native_grade8 ="Native Am. Number of 8th graders " Native_grade9 ="Native Am. Number of 9th graders " Native_grade10 ="Native Am. Number of 10th graders " Native_grade11 ="Native Am. Number of 11th graders " Native_grade12 ="Native Am. Number of 12th graders " Native_grade9to12 ="Native Am. Number of 9th, 10th, 11th and 12th graders " Native_himathC ="Native Am. Higher level math N. of students completed " Native_himathE ="Native Am. Higher level math N. of students enrolled " Native_passing_alge1 ="Native Am. Algebra 1 Passing Rate " Native_passing_allsci ="Native Am. Science Passing Rate " Native_passing_himath ="Native Am. Higher level math Passing Rate " Native_phy1C ="Native Am. Physics N. of students completed " Native_phy1E ="Native Am. Physics N. of studdents enrolled " Native_totalmathC ="Native Am. All Math courses N. of students completed " Native_totalmathE ="Native Am. All Math courses N. of studdents enrolled " Other_alge1C ="Other: Algebra 1 N. of students completed " Other_alge1E ="Other: Algebra 1 N. of Studdents enrolled " Other_alge2C ="Other: Algebra 2 N. of students completed " Other_alge2E ="Other: Algebra 2 N. of Studdents enrolled " Other_allsciC ="Other: Science N. of students completed " Other_allsciE ="Other: Scinece N. of Studdents enrolled " Other_bio1C ="Other: Biology N. of students completed " Other_bio1E ="Other: Biology N. of Studdents enrolled " Other_calcC ="Other: Calculus N. of students completed " Other_calcE ="Other: Calculus N. of studdents enrolled " Other_chem1C ="Other: Chemistry N. of students completed " Other_chem1E ="Other: Chemistry N. of studdents enrolled " Other_compl_alge1 ="Other: Algebra 1 Completion Rate " Other_compl_allsci ="Other: Science Completion Rate " Other_compl_himath ="Other: Higher level math Completion Rate " Other_enroll_alge1 ="Other: Algebra 1 Enrollment Rate " Other_enroll_allsci ="Other: Scinece Enrollment Rate " Other_enroll_himath ="Other: Higher level math Enrollment Rate " Other_geomC ="Other: Geometry N. of students completed " Other_geomE ="Other: Geometry N. of students enrolled " Other_grade8 ="Other: Number of 8th graders " Other_grade9 ="Other: Number of 9th graders " Other_grade10 ="Other: Number of 10th graders " Other_grade11 ="Other: Number of 11th graders " Other_grade12 ="Other: Number of 12th graders " Other_grade9to12 ="Other: Number of 9th, 10th, 11th and 12th graders " Other_himathC ="Other: Higher level math N. of students completed " Other_himathE ="Other: Higher level math N. of students enrolled " Other_passing_alge1 ="Other: Algebra 1 Passing Rate " Other_passing_allsci ="Other: Science Passing Rate " Other_passing_himath ="Other: Higher level math Passing Rate " Other_phy1C ="Other: Physics N. of students completed " Other_phy1E ="Other: Physics N. of studdents enrolled " Other_totalmathC ="Other: All Math courses N. of students completed " Other_totalmathE ="Other: All Math courses N. of studdents enrolled " Total_alge1C ="Whole Sample: Algebra 1 N. of students completed " Total_alge1E ="Whole Sample: Algebra 1 N. of Studdents enrolled " Total_alge2C ="Whole Sample: Algebra 2 N. of students completed " Total_alge2E ="Whole Sample: Algebra 2 N. of Studdents enrolled " Total_allsciC ="Whole Sample: Science N. of students completed " Total_allsciE ="Whole Sample: Scinece N. of Studdents enrolled " Total_bio1C ="Whole Sample: Biology N. of students completed " Total_bio1E ="Whole Sample: Biology N. of Studdents enrolled " Total_calcC ="Whole Sample: Calculus N. of students completed " Total_calcE ="Whole Sample: Calculus N. of studdents enrolled " Total_chem1C ="Whole Sample: Chemistry N. of students completed " Total_chem1E ="Whole Sample: Chemistry N. of studdents enrolled " Total_compl_alge1 ="Whole Sample: Algebra 1 Completion Rate " Total_compl_allsci ="Whole Sample: Science Completion Rate " Total_compl_himath ="Whole Sample: Higher level math Completion Rate " Total_enroll_alge1 ="Whole Sample: Algebra 1 Enrollment Rate " Total_enroll_allsci ="Whole Sample: Scinece Enrollment Rate " Total_enroll_himath ="Whole Sample: Higher level math Enrollment Rate " Total_geomC ="Whole Sample: Geometry N. of students completed " Total_geomE ="Whole Sample: Geometry N. of students enrolled " Total_grade8 ="Whole Sample: Number of 8th graders " Total_grade9 ="Whole Sample: Number of 9th graders " Total_grade10 ="Whole Sample: Number of 10th graders " Total_grade11 ="Whole Sample: Number of 11th graders " Total_grade12 ="Whole Sample: Number of 12th graders " Total_grade9to12 ="Whole Sample: Number of 9th, 10th, 11th and 12th graders " Total_himathC ="Whole Sample: Higher level math N. of students completed " Total_himathE ="Whole Sample: Higher level math N. of students enrolled " Total_passing_alge1 ="Whole Sample: Algebra 1 Passing Rate " Total_passing_allsci ="Whole Sample: Science Passing Rate " Total_passing_himath ="Whole Sample: Higher level math Passing Rate " Total_phy1C ="Whole Sample: Physics N. of students completed " Total_phy1E ="Whole Sample: Physics N. of studdents enrolled " Total_totalmathC ="Whole Sample: All Math courses N. of students completed " Total_totalmathE ="Whole Sample: All Math courses N. of studdents enrolled " UR_alge1C ="UR students: Algebra 1 N. of students completed " UR_alge1E ="UR students: Algebra 1 N. of Studdents enrolled " UR_alge2C ="UR students: Algebra 2 N. of students completed " UR_alge2E ="UR students: Algebra 2 N. of Studdents enrolled " UR_allsciC ="UR students: Science N. of students completed " UR_allsciE ="UR students: Scinece N. of Studdents enrolled " UR_bio1C ="UR students: Biology N. of students completed " UR_bio1E ="UR students: Biology N. of Studdents enrolled " UR_calcC ="UR students: Calculus N. of students completed " UR_calcE ="UR students: Calculus N. of studdents enrolled " UR_chem1C ="UR students: Chemistry N. of students completed " UR_chem1E ="UR students: Chemistry N. of studdents enrolled " UR_compl_alge1 ="UR students: Algebra 1 Completion Rate " UR_compl_allsci ="UR students: Science Completion Rate " UR_compl_himath ="UR students: Higher level math Completion Rate " UR_enroll_alge1 ="UR students: Algebra 1 Enrollment Rate " UR_enroll_allsci ="UR students: Scinece Enrollment Rate " UR_enroll_himath ="UR students: Higher level math Enrollment Rate " UR_geomC ="UR students: Geometry N. of students completed " UR_geomE ="UR students: Geometry N. of students enrolled " UR_grade8 ="UR students: Number of 8th graders " UR_grade9 ="UR students: Number of 9th graders " UR_grade10 ="UR students: Number of 10th graders " UR_grade11 ="UR students: Number of 11th graders " UR_grade12 ="UR students: Number of 12th graders " UR_grade9to12 ="UR students: Number of 9th, 10th, 11th and 12th graders " UR_himathC ="UR students: Higher level math N. of students completed " UR_himathE ="UR students: Higher level math N. of students enrolled " UR_passing_alge1 ="UR students: Algebra 1 Passing Rate " UR_passing_allsci ="UR students: Science Passing Rate " UR_passing_himath ="UR students: Higher level math Passing Rate " UR_phy1C ="UR students: Physics N. of students completed " UR_phy1E ="UR students: Physics N. of studdents enrolled " UR_totalmathC ="UR students: All Math courses N. of students completed " UR_totalmathE ="UR students: All Math courses N. of studdents enrolled " White_alge1C ="White: Algebra 1 N. of students completed " White_alge1E ="White: Algebra 1 N. of Studdents enrolled " White_alge2C ="White: Algebra 2 N. of students completed " White_alge2E ="White: Algebra 2 N. of Studdents enrolled " White_allsciC ="White: Science N. of students completed " White_allsciE ="White: Scinece N. of Studdents enrolled " White_bio1C ="White: Biology N. of students completed " White_bio1E ="White: Biology N. of Studdents enrolled " White_calcC ="White: Calculus N. of students completed " White_calcE ="White: Calculus N. of studdents enrolled " White_chem1C ="White: Chemistry N. of students completed " White_chem1E ="White: Chemistry N. of studdents enrolled " White_compl_alge1 ="White: Algebra 1 Completion Rate " White_compl_allsci ="White: Science Completion Rate " White_compl_himath ="White: Higher level math Completion Rate " White_enroll_alge1 ="White: Algebra 1 Enrollment Rate " White_enroll_allsci ="White: Scinece Enrollment Rate " White_enroll_himath ="White: Higher level math Enrollment Rate " White_geomC ="White: Geometry N. of students completed " White_geomE ="White: Geometry N. of students enrolled " White_grade8 ="White: Number of 8th graders " White_grade9 ="White: Number of 9th graders " White_grade10 ="White: Number of 10th graders " White_grade11 ="White: Number of 11th graders " White_grade12 ="White: Number of 12th graders " White_grade9to12 ="White: Number of 9th, 10th, 11th and 12th graders " White_himathC ="White: Higher level math N. of students completed " White_himathE ="White: Higher level math N. of students enrolled " White_passing_alge1 ="White: Algebra 1 Passing Rate " White_passing_allsci ="White: Science Passing Rate " White_passing_himath ="White: Higher level math Passing Rate " White_phy1C ="White: Physics N. of students completed " White_phy1E ="White: Physics N. of studdents enrolled " White_totalmathC ="White: All Math courses N. of students completed " White_totalmathE ="White: All Math courses N. of studdents enrolled " city ="City " cohort ="Cohort " cohort2 ="Cohort Indicator " cohort_name ="Cohort Indicator " getridof ="Deletion flag " when ="Sequential Year " year ="Year " ; proc sort data=alldata out=here.enrollment_CityYear;by city ;run; proc contents data=here.enrollment_cityyear; title1 "Contents of enrollment_cityyear data"; title2 "Case=City Year"; run; /* proc means data=here.enrollment_cityyear; title1 "Descriptive statistics of enrollment_cityyear data"; title2 "Case=City year"; run;*/ /* proc print data=here.enrollment_CityYear; var total_alge1e total_grade9to12 total_grade9 total_grade10 total_grade11 total_grade12;run; */ /*this data is too big for exporting as an excell file. As is, it returns a blank excel file. So I select only important variables for this file*/ data selectvars;set here.enrollment_CityYear; retain city when year cohort cohort2 cohort_name total_grade8 total_grade9to12 total_alge1E total_himathE total_allsciE total_alge1C total_himathC total_allsciC total_enroll_alge1 total_enroll_himath total_enroll_allsci total_passing_alge1 total_passing_himath total_passing_allsci total_compl_alge1 total_compl_himath total_compl_allsci total_enroll_alge1 total_enroll_himath total_enroll_allsci total_passing_alge1 total_passing_himath total_passing_allsci total_compl_alge1 total_compl_himath total_compl_allsci gap_enroll_alge1 gap_enroll_himath gap_enroll_allsci gap_passing_alge1 gap_passing_himath gap_passing_allsci gap_compl_alge1 gap_compl_himath gap_compl_allsci gap_enroll_alge1 gap_enroll_himath gap_enroll_allsci gap_passing_alge1 gap_passing_himath gap_passing_allsci gap_compl_alge1 gap_compl_himath gap_compl_allsci ; keep city when year cohort cohort2 cohort_name total_grade8 total_grade9to12 total_alge1E total_himathE total_allsciE total_alge1C total_himathC total_allsciC total_enroll_alge1 total_enroll_himath total_enroll_allsci total_passing_alge1 total_passing_himath total_passing_allsci total_compl_alge1 total_compl_himath total_compl_allsci total_enroll_alge1 total_enroll_himath total_enroll_allsci total_passing_alge1 total_passing_himath total_passing_allsci total_compl_alge1 total_compl_himath total_compl_allsci gap_enroll_alge1 gap_enroll_himath gap_enroll_allsci gap_passing_alge1 gap_passing_himath gap_passing_allsci gap_compl_alge1 gap_compl_himath gap_compl_allsci gap_enroll_alge1 gap_enroll_himath gap_enroll_allsci gap_passing_alge1 gap_passing_himath gap_passing_allsci gap_compl_alge1 gap_compl_himath gap_compl_allsci ; run; PROC EXPORT DATA= work.selectvars OUTFILE= "&WD\enrollment_CityYear.xls" DBMS=EXCEL2000 REPLACE;RUN; /* proc means data=here.enrollment_CityYear; title "Course taking data, summary statistics"; run;*/ /* data here.allsystemicdata; merge alldata here.rubric_standard3; by city; run;*/ *proc format; *value xfmt .="n/a"; *run; /*BELOW, I make CITY as a unit of analysis data, using cityyear data*/ data modeldata;set here.enrollment_CityYear; keep when year city Total_grade8 Total_grade9 Total_grade10 Total_grade11 Total_grade12 Total_alge1E Total_alge1C Total_alge2E Total_alge2C Total_geomE Total_geomC Total_calcE Total_calcC Total_totalmathE Total_totalmathC Total_bio1E Total_bio1C Total_chem1E Total_chem1C Total_phy1E Total_phy1C Total_allsciE Total_allsciC Total_enroll_alge1 Total_himathE Total_grade9to12 Total_enroll_himath Total_enroll_allsci Total_passing_alge1 Total_himathC Total_passing_himath Total_passing_allsci Total_compl_alge1 Total_compl_himath Total_compl_allsci Gap_enroll_alge1 Gap_enroll_himath GAP_enroll_allsci Gap_passing_alge1 Gap_passing_himath GAP_passing_allsci Gap_compl_alge1 Gap_compl_himath GAP_compl_allsci ; /*take out some cities information*/ /*Feb 2002*/ /*start here*/ if city= 'Hypothetical' or city='Chicago' or city='Baltimore' or city='Dallas' or city='Detroit' or city='El Paso' or city='Miami' or city='New York' or city='Phoenix' then cohort=1; if city='Cleveland' or city='Columbus' or city='Fresno' or city='Los Angeles' or city='Memphis' or city='New Orleans' or city='Philadelphia' then cohort=2; if city='Milwaukee' or city= 'St. Louis' or city= 'San Antonio' or city= 'San Diego' then cohort=3; if city='Atlanta' or city='Jacksonville' then cohort=4; if city='Houston' then cohort=5; array koretachi Total_grade8 Total_grade9 Total_grade10 Total_grade11 Total_grade12 Total_alge1E Total_alge1C Total_alge2E Total_alge2C Total_geomE Total_geomC Total_calcE Total_calcC Total_totalmathE Total_totalmathC Total_bio1E Total_bio1C Total_chem1E Total_chem1C Total_phy1E Total_phy1C Total_allsciE Total_allsciC Total_enroll_alge1 Total_himathE Total_grade9to12 Total_enroll_himath Total_enroll_allsci Total_passing_alge1 Total_himathC Total_passing_himath Total_passing_allsci Total_compl_alge1 Total_compl_himath Total_compl_allsci Gap_enroll_alge1 Gap_enroll_himath GAP_enroll_allsci Gap_passing_alge1 Gap_passing_himath GAP_passing_allsci Gap_compl_alge1 Gap_compl_himath GAP_compl_allsci; if year = 2000 then do over koretachi; koretachi=.; end; if cohort=1 and year > 1998 then do over koretachi; koretachi=.; end; if city="San Diego" and year > 1998 then do over koretachi; koretachi=.; end; proc sort data=modeldata out=modeldataS; by when; run; proc transpose data=modeldataS out=modeldataT; by when;id city;var Total_enroll_alge1 Total_enroll_himath Total_enroll_allsci Total_passing_alge1 Total_passing_himath Total_passing_allsci Total_compl_alge1 Total_compl_himath Total_compl_allsci Gap_enroll_alge1 Gap_enroll_himath GAP_enroll_allsci Gap_passing_alge1 Gap_passing_himath GAP_passing_allsci Gap_compl_alge1 Gap_compl_himath GAP_compl_allsci ;run; *proc print data=modeldataT; *run; *proc sort; *by _NAME_; data modeldataT2; length seprate $ 1 ; set modeldataT; separate="_"; newID=trim(_name_)||separate||left(when); *proc print data=modeldataT2; *var newID; *run; run; proc transpose data=modeldataT2 out=modeldataT2b; id /*when*/ newID; *by _name_; run; *proc print data=modeldataT2b; *run; data modeldataT3;set modeldataT2b; keep city length cohort city2 Total_enroll_alge1 Total_enroll_himath Total_enroll_allsci Total_passing_alge1 Total_passing_himath Total_passing_allsci Total_compl_alge1 Total_compl_himath Total_compl_allsci Gap_enroll_alge1 Gap_enroll_himath GAP_enroll_allsci Gap_passing_alge1 Gap_passing_himath GAP_passing_allsci Gap_compl_alge1 Gap_compl_himath GAP_compl_allsci size_of_student_population number_of_school N_of_student_per_school /* total_enroll_himath_0 total_enroll_himath_1 total_enroll_himath_2 total_enroll_himath_3 total_enroll_himath_4 total_enroll_himath_5 total_enroll_himath_6 diftotal_enroll_himath_1 diftotal_enroll_himath_2 diftotal_enroll_himath_3 diftotal_enroll_himath_4 diftotal_enroll_himath_5 diftotal_enroll_himath_6 diftotal_enroll_himath_7*/ ; *%macro aki (kaz=); *&kaz.1=&kaz._1-&kaz._0; *&kaz.2=&kaz._2-&kaz._1; *&kaz.3=&kaz._3-&kaz._2; *&kaz.4=&kaz._4-&kaz._3; *&kaz.5=&kaz._5-&kaz._4; *&kaz.6=&kaz._6-&kaz._5; *&kaz.7=&kaz._7-&kaz._6; *&kaz = mean( of &kaz.1 &kaz.2 &kaz.3 &kaz.4 &kaz.5 &kaz.6 &kaz.7); %macro aki (kaz=); dif&kaz._1=&kaz._1-&kaz._0; dif&kaz._2=&kaz._2-&kaz._1; dif&kaz._3=&kaz._3-&kaz._2; dif&kaz._4=&kaz._4-&kaz._3; dif&kaz._5=&kaz._5-&kaz._4; dif&kaz._6=&kaz._6-&kaz._5; dif&kaz._7=&kaz._7-&kaz._6; &kaz = mean( of dif&kaz._1 dif&kaz._2 dif&kaz._3 dif&kaz._4 dif&kaz._5 dif&kaz._6 dif&kaz._7); %mend aki; %aki (kaz=Total_enroll_alge1); %aki (kaz= Total_enroll_himath); %aki (kaz= Total_enroll_allsci); %aki (kaz= Total_passing_alge1); %aki (kaz= Total_passing_himath); %aki (kaz= Total_passing_allsci); %aki (kaz= Total_compl_alge1); %aki (kaz= Total_compl_himath); %aki (kaz= Total_compl_allsci); %aki (kaz= Gap_enroll_alge1); %aki (kaz= Gap_enroll_himath); %aki (kaz= GAP_enroll_allsci); %aki (kaz= Gap_passing_alge1); %aki (kaz= Gap_passing_himath); %aki (kaz= GAP_passing_allsci); %aki (kaz= Gap_compl_alge1); %aki (kaz= Gap_compl_himath); %aki (kaz= GAP_compl_allsci); /*_name_ contains city names, but blanks in between names got _, so I get rid of them*/ city=_name_; if city ="El_Paso" then city="El Paso"; if city="Los_Angeles" then city="Los Angeles"; if city="New_Orleans" then city="New Orleans"; if city="New_York" then city="New York"; if city="San_Diego" then city="San Diego"; if city="St__Louis" then city="St. Louis"; if city="San_Antonio" then city="San Antonio"; if city= 'Hypothetical' or city='Chicago' or city='Baltimore' or city='Dallas' or city='Detroit' or city='El Paso' or city='Miami' or city='New York' or city='Phoenix' then cohort=1; /*Jason wants to give weight to the length of years in the program*/ if city='Cleveland' or city='Columbus' or city='Fresno' or city='Los Angeles' or city='Memphis' or city='New Orleans' or city='Philadelphia' then cohort=2; if city='Milwaukee' or city= 'St. Louis' or city= 'San Antonio' or city= 'San Diego' then cohort=3; if city='Atlanta' or city='Jacksonville' then cohort=4; if city='Houston' then cohort=5; *if cohort=1 then length=2001-1993; *if cohort=2 then length=2001-1994; *if cohort=3 then length=2001-1995; *if cohort=4 then length=2001-1997; *if cohort=5 then length=2001-1999; /*Weight*/ if cohort=1 then length=5; if cohort=2 then length=4; if cohort=3 then length=3; if cohort=4 then length=1; if cohort=5 then length=1;/*we don't use Houston, but in case*/ *length=length*4; city2=trim(city)||left(length); *if _LABEL_ ne " "; *drop _name_; if city ne "when"; /*number of schools in each district*/ if city=" Fresno" then Nschl= 93 ; if city="Atlanta" then Nschl= 103 ; if city="St. Louis" then Nschl= 104 ; if city="Phoenix" then Nschl= 120 ; if city="Cleveland" then Nschl= 121 ; if city="New Orleans" then Nschl= 127 ; if city=" Columbus" then Nschl= 134 ; if city="Jacksonville" then Nschl= 154 ; if city="Milwaukee" then Nschl= 155 ; if city="Memphis " then Nschl= 160 ; if city="El Paso" then Nschl= 166 ; if city="San Diego" then Nschl= 178 ; if city="Baltimore" then Nschl= 187 ; if city="Dallas" then Nschl= 217 ; if city="Philadelphia " then Nschl= 259 ; if city="Detroit" then Nschl= 260 ; if city="Miami" then Nschl= 284 ; if city="San Antonio" then Nschl= 290 ; if city="Houston" then Nschl= 295 ; if city=" Chicago" then Nschl= 534 ; if city="Los Angeles" then Nschl= 570 ; if city="New York" then Nschl= 1145 ; /*number of kids in each district*/ if city="St. Louis" then stdsize= 39047 ; if city="Atlanta" then stdsize= 54320 ; if city="Columbus" then stdsize= 62481 ; if city="Cleveland" then stdsize= 71431 ; if city="Fresno" then stdsize= 78520 ; if city="New Orleans" then stdsize= 82288 ; if city="Phoenix" then stdsize= 84008 ; if city="Baltimore" then stdsize= 94763 ; if city="Milwaukee" then stdsize= 103777 ; if city="Memphis " then stdsize= 109931 ; if city="Jacksonville" then stdsize= 123261 ; if city="El Paso" then stdsize= 135677 ; if city="San Diego" then stdsize= 138356 ; if city="Dallas" then stdsize= 153404 ; if city="Detroit" then stdsize= 184139 ; if city="Philadelphia " then stdsize= 201425 ; if city="Houston" then stdsize= 208000 ; if city="San Antonio" then stdsize= 230449 ; if city="Miami" then stdsize= 359021 ; if city="Chicago" then stdsize= 411859 ; if city="Los Angeles" then stdsize= 592975 ; if city="New York" then stdsize= 1099437 ; /*density*/ size_of_student_population=stdsize; number_of_school=Nschl; N_of_student_per_school=stdsize/nschl; label GAP_compl_allsci ="Science: Average gap of completion rate between underrepresented minority and white students " GAP_enroll_allsci ="Science: Average gap of enrollment rate between underrepresented minority and white students " GAP_passing_allsci ="Science: Average gap of passing rate between underrepresented minority and white students " Gap_compl_alge1 ="Algebra 1: Average gap of completion rate between underrepresented minority and white students " Gap_compl_himath ="Higher level Math: Average gap of completion rate between underrepresented minority and white students " Gap_enroll_alge1 ="Algebra 1: Average gap of enrollment rate between underrepresented minority and white students " Gap_enroll_himath ="Higher level Math: Average gap of enrollment rate between underrepresented minority and white students " Gap_passing_alge1 ="Algebra 1: Average gap of passing rate between underrepresented minority and white students " Gap_passing_himath ="Higher level Math: Average gap of passing rate between underrepresented minority and white students " N_of_student_per_school ="Average N. of students per school " total_compl_alge1 ="Algebra 1: Annualized percentage point increase in completion rate " total_compl_allsci ="Science: Annualized percentage point increase in completion rate " total_compl_himath ="Higher level Math: Annualized percentage point increase in completion rate " total_enroll_alge1 ="Algebra 1: Annualized percentage point increase in enrollment rate " total_enroll_allsci ="Science: Annualized percentage point increase in enrollment rate " total_enroll_himath ="Higher level Math: Annualized percentage point increase in enrollment rate " total_passing_alge1 ="Algebra 1: Annualized percentage point increase in passing rate " total_passing_allsci ="Science: Annualized percentage point increase in passing rate " total_passing_himath ="Higher level Math: Annualized percentage point increase in passing rate " city ="City " city2 ="City Indicator " cohort ="Cohort " length ="Length of years in the USI program " number_of_school ="N. of Schools in the district " size_of_student_population ="N. of students in the district " ; run; proc sort data=modeldataT3 out=here.enrollment_City; by city;run; proc contents data=here.enrollment_City; title1"Contents of enrollment_city data"; title2 "Unit=City"; run; /* proc means data=here.enrollment_City; title1"Descriptive statistics of enrollment data"; title2 "Unit=City"; run;*/