Transforming panel data with different time points

[Chris Laugen]

Hello, I am using data collected from 36 different hospitals over the course of 7 quarters (2015q2 - 2016q4). The data consist of assessment scores measured from 0-100 percent (one score per assessment tool). I would like to use the final score for each hospital in a regression model and control for the baseline score. I received the data in long format and generated a variable for the quarter that each assessment score was made. The number of scores per hospital vary between 3 and 7, and the assessment dates also begin at different quarters during this time period. In an effort to use only the first and last assessment score, I kept only these for each hospital and then reshaped the data into wide format. However, due to the different starting and ending points, I am not sure how to make the data align so that I can use it in an analysis. Is there a way to "line up" all of the data to help with subsequent analysis?

These are the steps I have taken so far:

by facID, sort: keep if (_n == 1) | (_n == _N)

reshape wide tool_1_maternal tool_2_maternal tool_3_maternal tool_4_maternal tool_5_maternal tool_6_maternal total_maternal, i(facID) j(qdate)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 facID byte(tool_1_maternal tool_2_maternal tool_3_maternal tool_4_maternal) int tool_5_maternal byte(tool_6_maternal total_maternal) float qdate
"01"  40 100  67  88 100 63  84 225
"01"  40  67  17  38  50  0  38 223
"01"   0 100  67  88 100 63  76 227
"01"  40 100  80  88  40 75  68 224
"01"   0  50   0  43  25  0  23 222
"01"   0  83  40  86 100 43  71 226
"02"  40  67  20  43  60 29  46 223
"02"   0  33  17  38  17  0  23 222
"02"  40  67  40  75  60 29  61 224
"02"  60 100  80 100 100 63  88 227
"02"  20 100  80  75  75 43  71 226
"02"  80 100  83  86  80 57  86 225
"03"  60  83  67  75  83 63  77 226
"03"   0  50  33  38  33 13  33 223
"03"  40  83  40  63  40 38  57 224
"03"  60  83  80  75 100 63  81 227
"03"  60 100  83  88  83 75  87 225
"04"  80 100  67  50  67 38  59 226
"04"  40  33   0  25  17  0  23 222
"04"   0  17  17  38  17  0  21 221
"04"  80 100  67  75  67 50  74 227
"04"   0  83   0  38  50 25  33 223
"04"   . 100  60  71  33 13  65 225
"04"  80  50  25  25  25 38  40 224
"05"   0  33  17  38  50 13  26 221
"05" 100 100  67  75  60 63  74 224
"05"  20  83  33  50   0 13  38 223
"05"  20  83  33  38  33  0  36 222
"05" 100 100  50 100  83 63  84 226
"05"  80 100  67 100 100 63  87 227
"05"  80 100  33  63  67 63  64 225
"06"  60  67  50  57  33 38  53 227
"06"  20  50  17  25  20 14  24 222
"06"  60  67  25  43  25 38  42 226
"06"  20  50  25  43  25  0  33 225
"06"  20  50  17  25  17  0  23 224
"06"   0  50  20  29  17 13  22 223
"06"   0  17  17  25  50  0  21 221
"07"   0   0   0  13  17 25   5 224
"07"   0  33   0  13   0  0   8 223
"07" 100 100  83 100 100 75  97 227
"07" 100 100  83 100  80 75  95 226
"07"  80 100  67 100  83 75  82 225
"08"  20  50  33  43  40 13  36 224
"08" 100 100  83 100 100 75  97 227
"08"  60  50   0  29  40 13  33 223
"08" 100 100  83  86  75 75  86 226
"08"   0  67   0  13  17 25  15 222
"08" 100 100  80  75  60 71  76 225
"09"  80 100  80 100  80 75  89 227
"09"   0  67   0  43   0  0  22 223
"09"  60 100  80 100  80 50  83 226
"09"   0  50   0  25  33  0  23 224
"09"  60 100  67  71  60 13  72 225
"10"  60  83  50  50  83 63  68 225
"10"   0  67  40  38  50  0  41 224
"10" 100 100  83  75  67 63  82 227
"10"  60  83  50  57  67 38  65 226
"10"   0  67  50  38  67 38  49 223
end

After reshaping my data looks like this for one the assessment tool scores:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 facID byte(tool_1_maternal221 tool_1_maternal222 tool_1_maternal223 tool_1_maternal224 tool_1_maternal225 tool_1_maternal226 tool_1_maternal227)
"01" .  .   .  .  40   0   .
"02" .  .  40  .  80   .   .
"03" .  .   .  .  60  60   .
"04" .  .   . 80   .  80   .
"05" 0  .   .  .  80   .   .
"06" 0  .   .  .   .   .  60
"07" .  .   .  0  80   .   .
"08" .  .   . 20 100   .   .
"09" .  .   .  .  60   .  80
"10" .  .   0  .  60   .   .
"13" .  .   .  . 100 100   .
"14" .  .   .  .   . 100  80
"15" .  .   .  . 100 100   .
"16" .  .   . 80   .  80   .
"17" .  .   .  .  60   .  60
"18" . 60   .  .   .   . 100
"19" .  . 100  .   .   . 100
"22" .  .   .  .  40   . 100
"23" .  .   . 40  60   .   .
"25" .  .  80  .  80   .   .
"26" .  .  60  .   . 100   .
"27" .  .   .  .  80  80   .
"28" .  .  80  .   .  60   .
"29" .  .   0  .  40   .   .
"30" 0 20   .  .   .   .   .
"31" .  .   .  .   . 100 100
"32" .  .   0  . 100   .   .
"33" .  0   . 80   .   .   .
"34" 0  .   .  .   .  80   .
"35" .  0  60  .   .   .   .
"36" 0  .   .  .  80   .   .
end