Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Sum across rows with specified start and end points

    Hello,

    I have a wide dataset (dataex example below) that has 17 different defined intervals. For each person, these intervals start and end on different days (between 1 and 180). The start and end dates for each interval are named int1start, int1end, int2start, int2end, etc. I also have 180 "day" variables that are named day1 day2 day3 etc. I would like to sum across rows within each specified interval. For example, if int1start is 1 and int1end is 5, I would like a sum of the values that represents day1+day2+day3+day4+day5. I would like to create this sum for all 17 intervals for each person.

    Any help would be very much appreciated!

    Thank you,

    Sarah



    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input double pid byte(int1start int1end int2start int2end int3start int3end) int(int4start int4end int5start int5end int6start int6end) double(day1 day2 day3 day4 day5 day6 day7 day8 day9 day10 day11 day12 day13 day14 day15 day16 day17 day18 day19 day20)
1000006 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000008 1 13 14 27 28 41  42  55  56  69  70  83 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 0
1000012 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000048 1  7 11 11 21 34  35  48  49  53  81  82 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000063 1 14 19 32 33 46  47  60  73  86  87 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 0 1
1000070 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0
1000081 1 13 14 27 28 41  29  42  43  56  69  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0
1000089 1  1  2 15 16 29  31  44  45  58  59  72 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0
1000098 1  4  5 18 19 32  33  46  47  60  61  74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1
1000111 1  8  9 22 36 49  37  50  51  64  65  78 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000115 1 11 12 25 26 39  40  53  54  67  68  81 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000130 1  4  5 18 19 32  33  46  47  60  61  74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000133 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000157 1 13 14 27 28 41  29  42  43  56  69  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000159 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0
1000160 1  6  7 20 22 35  36  49  50  63  64  77 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000180 1  9 10 23 24 37  38  51  52  65  71  75 0 0 0 0 0 0 1 1 1 1 1 0 0 1 0 0 1 0 1 1
1000181 1  2  6 19 15 28  29  42  43  56  57  60 0 0 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000213 1  9 17 30 31 44  45  47  54  67  68  81 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000214 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000224 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000235 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000242 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0
1000245 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000248 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1
1000250 1  5 10 23 24 37  38  51  52  65  66  79 1 0 0 0 0 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0
1000271 1  2  4 17 15 23  25  26  32  45  46  57 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000281 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
1000296 1  6  7 20 21 34  35  48  55  68  69  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1
1000312 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000318 1  3  4 17 23 36  37  50  51  64  65  78 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2
1000319 1  2  3 16 18 31  32  45  46  59  60  73 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000327 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000328 1  8  9 22 23 36  37  50   .   .   .   . 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000334 1 11 12 25 26 39  44  54  56  69  70  83 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000343 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0
1000346 1  3  4 17 18 31  32  45  46  59  60  73 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 1 1 1
1000354 1  8 38 51 52 63  74  87  88 101 102 115 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 7 2 2
1000377 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000385 1  4  5 18 21 34  35  48  49  62  63  76 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000390 1  7  8 21 22 35  36  49  50  63  64  77 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1
1000391 1  6  7 20 21 34  35  48  49  62  63  76 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 0 0 1
1000399 1  3  3 16 17 30  32  45  46  48  50  63 0 0 0 1 1 1 0 1 1 1 1 1 1 1 1 1 1 1 1 1
1000402 1  9 10 23 24 37  38  51  52  65  66  79 0 0 0 0 0 0 1 1 1 0 0 1 0 0 1 1 0 0 0 0
1000409 1 12 13 26 27 40  41  54  55  68  69  82 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
1000434 1 10 11 24 25 38  39  52  53  66  80  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000441 1 10 11 24 25 38  39  52  56  57  62  75 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
1000483 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000488 1  4  5 18 24 37  38  49  68  81  82  95 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 0 2 2
1000512 1 12 13 26 27 40  44  57  58  71  72  81 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1
1000525 1  2  3 16 17 30  31  44  45  58  59  72 0 0 0 0 0 0 0 1 0 1 1 1 1 1 1 1 1 1 1 0
1000530 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 1 0 1 0 1 1 1 1 1 1 1
1000533 1  2  3 16  .  .   .   .   .   .   .   . 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 3 3 3 3
1000534 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000537 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
1000549 1  9 10 23 24 37  38  51  52  65  66  79 0 0 0 0 0 0 1 1 1 2 2 2 1 0 0 1 1 1 1 1
1000598 1 14 15 28 32 37  48  60  62  62  70  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000627 1  2  4  8 13 16  22  30  32  35  38  51 0 0 6 0 0 0 0 0 2 2 2 2 0 0 0 0 2 2 2 2
1000628 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 1 1 1 1
1000653 1 12 13 26 27 40  41  54  55  68  71  84 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1
1000676 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000678 1  1  2 15 16 29  30  43  44  57  58  71 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0 0
1000679 1 12 13 26 27 40  41  54  55  68  69  82 0 0 0 0 0 0 0 0 1 0 0 1 1 1 1 1 1 1 1 0
1000694 1  9 11 17 63 76   .   .   .   .   .   . 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0 1 1 2 2 2
1000698 1  4  5 18 20 33  34  42  46  54  71  77 0 0 0 0 0 0 0 0 1 0 1 1 0 0 1 1 0 1 4 1
1000735 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000740 1  6  7 20 21 34  39  52  53  66  67  80 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000742 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 1 0
1000752 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000763 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0
1000775 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000788 1 12 13 26 27 40  41  54  55  68  69  82 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000796 1 10 14 19 25 38  39  47  53  66  67  80 0 0 1 1 1 1 1 1 1 1 2 2 7 0 0 1 1 1 1 2
1000830 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 1 1 1 1
1000846 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000848 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000856 1  5  6 19 20 33  34  47  48  61  62  75 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000860 1  2  3 16 17 30  31  44  45  58  59  72 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0 0 0
1000861 1 12 13 26 27 40  41  54  55  68  69  82 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000869 1  3  9 22 23 36  37  50  51  64  65  77 0 0 0 2 2 2 2 2 1 0 0 0 0 1 1 1 1 1 1 1
1000870 1  7  8 21 22 35  36  49  50  63  74  87 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000892 1  3  9 22 23 36  39  52  53  66  67  80 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000938 1  2  3 16 17 30  31  44  45  58  59  72 0 0 0 0 0 0 0 0 1 1 1 1 1 0 1 1 0 1 0 1
1000971 1  3  4 17 22 35  36  49  50  63  64  77 0 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 2 2 2
1000998 1  1  2 15 19 23  29  42  43  56  57  60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 2 2 1 1
1001000 1 14 15 28 35 48  49  62  63  70  76  89 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1
1001014 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001031 1  2  3 16 26 39  40  53  54  67  68  81 0 0 0 0 0 0 0 0 1 0 1 1 1 0 1 1 2 2 2 2
1001056 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001060 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001061 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1
1001073 1  9 10 23 29 42  43  56  57  70  71  74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
1001074 1  7  8 21 22 35  36  49  50  63  74  87 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
1001093 1  6  9 22 23 36  37  50  51  64  65  78 0 0 1 1 1 1 2 2 0 0 0 0 1 1 1 1 0 1 0 1
1001094 1 12 13 26 27 40  41  54  55  68  70  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001111 1 13 14 27 28 41  42  55  56  69  70  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1
1001120 1  1 51 51 55 60 102 115 116 129 130 143 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1001130 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 0
1001155 5 12 13 26 28 41  42  55  56  69  70  83 2 2 2 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2000005 1  8  9 22 23 36  37  50  51  64  65  78 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
end
    Tags: None
  • #2
    This is a peculiar data layout, and one that is really quite inconvenient for use in Stata. So the major obstacle here is finding a better data organization. A particular issue is that there are 180 day variables but only 17 interval pairs, and basically you need to associate all 180 day variables with each of the 17 intervals, and then selectively calculate totals. The following code works (more or less) in your data example:

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input double pid byte(int1start int1end int2start int2end int3start int3end) int(int4start int4end int5start int5end int6start int6end) double(day1 day2 day3 day4 day5 day6 day7 day8 day9 day10 day11 day12 day13 day14 day15 day16 day17 day18 day19 day20)
1000006 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000008 1 13 14 27 28 41  42  55  56  69  70  83 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 0
1000012 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000048 1  7 11 11 21 34  35  48  49  53  81  82 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000063 1 14 19 32 33 46  47  60  73  86  87 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 0 1
1000070 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0
1000081 1 13 14 27 28 41  29  42  43  56  69  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0
1000089 1  1  2 15 16 29  31  44  45  58  59  72 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0
1000098 1  4  5 18 19 32  33  46  47  60  61  74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1
1000111 1  8  9 22 36 49  37  50  51  64  65  78 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000115 1 11 12 25 26 39  40  53  54  67  68  81 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000130 1  4  5 18 19 32  33  46  47  60  61  74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000133 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000157 1 13 14 27 28 41  29  42  43  56  69  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000159 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0
1000160 1  6  7 20 22 35  36  49  50  63  64  77 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000180 1  9 10 23 24 37  38  51  52  65  71  75 0 0 0 0 0 0 1 1 1 1 1 0 0 1 0 0 1 0 1 1
1000181 1  2  6 19 15 28  29  42  43  56  57  60 0 0 2 2 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000213 1  9 17 30 31 44  45  47  54  67  68  81 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000214 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000224 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000235 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000242 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0
1000245 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000248 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1
1000250 1  5 10 23 24 37  38  51  52  65  66  79 1 0 0 0 0 2 2 2 2 0 0 0 0 0 0 0 0 0 0 0
1000271 1  2  4 17 15 23  25  26  32  45  46  57 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000281 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1
1000296 1  6  7 20 21 34  35  48  55  68  69  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1
1000312 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000318 1  3  4 17 23 36  37  50  51  64  65  78 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2
1000319 1  2  3 16 18 31  32  45  46  59  60  73 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000327 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000328 1  8  9 22 23 36  37  50   .   .   .   . 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000334 1 11 12 25 26 39  44  54  56  69  70  83 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000343 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0
1000346 1  3  4 17 18 31  32  45  46  59  60  73 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 1 1 1
1000354 1  8 38 51 52 63  74  87  88 101 102 115 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 7 2 2
1000377 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000385 1  4  5 18 21 34  35  48  49  62  63  76 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000390 1  7  8 21 22 35  36  49  50  63  64  77 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1
1000391 1  6  7 20 21 34  35  48  49  62  63  76 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 0 0 1
1000399 1  3  3 16 17 30  32  45  46  48  50  63 0 0 0 1 1 1 0 1 1 1 1 1 1 1 1 1 1 1 1 1
1000402 1  9 10 23 24 37  38  51  52  65  66  79 0 0 0 0 0 0 1 1 1 0 0 1 0 0 1 1 0 0 0 0
1000409 1 12 13 26 27 40  41  54  55  68  69  82 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
1000434 1 10 11 24 25 38  39  52  53  66  80  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000441 1 10 11 24 25 38  39  52  56  57  62  75 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
1000483 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000488 1  4  5 18 24 37  38  49  68  81  82  95 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 0 2 2
1000512 1 12 13 26 27 40  44  57  58  71  72  81 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1
1000525 1  2  3 16 17 30  31  44  45  58  59  72 0 0 0 0 0 0 0 1 0 1 1 1 1 1 1 1 1 1 1 0
1000530 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 1 0 1 0 1 1 1 1 1 1 1
1000533 1  2  3 16  .  .   .   .   .   .   .   . 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 3 3 3 3
1000534 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000537 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
1000549 1  9 10 23 24 37  38  51  52  65  66  79 0 0 0 0 0 0 1 1 1 2 2 2 1 0 0 1 1 1 1 1
1000598 1 14 15 28 32 37  48  60  62  62  70  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000627 1  2  4  8 13 16  22  30  32  35  38  51 0 0 6 0 0 0 0 0 2 2 2 2 0 0 0 0 2 2 2 2
1000628 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 1 1 1 1
1000653 1 12 13 26 27 40  41  54  55  68  71  84 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1
1000676 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000678 1  1  2 15 16 29  30  43  44  57  58  71 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0 0
1000679 1 12 13 26 27 40  41  54  55  68  69  82 0 0 0 0 0 0 0 0 1 0 0 1 1 1 1 1 1 1 1 0
1000694 1  9 11 17 63 76   .   .   .   .   .   . 0 0 0 0 0 0 0 0 0 6 0 0 0 0 0 1 1 2 2 2
1000698 1  4  5 18 20 33  34  42  46  54  71  77 0 0 0 0 0 0 0 0 1 0 1 1 0 0 1 1 0 1 4 1
1000735 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000740 1  6  7 20 21 34  39  52  53  66  67  80 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000742 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 1 0
1000752 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000763 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0
1000775 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1000788 1 12 13 26 27 40  41  54  55  68  69  82 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000796 1 10 14 19 25 38  39  47  53  66  67  80 0 0 1 1 1 1 1 1 1 1 2 2 7 0 0 1 1 1 1 2
1000830 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 1 1 1 1
1000846 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000848 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000856 1  5  6 19 20 33  34  47  48  61  62  75 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000860 1  2  3 16 17 30  31  44  45  58  59  72 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 0 0 0 0 0
1000861 1 12 13 26 27 40  41  54  55  68  69  82 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000869 1  3  9 22 23 36  37  50  51  64  65  77 0 0 0 2 2 2 2 2 1 0 0 0 0 1 1 1 1 1 1 1
1000870 1  7  8 21 22 35  36  49  50  63  74  87 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000892 1  3  9 22 23 36  39  52  53  66  67  80 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000938 1  2  3 16 17 30  31  44  45  58  59  72 0 0 0 0 0 0 0 0 1 1 1 1 1 0 1 1 0 1 0 1
1000971 1  3  4 17 22 35  36  49  50  63  64  77 0 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 2 2 2
1000998 1  1  2 15 19 23  29  42  43  56  57  60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2 2 2 1 1
1001000 1 14 15 28 35 48  49  62  63  70  76  89 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1
1001014 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001031 1  2  3 16 26 39  40  53  54  67  68  81 0 0 0 0 0 0 0 0 1 0 1 1 1 0 1 1 2 2 2 2
1001056 1 14 15 28 29 42  43  56  57  70  71  84 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001060 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001061 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1
1001073 1  9 10 23 29 42  43  56  57  70  71  74 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
1001074 1  7  8 21 22 35  36  49  50  63  74  87 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
1001093 1  6  9 22 23 36  37  50  51  64  65  78 0 0 1 1 1 1 2 2 0 0 0 0 1 1 1 1 0 1 0 1
1001094 1 12 13 26 27 40  41  54  55  68  70  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1001111 1 13 14 27 28 41  42  55  56  69  70  83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 1
1001120 1  1 51 51 55 60 102 115 116 129 130 143 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1001130 1 14 15 28 29 42  43  56  57  70  71  84 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 0
1001155 5 12 13 26 28 41  42  55  56  69  70  83 2 2 2 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2000005 1  8  9 22 23 36  37  50  51  64  65  78 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
end

isid pid

reshape long int@start int@end, i(pid) j(_j)
drop if missing(intstart, intend)
reshape long day, i(pid intstart intend) j(_k)
by pid intstart intend: egen desired = total(cond(inrange(_k, intstart, intend), ///
    day, .))
reshape wide

//    I RECOMMEND YOU STOP HERE AND NOT USE THE LINES OF CODE BELOW.

//    AND IF YOU NEED TO GO BACK TO THE DOUBLE-WIDE LAYOUT
reshape wide int@start int@end desired, i(pid) j(_j)
    Comments:

    It does not appear possible to reshape this data to fully long layout in one step because we need to separately track the number of the day variable and the number of the interval variables. The results in this data may be a bit peculiar because, for example you have intervals like 130 to 143, but the day variables shown only go up to 20, so the sum for this interval will be zero because it is a sum over the empty set. I assume that in your real data this situation does not arise as you say that you actually have 180 variables, and the maximum value of an int*end variable is 180.

    Depending on what you plan to do next, I recommend that you leave the data in the layout that is long for intervals but wide for days. I think this will probably prove the most congenial to most additional data management and analysis, but without knowing exactly what your plans are, I can't assert this with high confidence. Hence the final line of code which will return you to your original layout if that is what you need to do. (But it's hard for me to imagine anything that can be done in a straightforward manner with that layout in Stata.) It may be that skipping even the simple -reshape wide- command just be fore that comment, and keeping the data in double-long layout is best.

    Comment

    • #3
      Hi Clyde,

      Thanks for this! This was exactly what I needed. I agree the double-wide format is weird; I'll stick to long.

      Sarah

      Comment

      Previous Next
      Working...
      X