"Not sorted"

[Sumedha Gupta]

Dear All,
I have individual level panel data that includes spells, with a 6 month *follow up* period after each spell as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(id month) byte spell float spell_followup double(base_y followup_y) float followupmonth
52   1 0 .                 .                  . .
52   2 0 .                 .                  . .
52   3 0 .                 .                  . .
52   4 0 .                 .                  . .
52   5 0 .                 .                  . .
52   6 0 .                 .                  . .
52   7 0 .                 .                  . .
52   8 0 .                 .                  . .
52   9 0 .                 .                  . .
52  10 0 .                 .                  . .
52  11 0 .                 .                  . .
52  12 0 .                 .                  . .
52  13 0 .                 .                  . .
52  14 0 .                 .                  . .
52  15 0 .                 .                  . .
52  16 0 .                 .                  . .
52  17 1 .                 .                  . .
52  18 1 .                 .                  . .
52  19 1 .                 .                  . .
52  20 1 . 30.35714340209961                  . .
52  21 0 1                 .              21.25 1
52  22 2 1                 .              21.25 2
52  23 2 1                 .              21.25 3
52  24 2 1                 .              21.25 4
52  25 2 1                 .              21.25 5
52  26 2 1             21.25              21.25 6
52  27 0 2                 .                  0 1
52  28 0 2                 .  32.04545593261719 2
52  29 0 2                 .  69.54545593261719 3
52  30 0 2                 .                 75 4
52  31 0 2                 .               37.5 5
52  32 0 2                 .                  0 6
52  33 0 .                 .                  . .
52  34 0 .                 .                  . .
52  35 0 .                 .                  . .
52  36 0 .                 .                  . .
52  37 0 .                 .                  . .
52  38 0 .                 .                  . .
52  39 0 .                 .                  . .
52  40 0 .                 .                  . .
52  41 0 .                 .                  . .
52  42 0 .                 .                  . .
52  43 0 .                 .                  . .
52  44 0 .                 .                  . .
52  45 0 .                 .                  . .
52  46 0 .                 .                  . .
52  47 0 .                 .                  . .
52  48 0 .                 .                  . .
52  49 0 .                 .                  . .
52  50 0 .                 .                  . .
52  51 0 .                 .                  . .
52  52 0 .                 .                  . .
52  53 0 .                 .                  . .
52  54 0 .                 .                  . .
52  55 0 .                 .                  . .
52  56 0 .                 .                  . .
52  57 0 .                 .                  . .
52  58 0 .                 .                  . .
52  59 0 .                 .                  . .
52  60 0 .                 .                  . .
52  61 0 .                 .                  . .
52  62 0 .                 .                  . .
52  63 0 .                 .                  . .
52  64 0 .                 .                  . .
52  65 0 .                 .                  . .
52  66 0 .                 .                  . .
52  67 0 .                 .                  . .
52  68 0 .                 .                  . .
52  69 0 .                 .                  . .
52  70 0 .                 .                  . .
52  71 0 .                 .                  . .
52  72 0 .                 .                  . .
52  73 0 .                 .                  . .
52  74 0 .                 .                  . .
52  75 0 .                 .                  . .
52  76 0 .                 .                  . .
52  77 0 .                 .                  . .
52  78 0 .                 .                  . .
52  79 0 .                 .                  . .
52  80 0 .                 .                  . .
52  81 0 .                 .                  . .
52  82 0 .                 .                  . .
52  83 0 .                 .                  . .
52  84 0 .                 .                  . .
52  85 0 .                 .                  . .
52  86 3 .                 .                  . .
52  87 3 .                 .                  . .
52  88 3 .             23.25                  . .
52  89 0 3                 . 10.576614379882813 1
52  90 0 3                 . 5.2016143798828125 2
52  91 0 3                 .                  0 3
52  92 0 3                 .                 10 4
52  93 0 3                 .             27.375 5
52  94 0 3                 .              34.75 6
52  95 0 .                 .                  . .
52  96 0 .                 .                  . .
52  97 0 .                 .                  . .
52  98 0 .                 .                  . .
52  99 0 .                 .                  . .
52 100 0 .                 .                  . .
end

In each of the 6 month follow up period, I want to check if the deviation from the base_y is greater than 15/30/40%. The code I was trying to run is:

tsset id month
bysort id spell_followup (followupmonth): gen var15=((l1.base_y-followup_y)/l1.base_y/>.15) if followupmonth==1
bysort id spell_followup (followupmonth): gen var30=((l1.base_y-followup_y)/l1.base_y/>.30) if followupmonth==1
bysort id spell_followup (followupmonth): gen var50=((l1.base_y-followup_y)/l1.base_y/>.50) if followupmonth==1

But I get an error "not sorted".

. bysort id spell_followup (followupmonth): gen var15=((l1.base_y-followup_y)/l1.ba
> se_y/>.15) if followupmonth==1
not sorted
r(5);

end of do-file

r(5);

I did try to tsset the data and use bysort. But, I am clearly messing up somewhere but the error doesn't give a lot of detail and I am stuck. I will be grateful for your help.
Sincerely,
Sumedha.

[Clyde Schechter]

The use of the l1 operator requires that the data be sorted consistently with the -tsset- command, but your -bysort...- commands specify a different sort. That's why you get the "not sorted" error message.

In any case, the main problem here is that your data organization puts the value of base_y in an observation outside the range of observations in the follow-up period where it is needed. Perhaps you can fix that by redoing the data management that led up to this point. But if not, you can do this instead:

sort id month
rangestat (lastnm) comparator = base_y, by(id) interval(month . -1)
foreach x in 15 30 50 {
    gen byte var`x' = 100*(comparator-followup_y)/comparator > `x' if !missing(followup_y)
}

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer; it is available from SSC.

[Sumedha Gupta]

Thank you Prof. Schechter. Your code helps me achieve what I asked for but creates an issue in the next step (which I had not posted as I hadn't envisaged this issue cropping up). To explain, I want to eventually create 3 dummies in the month just before the start of the spell (spell_start==1) for whether any of the

var15/var30/var50>0 in the

6 months from the start of the spell

(month spell starts+5 subsequent months)

. Your code works perfectly when the spell lasts for 6 months. But sometimes spells are shorter than 6 months and then the var15/var30/var50 variables are not constructed for all 6 months. The following example illustrates:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(id month) float spell_start byte spell float spell_followup double(base_y followup_y) float followupmonth double comparator byte(var15 var30 var50)
52   1 . 0 .                 .                  . .                 . . . .
52   2 . 0 .                 .                  . .                 . . . .
52   3 . 0 .                 .                  . .                 . . . .
52   4 . 0 .                 .                  . .                 . . . .
52   5 . 0 .                 .                  . .                 . . . .
52   6 . 0 .                 .                  . .                 . . . .
52   7 . 0 .                 .                  . .                 . . . .
52   8 . 0 .                 .                  . .                 . . . .
52   9 . 0 .                 .                  . .                 . . . .
52  10 . 0 .                 .                  . .                 . . . .
52  11 . 0 .                 .                  . .                 . . . .
52  12 . 0 .                 .                  . .                 . . . .
52  13 . 0 .                 .                  . .                 . . . .
52  14 . 0 .                 .                  . .                 . . . .
52  15 . 0 .                 .                  . .                 . . . .
52  16 . 0 .                 .                  . .                 . . . .
52  17 . 1 .                 .                  . .                 . . . .
52  18 . 1 .                 .                  . .                 . . . .
52  19 . 1 . 30.35714340209961                  . .                 . . . .
52  20 1 1 1                 . 25.803571701049805 1 30.35714340209961 1 0 0
52  21 . 0 1                 .              21.25 2 30.35714340209961 1 1 0
52  22 . 2 1                 .              21.25 3 30.35714340209961 1 1 0
52  23 . 2 1                 .              21.25 4 30.35714340209961 1 1 0
52  24 . 2 1             21.25              21.25 5 30.35714340209961 1 1 0
52  25 1 2 2                 .              21.25 1             21.25 0 0 0
52  26 . 2 2                 .             10.625 2             21.25 1 1 0
52  27 . 0 2                 .                  0 3             21.25 1 1 1
52  28 . 0 2                 .  32.04545593261719 4             21.25 0 0 0
52  29 . 0 2                 .  69.54545593261719 5             21.25 0 0 0
52  30 . 0 2                 .                 75 6             21.25 0 0 0
52  31 . 0 .                 .                  . .             21.25 . . .
52  32 . 0 .                 .                  . .             21.25 . . .
52  33 . 0 .                 .                  . .             21.25 . . .
52  34 . 0 .                 .                  . .             21.25 . . .
52  35 . 0 .                 .                  . .             21.25 . . .
52  36 . 0 .                 .                  . .             21.25 . . .
52  37 . 0 .                 .                  . .             21.25 . . .
52  38 . 0 .                 .                  . .             21.25 . . .
52  39 . 0 .                 .                  . .             21.25 . . .
52  40 . 0 .                 .                  . .             21.25 . . .
52  41 . 0 .                 .                  . .             21.25 . . .
52  42 . 0 .                 .                  . .             21.25 . . .
52  43 . 0 .                 .                  . .             21.25 . . .
52  44 . 0 .                 .                  . .             21.25 . . .
52  45 . 0 .                 .                  . .             21.25 . . .
52  46 . 0 .                 .                  . .             21.25 . . .
52  47 . 3 .                 .                  . .             21.25 . . .
52  48 . 0 .                 .                  . .             21.25 . . .
52  49 . 0 .                 .                  . .             21.25 . . .
52  50 . 0 .                 .                  . .             21.25 . . .
52  51 . 0 .                 .                  . .             21.25 . . .
52  52 . 0 .                 .                  . .             21.25 . . .
52  53 . 0 .                 .                  . .             21.25 . . .
52  54 . 0 .                 .                  . .             21.25 . . .
52  55 . 0 .                 .                  . .             21.25 . . .
52  56 . 0 .                 .                  . .             21.25 . . .
52  57 . 0 .                 .                  . .             21.25 . . .
52  58 . 0 .                 .                  . .             21.25 . . .
52  59 . 0 .                 .                  . .             21.25 . . .
52  60 . 0 .                 .                  . .             21.25 . . .
52  61 . 0 .                 .                  . .             21.25 . . .
52  62 . 0 .                 .                  . .             21.25 . . .
52  63 . 0 .                 .                  . .             21.25 . . .
52  64 . 0 .                 .                  . .             21.25 . . .
52  65 . 0 .                 .                  . .             21.25 . . .
52  66 . 0 .                 .                  . .             21.25 . . .
52  67 . 0 .                 .                  . .             21.25 . . .
52  68 . 0 .                 .                  . .             21.25 . . .
52  69 . 0 .                 .                  . .             21.25 . . .
52  70 . 0 .                 .                  . .             21.25 . . .
52  71 . 0 .                 .                  . .             21.25 . . .
52  72 . 0 .                 .                  . .             21.25 . . .
52  73 . 0 .                 .                  . .             21.25 . . .
52  74 . 0 .                 .                  . .             21.25 . . .
52  75 . 0 .                 .                  . .             21.25 . . .
52  76 . 0 .                 .                  . .             21.25 . . .
52  77 . 0 .                 .                  . .             21.25 . . .
52  78 . 0 .                 .                  . .             21.25 . . .
52  79 . 0 .                 .                  . .             21.25 . . .
52  80 . 0 .                 .                  . .             21.25 . . .
52  81 . 0 .                 .                  . .             21.25 . . .
52  82 . 0 .                 .                  . .             21.25 . . .
52  83 . 0 .                 .                  . .             21.25 . . .
52  84 . 0 .                 .                  . .             21.25 . . .
52  85 . 0 .                 .                  . .             21.25 . . .
52  86 . 4 .                 .                  . .             21.25 . . .
52  87 . 4 .                 .                  . .             21.25 . . .
52  88 . 4 .             23.25                  . .             21.25 . . .
52  89 1 0 3                 . 10.576614379882813 1             23.25 1 1 1
52  90 . 0 3                 . 5.2016143798828125 2             23.25 1 1 1
52  91 . 0 3                 .                  0 3             23.25 1 1 1
52  92 . 0 3                 .                 10 4             23.25 1 1 1
52  93 . 0 3                 .             27.375 5             23.25 0 0 0
52  94 . 0 3                 .              34.75 6             23.25 0 0 0
52  95 . 0 .                 .                  . .             23.25 . . .
52  96 . 0 .                 .                  . .             23.25 . . .
52  97 . 0 .                 .                  . .             23.25 . . .
52  98 . 0 .                 .                  . .             23.25 . . .
52  99 . 0 .                 .                  . .             23.25 . . .
52 100 . 0 .                 .                  . .             23.25 . . .
end

For this individual the spell that starts in month 20 only lasts for 5 months and then the next spell starts. But I still would like to create 3 dummies in month 19 (the month just before the start of the spell) that report if var15>0 in any of the subsequent 6 months, var30>0 in any of the subsequent 6 months, and var50>0 in any of the subsequent 6 months.

Sorry for the confusing/complex data structure... there is some purpose to it , and I will be very very grateful for your help.

Sincerely,

Sumedha.

[Clyde Schechter]

rangestat (max) var15 var30 var50, by(id) interval(month1 6)
foreach v of varlist var*_max {
    replace `v' = . if spell_start != 1
}