using stata for educational accountability & compliance ...fm · data architecture for stata...

BackgroundNew LegislationOld System

The ChallengeDevelopment TimelineEvolving Requirements/RulesData Management/Architecture

The SolutionADO Files !!!Workflow Overview

Getting Data Into StataTime Series Functions to theRescueData Architecture for StataData Management and RuleImplementationCalculations

MegaReporting = LATEX2ε +Bash

The Outcome

Where to go from here

Using Stata for EducationalAccountability & Compliance

ReportingStata Conference 2014

Boston, MA

Billy BuchananStrategic Data Project Data Fellow

Mississippi Department of Education

July 31, 2014

EdAccountability July 31, 2014 1 / 39






The Outcome


Overview

1 BackgroundNew LegislationOld System

2 The ChallengeDevelopment TimelineEvolving Requirements/RulesData Management/Architecture

3 The SolutionADO Files !!!

Workflow OverviewGetting Data Into StataTime Series Functions to the RescueData Architecture for Stata

Data Management and Rule ImplementationCalculations

MegaReporting = LATEX2ε + Bash4 The Outcome5 Where to go from here







The Outcome


The catalyst for the work

In 2013 the Governor of Mississippi signedSenate Bill 2396 into law

Combine the federal and state accountabilitysystemsUse a letter grade system to labelschool/district qualityWhen 65% of schools/districts are rated as a“B” or higher OR 75% of students areproficient or above on student assessmentsnew thresholds for performance labels will beset

The State Board of Education (SBE) conveneda task force to define the components of theaccountability system model, weighting of thecomponents, how student growth would bedetermined, and to set thresholds forperformance labels







The Outcome




Combine the federal and state accountabilitysystems

Use a letter grade system to labelschool/district qualityWhen 65% of schools/districts are rated as a“B” or higher OR 75% of students areproficient or above on student assessmentsnew thresholds for performance labels will beset








The Outcome




Combine the federal and state accountabilitysystemsUse a letter grade system to labelschool/district quality

When 65% of schools/districts are rated as a“B” or higher OR 75% of students areproficient or above on student assessmentsnew thresholds for performance labels will beset








The Outcome




Combine the federal and state accountabilitysystemsUse a letter grade system to labelschool/district qualityWhen 65% of schools/districts are rated as a“B” or higher OR 75% of students areproficient or above on student assessmentsnew thresholds for performance labels will beset








The Outcome


The catalyst for the work(continued)

This led to two distinct scales:

A scale for schools/districts without asecondary/high school graduating class, andA scale for schools/districts with asecondary/high school graduating class

And started a process of programming a newsystem from the ground up as well as reverseengineering parts of the legacy code baseIn order to provide information to the public, a“prototype” had to be developed in a period ofroughly 30 days







The Outcome



This led to two distinct scales:A scale for schools/districts without asecondary/high school graduating class, and

A scale for schools/districts with asecondary/high school graduating class








The Outcome



This led to two distinct scales:A scale for schools/districts without asecondary/high school graduating class, andA scale for schools/districts with asecondary/high school graduating class








The Outcome




And started a process of programming a newsystem from the ground up as well as reverseengineering parts of the legacy code base

In order to provide information to the public, a“prototype” had to be developed in a period ofroughly 30 days







The Outcome











The Outcome


In the beginning. . .there was some other analytic software

First accountability system developmentoccurred from 1985–1991 with four dedicatedstaff

From that point until 2012, there was always adedicated team of four or more staff that was ablend of information systems (e.g., SQLdevelopers), information tech (e.g.,networking/server maintenance), and 1 SASuserIn 2013 there were no staff available/able torun the myriad SAS scripts and manage thenumerous output files; the original designerwas contracted to run the former system







The Outcome



First accountability system developmentoccurred from 1985–1991 with four dedicatedstaffFrom that point until 2012, there was always adedicated team of four or more staff that was ablend of information systems (e.g., SQLdevelopers), information tech (e.g.,networking/server maintenance), and 1 SASuser

In 2013 there were no staff available/able torun the myriad SAS scripts and manage thenumerous output files; the original designerwas contracted to run the former system







The Outcome



First accountability system developmentoccurred from 1985–1991 with four dedicatedstaffFrom that point until 2012, there was always adedicated team of four or more staff that was ablend of information systems (e.g., SQLdevelopers), information tech (e.g.,networking/server maintenance), and 1 SASuserIn 2013 there were no staff available/able torun the myriad SAS scripts and manage thenumerous output files; the original designerwas contracted to run the former system







The Outcome


In the beginning. . .(continued)

Area # Scripts # Data FilesGrowth 32 80Graduation Rates 23 75AYP† 81 119ESEA (Federal Law) 16 22†

Various aggregated proportions of achievement levels/participation rates on standardized tests

The estimated run time of the previous system(which did include features/programs notrequired for the current system) was roughly 1week if no additional debugging/programmingwas needed

Data acquisition involved processing rawassessment files from test vendors, running avariety of stored procedures/function calls on alarge Oracle database, and retaining/usingarchived SAS datasets







The Outcome


In the beginning. . .(continued)



The estimated run time of the previous system(which did include features/programs notrequired for the current system) was roughly 1week if no additional debugging/programmingwas neededData acquisition involved processing rawassessment files from test vendors, running avariety of stored procedures/function calls on alarge Oracle database, and retaining/usingarchived SAS datasets







The Outcome


Stata to the rescue

Although the original intent was to develop thenew model solely in PL/SQL, the combinationof staff and time resource constraints led todeveloping a “prototype” of the system a bitdifferently than initially planned

Because result sets were available for severalcomponents (e.g., graduation rates, testparticipation, etc. . . ) there wasn’t a need todevelop scripts/programs for these areas;however, this would change over the course ofthe yearSo the workload was split: information systemsstaff would assemble the data needed into a flatfile and I would have responsibility for theimplementation of the “business rules” andcomputationsGiven the set of rules/operational definitions, itseemed like it would be fairly feasible; then westarted working with the data and gettingreminders about existing SBE and federalpolicies that also needed to be incorporatedinto the design







The Outcome


Stata to the rescue

Although the original intent was to develop thenew model solely in PL/SQL, the combinationof staff and time resource constraints led todeveloping a “prototype” of the system a bitdifferently than initially plannedBecause result sets were available for severalcomponents (e.g., graduation rates, testparticipation, etc. . . ) there wasn’t a need todevelop scripts/programs for these areas;however, this would change over the course ofthe year

So the workload was split: information systemsstaff would assemble the data needed into a flatfile and I would have responsibility for theimplementation of the “business rules” andcomputationsGiven the set of rules/operational definitions, itseemed like it would be fairly feasible; then westarted working with the data and gettingreminders about existing SBE and federalpolicies that also needed to be incorporatedinto the design







The Outcome


Stata to the rescue

Although the original intent was to develop thenew model solely in PL/SQL, the combinationof staff and time resource constraints led todeveloping a “prototype” of the system a bitdifferently than initially plannedBecause result sets were available for severalcomponents (e.g., graduation rates, testparticipation, etc. . . ) there wasn’t a need todevelop scripts/programs for these areas;however, this would change over the course ofthe yearSo the workload was split: information systemsstaff would assemble the data needed into a flatfile and I would have responsibility for theimplementation of the “business rules” andcomputations

Given the set of rules/operational definitions, itseemed like it would be fairly feasible; then westarted working with the data and gettingreminders about existing SBE and federalpolicies that also needed to be incorporatedinto the design







The Outcome


Stata to the rescue

Although the original intent was to develop thenew model solely in PL/SQL, the combinationof staff and time resource constraints led todeveloping a “prototype” of the system a bitdifferently than initially plannedBecause result sets were available for severalcomponents (e.g., graduation rates, testparticipation, etc. . . ) there wasn’t a need todevelop scripts/programs for these areas;however, this would change over the course ofthe yearSo the workload was split: information systemsstaff would assemble the data needed into a flatfile and I would have responsibility for theimplementation of the “business rules” andcomputationsGiven the set of rules/operational definitions, itseemed like it would be fairly feasible; then westarted working with the data and gettingreminders about existing SBE and federalpolicies that also needed to be incorporatedinto the design







The Outcome


Mutually Exclusive Completely Exhaustive (MECE) operational

definitions

Because the State’s student information systemacquires data through batch XML processingon a roughly monthly basis, there wereproblems with some data that could not bereconciled at entry

For example, students who enrolled and/orwithdrew from one school to another duringthe first month of school are not able to betracked as accurately as we would likeDue to the batch process that is usedvalidation checks that could prevent some ofthese issues aren’t always availableSince the definition of Full Academic Year is afunction of the percentage of calendar daysenrolled in a school/district, this led to manycases where the enrollment window could notbe closed

Additional challenges regarding the handlingof special cases of student scores would havereduced sample sizes to the point where anyestimates would have been highly unstable







The Outcome



definitions


For example, students who enrolled and/orwithdrew from one school to another duringthe first month of school are not able to betracked as accurately as we would like

Due to the batch process that is usedvalidation checks that could prevent some ofthese issues aren’t always availableSince the definition of Full Academic Year is afunction of the percentage of calendar daysenrolled in a school/district, this led to manycases where the enrollment window could notbe closed








The Outcome



definitions


For example, students who enrolled and/orwithdrew from one school to another duringthe first month of school are not able to betracked as accurately as we would likeDue to the batch process that is usedvalidation checks that could prevent some ofthese issues aren’t always available

Since the definition of Full Academic Year is afunction of the percentage of calendar daysenrolled in a school/district, this led to manycases where the enrollment window could notbe closed








The Outcome



definitions


For example, students who enrolled and/orwithdrew from one school to another duringthe first month of school are not able to betracked as accurately as we would likeDue to the batch process that is usedvalidation checks that could prevent some ofthese issues aren’t always availableSince the definition of Full Academic Year is afunction of the percentage of calendar daysenrolled in a school/district, this led to manycases where the enrollment window could notbe closed








The Outcome


MECE (continued)example

Student ID District ID School ID Date Code000123456 0001 020 09aug2013 E1000123456 0001 024 19aug2013 E1000123456 0001 020 10sep2013 T2000123456 0002 016 20oct2013 E4000123456 0001 024 20oct2013 T3000123456 0002 016 13apr2014 T8

Codes beginning with an E represent different classifications ofenrollmentsCodes beginning with a T represent different classifications of transfersFor the original prototype, these data were originally stored in a wideformat in the same flat file/table as the assessment dataIn cases like the first three observations, there was no way to determinewhen to close the enrollment window without making assumptionsabout which data were “true” and/or “correct” and which were notSince the first file submission from districts covers August andSeptember, there isn’t a good/clean way of reconciling the recordswhen they enter the data system without substantial time/effort thatwould have a negative effect on system performance







The Outcome


Dealing with Time Variant and TimeInvariant Data simultaneously

Federal law requires students to be assessed ingrades 10-12 but advanced students may takethese assessments prior to those grades

To work around this, many states have adopteda “banking” model where the associated scoreon the high school assessment is “deposited”until the student is in the 10th grade and“withdrawn” for applicationSome data elements (e.g., enrollment/transferrecords, monthly enrollment histories, etc. . . )were best suited for a “long” file format, whilethe assessment data were easier to store in a“wide” format







The Outcome



Federal law requires students to be assessed ingrades 10-12 but advanced students may takethese assessments prior to those gradesTo work around this, many states have adopteda “banking” model where the associated scoreon the high school assessment is “deposited”until the student is in the 10th grade and“withdrawn” for application

Some data elements (e.g., enrollment/transferrecords, monthly enrollment histories, etc. . . )were best suited for a “long” file format, whilethe assessment data were easier to store in a“wide” format







The Outcome



Federal law requires students to be assessed ingrades 10-12 but advanced students may takethese assessments prior to those gradesTo work around this, many states have adopteda “banking” model where the associated scoreon the high school assessment is “deposited”until the student is in the 10th grade and“withdrawn” for applicationSome data elements (e.g., enrollment/transferrecords, monthly enrollment histories, etc. . . )were best suited for a “long” file format, whilethe assessment data were easier to store in a“wide” format







The Outcome


Changes From the Prototype

The prototype versions of the Stata programsdid not include any capabilities to aggregateresults to subgroups withinschools/districts/state

Full Academic Year data was originally storedin wide format as part of the dataset thatcontained student assessment data; now thispart of the process can be implementedindependently of the other dataGraduation rate computations were included;this includes managing the 150+ filesdistributed to each of the school districts tovalidate and verify the data and make anyappeals/submit documentation to updaterecords (made easier by the UCLA StatsConsulting Group’s FAQ pages)







The Outcome



The prototype versions of the Stata programsdid not include any capabilities to aggregateresults to subgroups withinschools/districts/stateFull Academic Year data was originally storedin wide format as part of the dataset thatcontained student assessment data; now thispart of the process can be implementedindependently of the other data

Graduation rate computations were included;this includes managing the 150+ filesdistributed to each of the school districts tovalidate and verify the data and make anyappeals/submit documentation to updaterecords (made easier by the UCLA StatsConsulting Group’s FAQ pages)







The Outcome



The prototype versions of the Stata programsdid not include any capabilities to aggregateresults to subgroups withinschools/districts/stateFull Academic Year data was originally storedin wide format as part of the dataset thatcontained student assessment data; now thispart of the process can be implementedindependently of the other dataGraduation rate computations were included;this includes managing the 150+ filesdistributed to each of the school districts tovalidate and verify the data and make anyappeals/submit documentation to updaterecords (made easier by the UCLA StatsConsulting Group’s FAQ pages)







The Outcome


Changes . . .(continued)

During prototyping the data were pulled froman MS SQL Server box via ODBC (from OSXto Windows), and the table had cast all of thedata elements as string

Currently, the IT staff are setting up a Solarisworkspace and the data will be pulled directlyfrom the source data system (Oracle 11g soonOracle 12c)Since the information systems office begansharing their code, I’ve been able to modifythe SQL queries to implement the namingconventions and type casting on the back-end;unfortunately date and datetime fieldsimported from odbc load still need a littleclean up







The Outcome




Currently, the IT staff are setting up a Solarisworkspace and the data will be pulled directlyfrom the source data system (Oracle 11g soonOracle 12c)

Since the information systems office begansharing their code, I’ve been able to modifythe SQL queries to implement the namingconventions and type casting on the back-end;unfortunately date and datetime fieldsimported from odbc load still need a littleclean up







The Outcome




Currently, the IT staff are setting up a Solarisworkspace and the data will be pulled directlyfrom the source data system (Oracle 11g soonOracle 12c)Since the information systems office begansharing their code, I’ve been able to modifythe SQL queries to implement the namingconventions and type casting on the back-end;unfortunately date and datetime fieldsimported from odbc load still need a littleclean up







The Outcome


How Things Look Now

Full Academic Year is queried and computedas a standalone process

Graduation rates use program to build Statadataset from 150 + school district files,historical monthly enrollment records, andOracle database table which is callable fromthe program that estimates the results







The Outcome


How Things Look Now

Full Academic Year is queried and computedas a standalone processGraduation rates use program to build Statadataset from 150 + school district files,historical monthly enrollment records, andOracle database table which is callable fromthe program that estimates the results







The Outcome


How Things Look Now(continued)

Import data required to estimate ratios forproficiency, growth, and participationcomponent scores contained in a singletable/flat file

Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computations

One program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variables

Proficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)

A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputations

One program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter grade

One program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)

One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome




Several programs implement “business rules”(operational definitions) and prepare variablesfor use in computationsOne program used to create indicators for arank-based demographic variablesProficiency, growth, and test participationrates calculations use 1 program each(mde_proficiency & mde_growth)A few programs exist to combine results fromdifferent iterations of the proficiency/growthcomputationsOne program assembles all of the result sets,generates total/composite score, and assignsletter gradeOne program used to create approximately7,350 layered scatter plots (7 for each schooland 7 for each district)One program used to take the results set andbuild “reports” for schools/districts as well asoptions to add graphs to the file







The Outcome


mde_importData System Connectivity

A utility program that uses odbc to pull datafrom a large student information system

Program was originally set up to addressformatting (e.g., converting date strings todates) and naming conventions, but this isbeing pushed into the sql that will be passed toodbc sqlfile()Formatting dates now will be a matter offormat %td *dateMore importantly, however, is that thisprogram labels all of the variables, adds valuelabels where appropriate/necessary, and createsa checksum that can be used to ensure theestimates can be replicated and validatedindependently







The Outcome



A utility program that uses odbc to pull datafrom a large student information systemProgram was originally set up to addressformatting (e.g., converting date strings todates) and naming conventions, but this isbeing pushed into the sql that will be passed toodbc sqlfile()

Formatting dates now will be a matter offormat %td *dateMore importantly, however, is that thisprogram labels all of the variables, adds valuelabels where appropriate/necessary, and createsa checksum that can be used to ensure theestimates can be replicated and validatedindependently







The Outcome



A utility program that uses odbc to pull datafrom a large student information systemProgram was originally set up to addressformatting (e.g., converting date strings todates) and naming conventions, but this isbeing pushed into the sql that will be passed toodbc sqlfile()Formatting dates now will be a matter offormat %td *date

More importantly, however, is that thisprogram labels all of the variables, adds valuelabels where appropriate/necessary, and createsa checksum that can be used to ensure theestimates can be replicated and validatedindependently







The Outcome



A utility program that uses odbc to pull datafrom a large student information systemProgram was originally set up to addressformatting (e.g., converting date strings todates) and naming conventions, but this isbeing pushed into the sql that will be passed toodbc sqlfile()Formatting dates now will be a matter offormat %td *dateMore importantly, however, is that thisprogram labels all of the variables, adds valuelabels where appropriate/necessary, and createsa checksum that can be used to ensure theestimates can be replicated and validatedindependently







The Outcome


mde_fayCalculating Full Academic Year Status

I structured the enrollment/transfer records inlong format to take full advantage of qui:g daysenrolled = D.date + 1

After getting the number of days enrolled asimple collapse (sum)daysenrolled, by(studentidschoolid)With the total number of days enrolled I coulduse a scalar created from required arguments inthe program to test the 75% thresholdmde_fay, tsd(01sep2013)ted(28apr2014) fbed(01dec2013)sbsd(01feb2014) and 2 minutes later andfull academic year (FAY) determinations weremade for 500,000 + students at the state,district, and school levels for full year, fallsemester, and spring semester







The Outcome



I structured the enrollment/transfer records inlong format to take full advantage of qui:g daysenrolled = D.date + 1After getting the number of days enrolled asimple collapse (sum)daysenrolled, by(studentidschoolid)

With the total number of days enrolled I coulduse a scalar created from required arguments inthe program to test the 75% thresholdmde_fay, tsd(01sep2013)ted(28apr2014) fbed(01dec2013)sbsd(01feb2014) and 2 minutes later andfull academic year (FAY) determinations weremade for 500,000 + students at the state,district, and school levels for full year, fallsemester, and spring semester







The Outcome



I structured the enrollment/transfer records inlong format to take full advantage of qui:g daysenrolled = D.date + 1After getting the number of days enrolled asimple collapse (sum)daysenrolled, by(studentidschoolid)With the total number of days enrolled I coulduse a scalar created from required arguments inthe program to test the 75% threshold

mde_fay, tsd(01sep2013)ted(28apr2014) fbed(01dec2013)sbsd(01feb2014) and 2 minutes later andfull academic year (FAY) determinations weremade for 500,000 + students at the state,district, and school levels for full year, fallsemester, and spring semester







The Outcome



I structured the enrollment/transfer records inlong format to take full advantage of qui:g daysenrolled = D.date + 1After getting the number of days enrolled asimple collapse (sum)daysenrolled, by(studentidschoolid)With the total number of days enrolled I coulduse a scalar created from required arguments inthe program to test the 75% thresholdmde_fay, tsd(01sep2013)ted(28apr2014) fbed(01dec2013)sbsd(01feb2014) and 2 minutes later andfull academic year (FAY) determinations weremade for 500,000 + students at the state,district, and school levels for full year, fallsemester, and spring semester







The Outcome


Creating Rule-Based CompositeVariablesmde_page, mde_fayvars, & mde_growvars

Students with severe cognitive disabilitiescoded to “ungraded grades” throughout the US,but we need accurate grade levels to determineon-/off-grade testing

mde_page creates a composite grade levelvariable (‘pgrade’) that combines an age-basedgrade determination for “ungraded” studentsand the standard grade level for other studentsAdditionally, because students can takeconcurrent courses on differing schedules (e.g.,full year, fall/spring semester), we needed away to correctly determine whether FAY wassatisfied for the individual subject areasmde_fayvars checks student courseschedule types and builds new variables thatselect the appropriate FAY district and schoolID values for each subject area







The Outcome



Students with severe cognitive disabilitiescoded to “ungraded grades” throughout the US,but we need accurate grade levels to determineon-/off-grade testingmde_page creates a composite grade levelvariable (‘pgrade’) that combines an age-basedgrade determination for “ungraded” studentsand the standard grade level for other students

Additionally, because students can takeconcurrent courses on differing schedules (e.g.,full year, fall/spring semester), we needed away to correctly determine whether FAY wassatisfied for the individual subject areasmde_fayvars checks student courseschedule types and builds new variables thatselect the appropriate FAY district and schoolID values for each subject area







The Outcome



Students with severe cognitive disabilitiescoded to “ungraded grades” throughout the US,but we need accurate grade levels to determineon-/off-grade testingmde_page creates a composite grade levelvariable (‘pgrade’) that combines an age-basedgrade determination for “ungraded” studentsand the standard grade level for other studentsAdditionally, because students can takeconcurrent courses on differing schedules (e.g.,full year, fall/spring semester), we needed away to correctly determine whether FAY wassatisfied for the individual subject areas

mde_fayvars checks student courseschedule types and builds new variables thatselect the appropriate FAY district and schoolID values for each subject area







The Outcome



Students with severe cognitive disabilitiescoded to “ungraded grades” throughout the US,but we need accurate grade levels to determineon-/off-grade testingmde_page creates a composite grade levelvariable (‘pgrade’) that combines an age-basedgrade determination for “ungraded” studentsand the standard grade level for other studentsAdditionally, because students can takeconcurrent courses on differing schedules (e.g.,full year, fall/spring semester), we needed away to correctly determine whether FAY wassatisfied for the individual subject areasmde_fayvars checks student courseschedule types and builds new variables thatselect the appropriate FAY district and schoolID values for each subject area







The Outcome


Creating Weightsmde_profvars & mde_onepercent

We also needed a method to manageprecedence rules for assessment types (generalv. alternate), check for on/off grade testing, andorganize the data for non-technical audiencesto replicate results

mde_profvars rla mth sci hiscreates off-grade violation flags, binaryindicators for proficiency, a test type indicator,and a copy of the original proficiency levelSome students w/disabilities are allowed totake alternate statewide assessments, butfederal law restricts the proportion of studentsper district counted as proficientmde_onepercent rla mth sci,district(dist) estimates the proportionof these students scoring proficient or aboveand defines a weight 1

%Proficient/Above × 0.01 toimpose a ceiling of 1%







The Outcome



We also needed a method to manageprecedence rules for assessment types (generalv. alternate), check for on/off grade testing, andorganize the data for non-technical audiencesto replicate resultsmde_profvars rla mth sci hiscreates off-grade violation flags, binaryindicators for proficiency, a test type indicator,and a copy of the original proficiency level

Some students w/disabilities are allowed totake alternate statewide assessments, butfederal law restricts the proportion of studentsper district counted as proficientmde_onepercent rla mth sci,district(dist) estimates the proportionof these students scoring proficient or aboveand defines a weight 1








The Outcome



We also needed a method to manageprecedence rules for assessment types (generalv. alternate), check for on/off grade testing, andorganize the data for non-technical audiencesto replicate resultsmde_profvars rla mth sci hiscreates off-grade violation flags, binaryindicators for proficiency, a test type indicator,and a copy of the original proficiency levelSome students w/disabilities are allowed totake alternate statewide assessments, butfederal law restricts the proportion of studentsper district counted as proficient

mde_onepercent rla mth sci,district(dist) estimates the proportionof these students scoring proficient or aboveand defines a weight 1








The Outcome



We also needed a method to manageprecedence rules for assessment types (generalv. alternate), check for on/off grade testing, andorganize the data for non-technical audiencesto replicate resultsmde_profvars rla mth sci hiscreates off-grade violation flags, binaryindicators for proficiency, a test type indicator,and a copy of the original proficiency levelSome students w/disabilities are allowed totake alternate statewide assessments, butfederal law restricts the proportion of studentsper district counted as proficientmde_onepercent rla mth sci,district(dist) estimates the proportionof these students scoring proficient or aboveand defines a weight 1








The Outcome


Weighted Transitional Probabilitiesmde_growvars

A major concern for estimating growth was theease with which the calculation andinterpretation could be communicated to thepublic and practitioners

The state adopted a “categorical growth”model, which is — in a sense — a transitionalprobability, that included additional weightsfor larger transitions, and splitting of lowerlevel valuesmde_growvars builds a dataset containingscaled score cut points and uses a combinationof merge m:1 and the cond() function torecode proficiency levels from 4 to 6 values







The Outcome



A major concern for estimating growth was theease with which the calculation andinterpretation could be communicated to thepublic and practitionersThe state adopted a “categorical growth”model, which is — in a sense — a transitionalprobability, that included additional weightsfor larger transitions, and splitting of lowerlevel values

mde_growvars builds a dataset containingscaled score cut points and uses a combinationof merge m:1 and the cond() function torecode proficiency levels from 4 to 6 values







The Outcome



A major concern for estimating growth was theease with which the calculation andinterpretation could be communicated to thepublic and practitionersThe state adopted a “categorical growth”model, which is — in a sense — a transitionalprobability, that included additional weightsfor larger transitions, and splitting of lowerlevel valuesmde_growvars builds a dataset containingscaled score cut points and uses a combinationof merge m:1 and the cond() function torecode proficiency levels from 4 to 6 values







The Outcome


Proportion/Order-BasedClassificationsmde_low25

As the US Department of Education’s policyfocus shifted under President Obama’sadministration, an increase emphasis ondefining sub-group membership based onperformance emerged

To identify the lowest quartile in literacy andnumeracy domains, the state adopted adefinition that would combine ranking andproportion methods to define the students inthe lowest 25% in academic performance in theschoolceil(N * .25) is used to identify theposition of the student within a subject, grade,and school/district whose score is used for themembership thresholdmde_low25 rla mth,district(dist) school(sch)implements these rules and creates indicatorvariables for the students







The Outcome



As the US Department of Education’s policyfocus shifted under President Obama’sadministration, an increase emphasis ondefining sub-group membership based onperformance emergedTo identify the lowest quartile in literacy andnumeracy domains, the state adopted adefinition that would combine ranking andproportion methods to define the students inthe lowest 25% in academic performance in theschool

ceil(N * .25) is used to identify theposition of the student within a subject, grade,and school/district whose score is used for themembership thresholdmde_low25 rla mth,district(dist) school(sch)implements these rules and creates indicatorvariables for the students







The Outcome



As the US Department of Education’s policyfocus shifted under President Obama’sadministration, an increase emphasis ondefining sub-group membership based onperformance emergedTo identify the lowest quartile in literacy andnumeracy domains, the state adopted adefinition that would combine ranking andproportion methods to define the students inthe lowest 25% in academic performance in theschoolceil(N * .25) is used to identify theposition of the student within a subject, grade,and school/district whose score is used for themembership threshold

mde_low25 rla mth,district(dist) school(sch)implements these rules and creates indicatorvariables for the students







The Outcome



As the US Department of Education’s policyfocus shifted under President Obama’sadministration, an increase emphasis ondefining sub-group membership based onperformance emergedTo identify the lowest quartile in literacy andnumeracy domains, the state adopted adefinition that would combine ranking andproportion methods to define the students inthe lowest 25% in academic performance in theschoolceil(N * .25) is used to identify theposition of the student within a subject, grade,and school/district whose score is used for themembership thresholdmde_low25 rla mth,district(dist) school(sch)implements these rules and creates indicatorvariables for the students







The Outcome


Statusmde_proficiency

The proficiency calculations are a combinationof weighted/unweighted proportions based onthe weights created by themde_onepercent program

mde_proficiency rla mth scihis, district(dist) school(sch)disfile(district_proficiency)schfile(school_proficiency) wouldgenerate all of the proficiency values for eachof the four subject areas at the state, district,and school levelsAlthough not included in the originaldesign/build, the current version of the programincludes an option amovars that is used tocreate all of the result sets require for federalreporting of Annual Measurable Objectives







The Outcome



The proficiency calculations are a combinationof weighted/unweighted proportions based onthe weights created by themde_onepercent programmde_proficiency rla mth scihis, district(dist) school(sch)disfile(district_proficiency)schfile(school_proficiency) wouldgenerate all of the proficiency values for eachof the four subject areas at the state, district,and school levels

Although not included in the originaldesign/build, the current version of the programincludes an option amovars that is used tocreate all of the result sets require for federalreporting of Annual Measurable Objectives







The Outcome



The proficiency calculations are a combinationof weighted/unweighted proportions based onthe weights created by themde_onepercent programmde_proficiency rla mth scihis, district(dist) school(sch)disfile(district_proficiency)schfile(school_proficiency) wouldgenerate all of the proficiency values for eachof the four subject areas at the state, district,and school levelsAlthough not included in the originaldesign/build, the current version of the programincludes an option amovars that is used tocreate all of the result sets require for federalreporting of Annual Measurable Objectives







The Outcome


Growthmde_growth

The growth program is structured similar to theproficiency program, but includes a substantialincrease in the number of available options

Because the lowest 25% membership is onlyused in growth calculations, this programincludes options to specify each of the groupidentifiers for their respective subject areas, aswell as state, district, and school identifiers toaggregate the data properly







The Outcome


Growthmde_growth

The growth program is structured similar to theproficiency program, but includes a substantialincrease in the number of available optionsBecause the lowest 25% membership is onlyused in growth calculations, this programincludes options to specify each of the groupidentifiers for their respective subject areas, aswell as state, district, and school identifiers toaggregate the data properly







The Outcome


Graduation & Dropout Ratesmde_grads & mde_gradfiles

Files are distributed to each of the 150 +districts across MS to verify and validatestudents’ final status as graduates, stillenrolled, dropouts, or other completers

mde_gradfiles builds a file list of thesefiles and automates importing, converting, andcombining them into a single Stata dataset; italso adds variable and value labels and doessome other associated housekeeping along thewayEven with the file created from the output ofthe program above, we still need to managemonthly enrollment records and the officialinformation stored in the information systemsmde_grads can call mde_gradfiles tobuild the file above; import, clean, and mergethe two other distinct data sources; computethe rates; and accept a do file as an argument toimplement changes from the appeals process aswell as adding characteristics into the datasetto document the change to each variable foreach appeal and add a label identifying theschool the filed the appeal







The Outcome



Files are distributed to each of the 150 +districts across MS to verify and validatestudents’ final status as graduates, stillenrolled, dropouts, or other completersmde_gradfiles builds a file list of thesefiles and automates importing, converting, andcombining them into a single Stata dataset; italso adds variable and value labels and doessome other associated housekeeping along theway

Even with the file created from the output ofthe program above, we still need to managemonthly enrollment records and the officialinformation stored in the information systemsmde_grads can call mde_gradfiles tobuild the file above; import, clean, and mergethe two other distinct data sources; computethe rates; and accept a do file as an argument toimplement changes from the appeals process aswell as adding characteristics into the datasetto document the change to each variable foreach appeal and add a label identifying theschool the filed the appeal







The Outcome



Files are distributed to each of the 150 +districts across MS to verify and validatestudents’ final status as graduates, stillenrolled, dropouts, or other completersmde_gradfiles builds a file list of thesefiles and automates importing, converting, andcombining them into a single Stata dataset; italso adds variable and value labels and doessome other associated housekeeping along thewayEven with the file created from the output ofthe program above, we still need to managemonthly enrollment records and the officialinformation stored in the information systems

mde_grads can call mde_gradfiles tobuild the file above; import, clean, and mergethe two other distinct data sources; computethe rates; and accept a do file as an argument toimplement changes from the appeals process aswell as adding characteristics into the datasetto document the change to each variable foreach appeal and add a label identifying theschool the filed the appeal







The Outcome



Files are distributed to each of the 150 +districts across MS to verify and validatestudents’ final status as graduates, stillenrolled, dropouts, or other completersmde_gradfiles builds a file list of thesefiles and automates importing, converting, andcombining them into a single Stata dataset; italso adds variable and value labels and doessome other associated housekeeping along thewayEven with the file created from the output ofthe program above, we still need to managemonthly enrollment records and the officialinformation stored in the information systemsmde_grads can call mde_gradfiles tobuild the file above; import, clean, and mergethe two other distinct data sources; computethe rates; and accept a do file as an argument toimplement changes from the appeals process aswell as adding characteristics into the datasetto document the change to each variable foreach appeal and add a label identifying theschool the filed the appeal







The Outcome


Combining the results and assigningletter gradesmde_grades

The final step before creating the reports is theassignment of performance classifiers toschools and districts

This phase in the overall workflow is alsowhere “pseudo-imputation” is used to providecomponent scores for schools withoutsufficient data (e.g., < 10 Students) using theresults for the districtIn this phase a scale linking occurs for specialcases of primary grade schools that do not havescience assessment resultsA recent addition to this program — at therequest of a district superintendent — is addingpercentile rankings for each component scoreto the output file; the results are also classifiedinto quintiles which are used to define fillcolors for cells in the reports







The Outcome



The final step before creating the reports is theassignment of performance classifiers toschools and districtsThis phase in the overall workflow is alsowhere “pseudo-imputation” is used to providecomponent scores for schools withoutsufficient data (e.g., < 10 Students) using theresults for the district

In this phase a scale linking occurs for specialcases of primary grade schools that do not havescience assessment resultsA recent addition to this program — at therequest of a district superintendent — is addingpercentile rankings for each component scoreto the output file; the results are also classifiedinto quintiles which are used to define fillcolors for cells in the reports







The Outcome



The final step before creating the reports is theassignment of performance classifiers toschools and districtsThis phase in the overall workflow is alsowhere “pseudo-imputation” is used to providecomponent scores for schools withoutsufficient data (e.g., < 10 Students) using theresults for the districtIn this phase a scale linking occurs for specialcases of primary grade schools that do not havescience assessment results

A recent addition to this program — at therequest of a district superintendent — is addingpercentile rankings for each component scoreto the output file; the results are also classifiedinto quintiles which are used to define fillcolors for cells in the reports







The Outcome



The final step before creating the reports is theassignment of performance classifiers toschools and districtsThis phase in the overall workflow is alsowhere “pseudo-imputation” is used to providecomponent scores for schools withoutsufficient data (e.g., < 10 Students) using theresults for the districtIn this phase a scale linking occurs for specialcases of primary grade schools that do not havescience assessment resultsA recent addition to this program — at therequest of a district superintendent — is addingpercentile rankings for each component scoreto the output file; the results are also classifiedinto quintiles which are used to define fillcolors for cells in the reports







The Outcome


Visualizing Comparisons for Schools,LEAs, & the Publicmde_graphs

Anyone who has looked at data viz ineducational reporting has almost definitelywitnessed massive abuse of bar graphs forlongitudinal data

We wanted to take a slightly different approachthat expressed the data in multiple dimensionsand could be useful for making within andbetween district comparisons of the school ofinterestAnd when we solicited feedback from a groupof district administrators, added to the numberof graphs to meet the requestsHowever, some folks weren’t immediatelycomfortable with scatter plots, so I also createda short animated graph using Stata andffmpeg (thanks to the tutorial developed byR. Grant) that provides an over simplified wayof reading scatter plots







The Outcome



Anyone who has looked at data viz ineducational reporting has almost definitelywitnessed massive abuse of bar graphs forlongitudinal dataWe wanted to take a slightly different approachthat expressed the data in multiple dimensionsand could be useful for making within andbetween district comparisons of the school ofinterest

And when we solicited feedback from a groupof district administrators, added to the numberof graphs to meet the requestsHowever, some folks weren’t immediatelycomfortable with scatter plots, so I also createda short animated graph using Stata andffmpeg (thanks to the tutorial developed byR. Grant) that provides an over simplified wayof reading scatter plots







The Outcome



Anyone who has looked at data viz ineducational reporting has almost definitelywitnessed massive abuse of bar graphs forlongitudinal dataWe wanted to take a slightly different approachthat expressed the data in multiple dimensionsand could be useful for making within andbetween district comparisons of the school ofinterestAnd when we solicited feedback from a groupof district administrators, added to the numberof graphs to meet the requests

However, some folks weren’t immediatelycomfortable with scatter plots, so I also createda short animated graph using Stata andffmpeg (thanks to the tutorial developed byR. Grant) that provides an over simplified wayof reading scatter plots







The Outcome



Anyone who has looked at data viz ineducational reporting has almost definitelywitnessed massive abuse of bar graphs forlongitudinal dataWe wanted to take a slightly different approachthat expressed the data in multiple dimensionsand could be useful for making within andbetween district comparisons of the school ofinterestAnd when we solicited feedback from a groupof district administrators, added to the numberof graphs to meet the requestsHowever, some folks weren’t immediatelycomfortable with scatter plots, so I also createda short animated graph using Stata andffmpeg (thanks to the tutorial developed byR. Grant) that provides an over simplified wayof reading scatter plots







The Outcome


Visualizing Comparisons for Schools,LEAs, & the PublicProficiency Example

0102030405060708090

100

Read

ing

Lang

uage

Arts

:Pr

ofici

ency

Poi

nts

0 10 20 30 40 50 60 70 80 90 100Mathematics:

Proficiency Points

Other MS Schools Other Schools in DistrictSchool of Interest

Red Lines Indicate the State's Performance.For Example Purposes OnlyAll Data Are Simulated for Illustration Only

Reading Language Arts andMathematics Proficiency







The Outcome


Visualizing Comparisons for Schools,LEAs, & the PublicGrowth Example

0102030405060708090

100110

Read

ing

Lang

uage

Arts

:G

row

th P

oint

s All

Stud

ents

0 10 20 30 40 50 60 70 80 90 100 110 120Mathematics:

Growth Points All Students



2012-2013 Growth:All Students







The Outcome


Visualizing Comparisons for Schools,LEAs, & the PublicLow 25% Student Growth Example

0102030405060708090

100110

Read

ing

Lang

uage

Arts

:G

row

th P

oint

s Low

25%

Stu

dent

s

0 10 20 30 40 50 60 70 80 90 100 110 120Mathematics:

Growth Points Low 25% Students



2012-2013 Growth:Low 25% Students







The Outcome


Visualizing Comparisons for Schools,LEAs, & the PublicProficiency v Growth Example

0102030405060708090

100

Read

ing

Lang

uage

Arts

:Pr

ofici

ency

Poi

nts

0 10 20 30 40 50 60 70 80 90 100 110 120Reading Language Arts:

Growth Points All Students



Proficiency vs Growth:Reading Language Arts







The Outcome


Visualizing Comparisons for Schools,LEAs, & the PublicProficiency v Low 25% Student Growth Example

0102030405060708090

100

Read

ing

Lang

uage

Arts

:Pr

ofici

ency

Poi

nts

0 10 20 30 40 50 60 70 80 90 100 110 120Reading Language Arts:

Growth Points Low 25% Students



Proficiency vs Growth:Reading Language Arts







The Outcome


Homemade LATEXMail Mergemde_reports

Due to a combination of limited access andfamiliarity with Crystal Reports, I also had todevelop a solution for our reporting needs

Since a standardized layout/formatting of thecomponents/scores was already familiar tostakeholders, I recreated the layout/format inLATEXTo make the values of the component scoresmore informative, we created quintiles for eachcomponent score and used the quintiles todefine the fill colors for the cellsmde_reports reads the result set data fileand uses file to write the LATEXand write aBash script that is optionalled called by shellto compile the .tex files and remove all of theancillary files after compilation







The Outcome



Due to a combination of limited access andfamiliarity with Crystal Reports, I also had todevelop a solution for our reporting needsSince a standardized layout/formatting of thecomponents/scores was already familiar tostakeholders, I recreated the layout/format inLATEX

To make the values of the component scoresmore informative, we created quintiles for eachcomponent score and used the quintiles todefine the fill colors for the cellsmde_reports reads the result set data fileand uses file to write the LATEXand write aBash script that is optionalled called by shellto compile the .tex files and remove all of theancillary files after compilation







The Outcome



Due to a combination of limited access andfamiliarity with Crystal Reports, I also had todevelop a solution for our reporting needsSince a standardized layout/formatting of thecomponents/scores was already familiar tostakeholders, I recreated the layout/format inLATEXTo make the values of the component scoresmore informative, we created quintiles for eachcomponent score and used the quintiles todefine the fill colors for the cells

mde_reports reads the result set data fileand uses file to write the LATEXand write aBash script that is optionalled called by shellto compile the .tex files and remove all of theancillary files after compilation







The Outcome



Due to a combination of limited access andfamiliarity with Crystal Reports, I also had todevelop a solution for our reporting needsSince a standardized layout/formatting of thecomponents/scores was already familiar tostakeholders, I recreated the layout/format inLATEXTo make the values of the component scoresmore informative, we created quintiles for eachcomponent score and used the quintiles todefine the fill colors for the cellsmde_reports reads the result set data fileand uses file to write the LATEXand write aBash script that is optionalled called by shellto compile the .tex files and remove all of theancillary files after compilation







The Outcome


Homemade LATEXMail MergeSchools/Districts with a 12th Grade Example

SCHOOL NAME HEREDISTRICT NAME HERE

MATHEMATICS SCIENCE US HISTORYACCELERATION⇓READING

Grade: CTotal Points:

586

PROFICIENCY

GROWTH

ALLSTUDENTS

GROWTHLOW 25%

60.5 71.6 60.8

66.9

52.0

70.7

50.6

GRADUATION

RATE⇓

85.9

68.3 N/A

COLLEGE &CAREER

READINESS⇓

N/A

PARTICIPATION

RATE⇓

99.6

0–20%ile 21–40%ile 41–60%ile 61–80%ile 81–100%ileThe colors above indicate in which quintile the individual component is in compared to other schools in the MS Statewide Accountability System.







The Outcome


Homemade LATEXMail MergeSchools/Districts without a 12th Grade Example

SCHOOL NAME HEREDISTRICT NAME HERE

MATHEMATICS SCIENCEREADINGGrade: CTotal Points: 418

PROFICIENCY

GROWTHALL STUDENTS

GROWTHLOW 25%

49.5 45.8 54.8

65.9

74.1

61.1

68.2

PARTICIPATION

RATE⇓

99.6

0–20%ile 21–40%ile 41–60%ile 61–80%ile 81–100%ileThe colors above indicate in which quintile the individual component is in compared to other schools in the MS Statewide Accountability System.







The Outcome


Efficiency Gains from StataComparing the code bases



The process of estimating growth previouslyrequired the use of 32 static SAS Scripts; Now. . . it is completed with 1 .ADO file.Graduation Rates previously used 23 staticSAS Scripts; Now . . . it can be completed with2 .ADO files.Proficiency/Participation Rate Measures used> 50 Scripts previously. Now . . . it iscompleted with 2 .ADO files.







The Outcome


Efficiency Gains from Stata(continued)

In addition to reducing the code base, the useof ADO files should also reduce long termmaintenance (so long as the rules don’t change)The migration also gives the agency theflexibility to deploy open sourced toolkitsdeveloped by the Strategic Data Projecthttp://www.gse.harvard.edu/sdp/resources/toolkit.php formeasuring and leveraging existing data to drivehuman capital policies/practices as well asinform policies, practices, and programdevelopment to support college and careerreadiness goals


http://www.gse.harvard.edu/sdp/resources/toolkit.php






The Outcome


Ease of use for additional end-usersMenu/Dialog Driven Interface

Although Stata may be user friendly to folksaccustomed to working in the Stataenvironment, scripting languages can often beintimidating for many folks in the educationsector whose only experience with analyticsoftware is Some Program for Some Statisticsand/or spreadsheet-based programsWhile dialog programming in Stata may not bethe easiest, creating menu driven systems canmake the interface more welcoming to noviceusersStata is also still fairly new to the educationsector in general and many graduate trainingprograms in education tend to favor SomeProgram for Some Statistics even though it is aless robust platform for analysis anddevelopment







The Outcome


Optimizing the code basecomputations/data management

Since a few of the operations take severalminutes to run, it would be great to push someof the computations into MataCurrently working on refining the SQLcodebase to force queries to return SIF valuesfor date fields (e.g.,TO_NUMBER(TO_DATE(’09012014’,’MMDDYYYY’) - TO_DATE(’01jan1960’,’DDmmmYYYY’)) = 19967) to reducecleaning/formatting operations on the Stataside of thingsBecause of the tight development timeline, themajority of the code took the “brute force”approach so there are likely to be numerousopportunities for code optimization







The Outcome


Adding more robust support for datavisualizationinteractive visualizations and code efficiency

One of the biggest runtime efficiencychallenges is in graph creationCurrently all of the graphs are static, but I’mhoping that I’ll learn enough about stata2d3during the conference to change that a bit moreI also hope to better integrate tools like QGISand/or spmap (Available from SSC)to develop data products for publicconsumption







The Outcome


Training additional end-users

Used some of the existing examples of building.smcl-based presentations as a starting pointReplaced nearly all code with hypertext toreduce end-users’ anxiety of script-basedsoftware and allow them to get an idea of whatthe software is capable of without having tolearn to code firstAfter some initial participant feedback, addedtwo sub-routines that would allow users tofetch data sets created with aisa (Writtenby S Kolenikov) and a routine that wouldlaunch video tutorials in Stata


using stata for educational accountability & compliance ...fm · data architecture for stata...

Documents