Libname sasin  "l:cohort 1/grade 10/school records/academic";
Libname sasout "d:/work/school records/year 11";


 **Need to convert the variables from character to numeric**;

Data one;
set sasin.do11q1
	sasin.no11q1
	sasin.po11q1
	sasin.so11q1;

o11q10 = Co11q10+0;
o11q73 = Co11q73+0;
o11q87 = Co11q87+0;
if Co11q13 = 'skip' then o11q13x = .; else o11q13x = Co11q13+0;
if Co11q22 = 'skip' then o11q22x = .; else o11q22x = Co11q22+0;
if Co11q31 = 'skip' then o11q31x = .; else o11q31x = Co11q31+0;
if Co11q74 = 'skip' then Co11q74 = '.'; else o11q74x = Co11q74+0;
if Co11q75 = 'skip' then Co11q75 = '.'; else o11q75x = Co11q75+0;
if Co11q76 = 'skip' then Co11q76 = '.'; else o11q76x = Co11q76+0;
if Co11q77 = 'skip' then Co11q77 = '.'; else o11q77x = Co11q78+0;
if Co11q78 = 'skip' then Co11q78 = '.'; else o11q78x = Co11q78+0;
if Co11q79 = 'skip' then Co11q79 = '.'; else o11q79x = Co11q79+0;
if Co11q80 = 'skip' then Co11q80 = '.'; else o11q80x = Co11q80+0;
if Co11q81 = 'skip' then Co11q81 = '.'; else o11q81x = Co11q81+0;

DROP  Co11q74-Co11q81 co11q13 co11q22 co11q31;
RUN;

DATA two;
SET one;
RENAME	o11q13x = o11q13
		o11q22x = o11q22
		o11q31x = o11q31
		o11q74x = o11q74
		o11q75x = o11q75
	   	o11q76x = o11q76
	   	o11q77x = o11q77
	   	o11q78x = o11q78
	   	o11q79x = o11q79
	   	o11q80x = o11q80
	   	o11q81x = o11q81;
	PROC SORT ; BY SITE TCID ;

DATA three;
SET two;
**First School**;
 if co11q15 = 'A' then o11q15 = 13;**Math**;
 if co11q15 = 'B' then o11q15 = 10;
 if co11q15 = 'C' then o11q15 = 7; 
 if co11q15 = 'D' then o11q15 = 4; 
 if co11q15 = 'E' then o11q15 = 1; 
 if co11q15 = 'F' then o11q15 = 1; 
 if co11q15 = 'P' then o11q15 = 7;

 drop co11q15;

 if co11q16 = 'A' then o11q16 = 13;**Language Arts**;
 if co11q16 = 'B' then o11q16 = 10;
 if co11q16 = 'C' then o11q16 = 7; 
 if co11q16 = 'D' then o11q16 = 4; 
 if co11q16 = 'E' then o11q16 = 1; 
 if co11q16 = 'F' then o11q16 = 1;
 if co11q16 = 'P' then o11q16 = 7; 

 drop co11q16;

 if co11q17 = 'A' then o11q17 = 13;**Social Studies**;
 if co11q17 = 'B' then o11q17 = 10;
 if co11q17 = 'C' then o11q17 = 7; 
 if co11q17 = 'D' then o11q17 = 4; 
 if co11q17 = 'E' then o11q17 = 1; 
 if co11q17 = 'F' then o11q17 = 1;
 if co11q17 = 'P' then o11q17 = 7; 

 drop co11q17;

 if co11q18 = 'A' then o11q18 = 13;**Science**;
 if co11q18 = 'B' then o11q18 = 10;
 if co11q18 = 'C' then o11q18 = 7; 
 if co11q18 = 'D' then o11q18 = 4; 
 if co11q18 = 'E' then o11q18 = 1; 
 if co11q18 = 'F' then o11q18 = 1; 
 if co11q18 = 'P' then o11q18 = 7;

 drop co11q18;

 **Second School**;
 if co11q24 = 'A' then o11q24 = 13;**Math**;
 if co11q24 = 'B' then o11q24 = 10;
 if co11q24 = 'C' then o11q24 = 7; 
 if co11q24 = 'D' then o11q24 = 4; 
 if co11q24 = 'E' then o11q24 = 1; 
 if co11q24 = 'F' then o11q24 = 1; 
 if co11q24 = 'P' then o11q24 = 7;

 drop co11q24;

 if co11q25 = 'A' then o11q25 = 13;**Language Arts**;
 if co11q25 = 'B' then o11q25 = 10;
 if co11q25 = 'C' then o11q25 = 7; 
 if co11q25 = 'D' then o11q25 = 4; 
 if co11q25 = 'E' then o11q25 = 1; 
 if co11q25 = 'F' then o11q25 = 1;
 if co11q25 = 'P' then o11q25 = 7; 

 drop co11q25;

 if co11q26 = 'A' then o11q26 = 13;**Social Studies**;
 if co11q26 = 'B' then o11q26 = 10;
 if co11q26 = 'C' then o11q26 = 7; 
 if co11q26 = 'D' then o11q26 = 4; 
 if co11q26 = 'E' then o11q26 = 1; 
 if co11q26 = 'F' then o11q26 = 1;
 if co11q26 = 'P' then o11q26 = 7; 

 drop co11q26;

 if co11q27 = 'A' then o11q27 = 13;**Science**;
 if co11q27 = 'B' then o11q27 = 10;
 if co11q27 = 'C' then o11q27 = 7; 
 if co11q27 = 'D' then o11q27 = 4; 
 if co11q27 = 'E' then o11q27 = 1; 
 if co11q27 = 'F' then o11q27 = 1; 
 if co11q27 = 'P' then o11q27 = 7;

 drop co11q27;

 **Third School**;
 if co11q33 = 'A' then o11q33 = 13;**Math**;
 if co11q33 = 'B' then o11q33 = 10;
 if co11q33 = 'C' then o11q33 = 7; 
 if co11q33 = 'D' then o11q33 = 4; 
 if co11q33 = 'E' then o11q33 = 1; 
 if co11q33 = 'F' then o11q33 = 1; 
 if co11q33 = 'P' then o11q33 = 7;

 drop co11q33;

 if co11q34 = 'A' then o11q34 = 13;**Language Arts**;
 if co11q34 = 'B' then o11q34 = 10;
 if co11q34 = 'C' then o11q34 = 7; 
 if co11q34 = 'D' then o11q34 = 4; 
 if co11q34 = 'E' then o11q34 = 1; 
 if co11q34 = 'F' then o11q34 = 1;
 if co11q34 = 'P' then o11q34 = 7; 

 drop co11q34;

 if co11q35 = 'A' then o11q35 = 13;**Social Studies**;
 if co11q35 = 'B' then o11q35 = 10;
 if co11q35 = 'C' then o11q35 = 7; 
 if co11q35 = 'D' then o11q35 = 4; 
 if co11q35 = 'E' then o11q35 = 1; 
 if co11q35 = 'F' then o11q35 = 1;
 if co11q35 = 'P' then o11q35 = 7; 

 drop co11q35;

 if co11q36 = 'A' then o11q36 = 13;**Science**;
 if co11q36 = 'B' then o11q36 = 10;
 if co11q36 = 'C' then o11q36 = 7; 
 if co11q36 = 'D' then o11q36 = 4; 
 if co11q36 = 'E' then o11q36 = 1; 
 if co11q36 = 'F' then o11q36 = 1; 
 if co11q36 = 'P' then o11q36 = 7;

 drop co11q36;
**Fixing spacing for variable grade and converting to numeric**;
Data four;
Set three;
grade=trim(left(grade));
Run;

**Creating a mean overall grade for subjects**;
Data five;
set four;

Mathgrade=mean(o11q15, o11q24, o11q33);
LAgrade=mean(o11q16, o11q25, o11q34);
SSgrade=mean(o11q17, o11q26, o11q35);
Scigrade=mean(o11q18, o11q27, o11q36);
**Calculating: 1) school with the max number of days enrolled and 
2)the grades for the subjects at that school**;

Data six;
set five;

maxdaysenrolled=max(o11q21, o11q30, o11q39);
if maxdaysenrolled=o11q21 then mathmax=o11q15;
if maxdaysenrolled=o11q30 then mathmax=o11q24;
if maxdaysenrolled=o11q39 then mathmax=o11q33;

if maxdaysenrolled=o11q21 then lamax=o11q16;
if maxdaysenrolled=o11q30 then lamax=o11q25;
if maxdaysenrolled=o11q39 then lamax=o11q34;

if maxdaysenrolled=o11q21 then ssmax=o11q17;
if maxdaysenrolled=o11q30 then ssmax=o11q26;
if maxdaysenrolled=o11q39 then ssmax=o11q35;

if maxdaysenrolled=o11q21 then scimax=o11q18;
if maxdaysenrolled=o11q30 then scimax=o11q27;
if maxdaysenrolled=o11q39 then scimax=o11q36;
run;
Data seven;
set six;
Totaldaysenrolled= sum(o11q21, o11q30, o11q39);
Totaldaystardy= sum(o11q20, o11q29, o11q38);
Totaldaysabsent= sum(o11q19, o11q28, o11q37);
run;

Data sasout.SRR11;

Set seven;

Label	o11q15 = "1st School-Math"
		o11q16 = "1st School-Language Arts"
		o11q17 = "1st School-Social Studies"
		o11q18 = "1st School-Science"
		o11q24 = "2nd School-Math"
		o11q25 = "2nd School-Language Arts"
		o11q26 = "2nd School-Social Studies"
		o11q27 = "2nd School-Science"
		o11q33 = "3rd School-Math"
		o11q34 = "3rd School-Language Arts"
		o11q35 = "3rd School-Social Studies"
		o11q36 = "3rd School-Science"
		o11q10 = "Is Child Repeating a Grade?"
		o11q73 = "Current IEP?"
		o11q87 = "Child Been Declared Not Needing SPED?"
		o11q74 = "Disability/Class Mentally Retarded"
		o11q75 = "Disability/Class Learning Disabled"
		o11q76 = "Disability/Class SBD/SED/BEH"
		o11q77 = "Disability/Class Orthopaedically Impaired"
		o11q78 = "Disability/Class Other Health Impaired"
		o11q79 = "Disability/Class Speech/Language Impaired"
		o11q80 = "Disability/Class Talented & Gifted"
		o11q81 = "Disability/Class Missing"
		o11q11 = "# of School Building Transitions"
		o11q13 = "1st School: What type of school did the child attend this past school year"
		o11q22 = "2nd School: What type of school did the child attend this past school year"
		o11q31 = "3rd School: What type of school did the child attend this past school year"
		o11q83 = "Current Level of Service: #Min/Wk Special Ed Resources"
		Mathgrade = "Average Math Grade across Schools"
		LAGrade = "Average Language Arts Grade across Schools"
		SSgrade = "Average Social Studies Grade across Schools"
		Scigrade = "Average Science Grade across Schools"
		Maxdaysenrolled = "Attendance for School Student Attended Longest"
		Mathmax = "Math grade for school student attended longest"
		LAmax = "Language Arts grade for school student attended longest"
		SSmax = "Social Studies grade for school student attended longest"
		Scimax = "Science grade for school student attended longest"
		Totaldaysenrolled = "Total days enrolled in school"
		Totaldaystardy = "Total days tardy to school"
		Totaldaysabsent = "Total days absent from school";


Run;

Proc contents;
Run;

Proc freq;
Tables o11q15 o11q26 o11q10 o11q73 co11q9 o11q76 o11q78 o11q13 o11q22 o11q31 LAgrade 
Scigrade LAmax Maxdaysenrolled totaldaysenrolled totaldaystardy totaldaysabsent;
Run;