Panel Data for Test Scores

[Susie Truitt]

Hello,

I've created a dataset to analyze differences in NC test scores due to COVID-19. I currently have it set up so each observation has a year, school, test name, and score. I'm trying to create another variable, Difference, that generates the difference in scores from 2020 to 2019, but the difference for the same test at the same school (so long as Year==2020, it will only appear under 2020 observations which is okay with me).
I cannot quite figure out how to do this. I have python experience and would create a dictionary if I was using python, but I'm not sure how to do the equivalent on stata. I saw a command that used PanelID, but am not sure if its applicable in my situation. Can anyone help?

[Ken Chui]

Hello and welcome to Statalist.

The trick to get a useful answer quickly is "don't just tell, also show." The data feel so "of course!" to you because you have been looking at it for a long time, but that is not the case for us. Please take a moment to read the FAQ (http://www.statalist.org/forums/help) and follow how to use dataex to show how the data were structured. That way you may be able to get response faster, plus codes contributed by users here that you can actually use immediately.

From the description it feels like it's a long format data, so something akin to this may be possible. But it's of course untested:

bysort schoolid testname (year): generate scorediff = score - score[_n-1]

[Susie Truitt]

Hi Ken,

Thanks for pointing that out to me. Heres my dataex result:

input float Year str30 DistrictName long SchoolCode str40 SchoolName str26 Subject double Proficient float Blended long var3 str19 County
2020 "Alamance-Burlington Schools" 10303 "ABSS Early College at ACC"    "NC Math 3 (9-12)" 67.9 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10303 "ABSS Early College at ACC"    "Biology"          72.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10303 "ABSS Early College at ACC"    "NC Math 1 (9-12)"   50 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10304 "Alexander Wilson Elementary"  "Reading Grade 4"  56.8 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10304 "Alexander Wilson Elementary"  "Math Grade 5"     37.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10304 "Alexander Wilson Elementary"  "Science Grade 5"  40.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10304 "Alexander Wilson Elementary"  "Reading Grade 5"  37.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10304 "Alexander Wilson Elementary"  "Math Grade 3"     20.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10304 "Alexander Wilson Elementary"  "Math Grade 4"     36.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10308 "Altamahaw-Ossipee Elementary" "Science Grade 5"  58.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10308 "Altamahaw-Ossipee Elementary" "Math Grade 5"       35 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10308 "Altamahaw-Ossipee Elementary" "Math Grade 3"     44.6 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10308 "Altamahaw-Ossipee Elementary" "Math Grade 4"       34 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10308 "Altamahaw-Ossipee Elementary" "Reading Grade 5"  36.8 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10308 "Altamahaw-Ossipee Elementary" "Reading Grade 4"  49.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Math Grade 7"     14.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Reading Grade 8"  22.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Reading Grade 6"  26.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Science Grade 8"  40.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Math Grade 6"     14.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Math Grade 8"      7.3 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10310 "Broadview Middle"             "Reading Grade 7"  22.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10312 "Highland Elementary"          "Math Grade 4"     40.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10312 "Highland Elementary"          "Math Grade 5"     43.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10312 "Highland Elementary"          "Reading Grade 4"  60.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10312 "Highland Elementary"          "Reading Grade 5"  45.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10312 "Highland Elementary"          "Science Grade 5"  65.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10312 "Highland Elementary"          "Math Grade 3"     38.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10320 "E M Yoder Elementary"         "Math Grade 5"     51.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10320 "E M Yoder Elementary"         "Science Grade 5"  57.8 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10320 "E M Yoder Elementary"         "Math Grade 4"     30.3 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10320 "E M Yoder Elementary"         "Reading Grade 4"  51.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10320 "E M Yoder Elementary"         "Math Grade 3"     43.6 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10320 "E M Yoder Elementary"         "Reading Grade 5"  44.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10324 "Eastern Alamance High"        "English II"       60.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10324 "Eastern Alamance High"        "NC Math 1 (9-12)" 18.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10324 "Eastern Alamance High"        "Biology"          45.3 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10324 "Eastern Alamance High"        "NC Math 3 (9-12)"   41 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10326 "Eastlawn Elementary"          "Math Grade 3"     16.3 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10326 "Eastlawn Elementary"          "Reading Grade 4"  21.3 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10326 "Eastlawn Elementary"          "Reading Grade 5"  27.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10326 "Eastlawn Elementary"          "Science Grade 5"  27.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10326 "Eastlawn Elementary"          "Math Grade 5"     16.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10328 "Edwin M Holt Elementary"      "Math Grade 4"     38.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10328 "Edwin M Holt Elementary"      "Reading Grade 4"  44.3 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10328 "Edwin M Holt Elementary"      "Reading Grade 5"  47.9 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10328 "Edwin M Holt Elementary"      "Math Grade 5"     41.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10328 "Edwin M Holt Elementary"      "Science Grade 5"  60.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10328 "Edwin M Holt Elementary"      "Math Grade 3"     47.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10340 "Elon Elementary"              "Math Grade 5"     33.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10340 "Elon Elementary"              "Math Grade 3"     25.9 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10340 "Elon Elementary"              "Math Grade 4"     33.6 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10340 "Elon Elementary"              "Reading Grade 4"  49.2 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10340 "Elon Elementary"              "Reading Grade 5"  37.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10340 "Elon Elementary"              "Science Grade 5"  45.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10346 "B. Everett Jordan Elementary" "Math Grade 5"       23 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10346 "B. Everett Jordan Elementary" "Reading Grade 5"  34.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10346 "B. Everett Jordan Elementary" "Math Grade 4"     18.8 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10346 "B. Everett Jordan Elementary" "Math Grade 3"     17.6 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10346 "B. Everett Jordan Elementary" "Reading Grade 4"  40.6 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10346 "B. Everett Jordan Elementary" "Science Grade 5"  54.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10347 "Audrey W. Garrett Elementary" "Math Grade 4"     41.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10347 "Audrey W. Garrett Elementary" "Reading Grade 4"  54.8 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10347 "Audrey W. Garrett Elementary" "Science Grade 5"  40.4 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10347 "Audrey W. Garrett Elementary" "Math Grade 3"     46.1 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10347 "Audrey W. Garrett Elementary" "Math Grade 5"     25.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10347 "Audrey W. Garrett Elementary" "Reading Grade 5"  41.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10348 "Graham High"                  "NC Math 1 (9-12)"  7.6 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10348 "Graham High"                  "Biology"            24 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10348 "Graham High"                  "NC Math 3 (9-12)" 30.7 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10348 "Graham High"                  "English II"       38.5 1 1 "Alamance County"
2020 "Alamance-Burlington Schools" 10350 "Graham Middle"                "Reading Grade 7"  28.7 1 1 "Alamance County"

If this still is not a desired result please let me know and I will format it accordingly.

[Susie Truitt]

The dataex did not include this but the 2019 results are in the same dataset and formatted the exact same way, just with 2019 as the year. I wish to generate a new variable that captures the 2020-2019 result for each test and each school and places it after County.

[Ken Chui]

See if this works:

bysort SchoolCode Subject (Year): gen score_change = Proficient - Proficient[_n - 1]

[Susie Truitt]

Ken,

It worked, thanks so much!

[Susie Truitt]

Ken,

It worked, thanks so much!