Calculate time differences

[Dirk Enzmann]

Variables paget_01-paget_16 measure the cumulative time in seconds spend by respondents on pages 01 to 16. I want to replace the cumulative times into the absolute times spend on these pages. Here my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input double(paget_01 paget_02 paget_03 paget_04 paget_05 paget_06 paget_07 paget_08 paget_09 paget_10 paget_11 paget_12 paget_13 paget_14 paget_15 paget_16)
 5  8 12 16 23 30  . 34  . 38  . 43  .  .   .  52
 3  4  6  7  9 11 19 21  . 22  . 23 29 32  34  35
 3  7 10 12 15 18  . 20  . 21  . 23  .  .   .  26
 6 12 15 18 25 29  . 39  . 44  . 49  . 81 184 191
 4  6  7  8 10 11  . 12  . 13  . 14  . 16  18  19
 3  6  9 10 18 28  . 32  . 38  . 42  .  .   .  55
 4  9 12 14 18 29  . 34  . 38  . 43  .  .   .  51
 6 12 16 20 32 38  . 43  . 49  . 58  .  .   .  70
 3  7  9 13 19 24  . 28  . 33  . 40 51  .   .  59
 3  5  8 10 15 23 32 41  . 45  . 53  .  .   .  60
16 25 28 31 38 43  . 49  . 58  . 65  . 91 119 129
 3 11 14 17 22 30  . 34  . 38  . 44  . 77  99 109
 3  6  9 11 18 35  . 43  . 49 69 81  .  .   .  89
 4  8 12 16 23 31  . 40 51 59  . 68  . 97 124 136
end

The first case took 52 seconds in total on pages 01-16. If the time is missing, 0 seconds are spend on the page. I want to have the time spend on each page, not the time accumulated. For the first case this would be

5 3 4 4 7 7 0 4 0 4 0 5 0 0 0 9

I tried to solve the problem by (a) first replacing casewise missing values by the value of the preceding variable, to (b) subsequently calculate the differences. However, my attempt to do the first failed already because using my attempt the new variables (paget_01tmp ...) do not contain the values of the preceding variable if missing:

qui ds paget_01-paget_15
local vlist1 = r(varlist)
qui ds paget_02-paget_16
local vlist2 = r(varlist)

gen paget_01tmp = paget_01
forvalues i = 1/15 {
   local v1 : word `i' of `vlist1'
   local v2 : word `i' of `vlist2'
   gen `v2'tmp = cond(`v2'==.,`v1'tmp,`v2')
}

Any ideas about what is wrong in my attempt and/or how to solve the problem efficiently?

[Øyvind Snilsberg]

g n=_n
ren paget_0* paget_*
reshape long paget_, i(n)

by n: replace paget = paget[_n-1] if paget==.
by n: g wanted = cond(_n>1,paget-paget[_n-1],paget[1])

drop paget

reshape wide wanted, i(n) j(_j)

[Leonardo Guizzetti]

I was going to suggest essentially an identical solution as Oyvind. It's a good solution, and while the reshape may be less time efficient in large datasets, it's a safer way to proceed that trying to use the loops because of the enforcement of ordering of pages.

[Dirk Enzmann]

Thanks a lot, Øyvind!

Your solution is perfect if I replace "paget" by "paget_" in lines 5-8.