Calculate difference of an outcome variable between timepoints

[Nikolaos Pandis]

Hi,

I have the following dataset in a long format with 6 records per patient_id corresponding to 3 timepoints and 2 jaws:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int patient_id byte timepoint long(jaw retainer) double irreg_index
1 1 2 1  4.2
1 1 1 2    .
1 2 2 1 1.71
1 2 1 2  .97
1 3 2 1  .58
1 3 1 2  .22
3 1 1 3 1.18
3 1 2 1 9.83
3 2 2 1  .66
3 2 1 3  .85
3 3 1 3  .51
3 3 2 1  .98
end

I would like to calculate the difference of the irreg_index variable between timepoint3-timepoint1 per jaw separately.

I have tried to reshape my data wide in order to generate the difference :

. gen id=_n

. reshape wide irreg_index ,i( id) j(timepoint)
(note: j = 1 2 3)

Data long -> wide
-----------------------------------------------------------------------------
Number of obs. 450 -> 450
Number of variables 6 -> 7
j variable (3 values) timepoint -> (dropped)
xij variables:
irreg_index -> irreg_index1 irreg_index2 irreg_index3
-----------------------------------------------------------------------------
but as the number of rows is maintained I get many empty cells and I cannot calculate the difference as further data manipulation is needed.
Any advice would be appreciated.

Thank you,

Nikos

[Liu Qiang]

Hi Nikolaos, I think the following code my help you achieve what you want. But no idea on " further data manipulation".

sort patient_id timepoint jaw
reshape wide irreg_index, i(patient_id jaw) j(timepoint)
gen dif=irreg_index3- irreg_index1

Best

[Nikolaos Pandis]

Thank you Liu.

Unfortunately your suggestion did not work.

reshape wide irreg_index, i(id jaw) j(timepoint) I used id instead of patient_id as patient_id not unique in the long format and I still have in wide 450 rows (same number as in long format) and for the difference 450 missing generated values. The irreg_index 1-3 columns are not stacked next to each other and this is why the differences are missing:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id int patient_id long jaw double(irreg_index1 irreg_index2 irreg_index3) float dif long retainer
435 1 2    .    . .58 . 1
272 1 2  4.2    .   . . 1
 29 1 1    .    .   . . 2
201 1 1    .    . .22 . 2
127 1 1    .  .97   . . 2
355 1 2    . 1.71   . . 1
381 3 2    .    . .98 . 1
105 3 1    .  .85   . . 3
172 3 1    .    . .51 . 3
241 3 2 9.83    .   . . 1
365 3 2    .  .66   . . 1
 51 3 1 1.18    .   . . 3
end

Best, Nikos

[Liu Qiang]

alright, can you first of all list what you want to achieve then? For example, what would be your intended data structure and new variable? You can compute a few observations by hand and offer some details so that others, if I am incapable of doing that, learn how to help you afterwards?
Best

[Nikolaos Pandis]

Thank you Liu
Please see below final intended data structure which would allow to calculate the difference irreg_3-irreg_2 :

id patien~d jaw irreg_~1 irreg_~2 irreg_~3 dif retainer |
|-----------------------------------------------------------------------------|
1. | 435 1 maxilla 2 0 .58 .58 ss4 |
2. | 272 1 maxilla 4.2 0.1 1 .99 ss4 |
3. | 29 1 mandible 3 .5 2.7 2.2 ss6

Thank you,
Nikos