Making a complex variable from dataset

[Joseph Stein]

I have been stuck on a tabulation which has proven to be rather complex for me and so far I have had no success. I decided to post here hoping that the issue can finally be resolved with gracious help.

I have a dataset of a survey that I have been performing analysis on. I am trying to create one dummy variable called 'premarital' which will take the value of '1' if the man has had his first intercourse prior to his first union/marriage and the value will be '0' if the first intercourse occurred either after or at the same time as the first union/marriage. This will be done by comparing the ages at which the two activities (age at first marriage/union and age at first intercourse) took place. If the man's age at first intercourse is the same as his age at first marriage/union or if his age at first intercourse is later than the age at first marriage/union, then it will contribute to '0'.

A never-married individual may engage in intercourse, so it will contribute to the value of '1' in the dummy variable 'premarital'. However, if the never-married individual hasn't engaged in intercourse, then it'll contribute to '0'.

For the 'premarital' variable, both never-married and ever-married individuals (currently and formerly married) will be taken into account. However, I also seek another variable 'premarital_m' which will only take into account the currently and formerly married individuals and whether their first intercourse occurred before, after or at the same time as first union/marriage.

There are individuals who haven't experienced intercourse yet so they have to be taken out of the equation. Another problem is how to include 'First time when started living with wife' from 'ms10' into the equation so the people who gave this response instead of an age will also contribute to '0'. The 'missing' values also have to be taken out so the calculation can be accurate.

With everything done, I could then tabulate the rate of such pre-union activity with the variables 'premarital' and 'premarital_m' by the respondent's residence, level of education...etc which are provided in the dataset by simply 'tab hh6a premarital' as an example, where hh6a gives the proportion of urban and rural residence for the sample respondents.

I hope this is possible and a solution which would give an accurate rate is obtainable. Some respondents provided only the age at first marriage/union and then didn't provide the age at first intercourse/union, only giving the response that it happened at the first time they entered an union and this might complicate things I feel. Hopefully a good solution is still possible.

Here is what I have:

. tab magem

     Age at |
      first |
marriage/un |
  ion - man |      Freq.     Percent        Cum.
------------+-----------------------------------
          1 |          1        0.01        0.01
          3 |          2        0.03        0.04
          6 |          3        0.04        0.09
          8 |          2        0.03        0.12
          9 |          5        0.07        0.19
         10 |         19        0.28        0.47
         11 |         13        0.19        0.65
         12 |         40        0.58        1.24
         13 |         69        1.00        2.24
         14 |        132        1.92        4.16
         15 |        203        2.95        7.11
         16 |        329        4.78       11.89
         17 |        533        7.75       19.64
         18 |        604        8.78       28.42
         19 |        641        9.32       37.74
         20 |        669        9.73       47.47
         21 |        574        8.35       55.82
         22 |        629        9.15       64.96
         23 |        478        6.95       71.91
         24 |        442        6.43       78.34
         25 |        363        5.28       83.61
         26 |        278        4.04       87.66
         27 |        203        2.95       90.61
         28 |        184        2.68       93.28
         29 |        132        1.92       95.20
         30 |         94        1.37       96.57
         31 |         52        0.76       97.32
         32 |         57        0.83       98.15
         33 |         42        0.61       98.76
         34 |         24        0.35       99.11
         35 |         15        0.22       99.33
         36 |         11        0.16       99.49
         37 |          9        0.13       99.62
         38 |         11        0.16       99.78
         39 |          7        0.10       99.88
         40 |          2        0.03       99.91
         41 |          3        0.04       99.96
         42 |          2        0.03       99.99
         46 |          1        0.01      100.00
------------+-----------------------------------
      Total |      6,878      100.00


. tab ms10

        Age at first sexual intercourse |      Freq.     Percent        Cum.
----------------------------------------+-----------------------------------
           Never had sexual intercourse |      2,085       20.95       20.95
                                     10 |          5        0.05       21.00
                                     11 |          4        0.04       21.04
                                     12 |         23        0.23       21.27
                                     13 |         56        0.56       21.84
                                     14 |        144        1.45       23.28
                                     15 |        725        7.29       30.57
                                     16 |        852        8.56       39.13
                                     17 |        823        8.27       47.40
                                     18 |      1,135       11.41       58.81
                                     19 |        558        5.61       64.42
                                     20 |        730        7.34       71.75
                                     21 |        249        2.50       74.25
                                     22 |        262        2.63       76.89
                                     23 |        175        1.76       78.65
                                     24 |        111        1.12       79.76
                                     25 |        219        2.20       81.96
                                     26 |         58        0.58       82.54
                                     27 |         56        0.56       83.11
                                     28 |         38        0.38       83.49
                                     29 |         21        0.21       83.70
                                     30 |         21        0.21       83.91
                                     31 |          2        0.02       83.93
                                     32 |          7        0.07       84.00
                                     33 |          3        0.03       84.03
                                     34 |          4        0.04       84.07
                                     35 |          5        0.05       84.12
                                     36 |          1        0.01       84.13
                                     37 |          1        0.01       84.14
                                     38 |          1        0.01       84.15
                                     39 |          1        0.01       84.16
                                     40 |          1        0.01       84.17
First time when started living with wif |      1,567       15.75       99.92
                                Missing |          8        0.08      100.00
----------------------------------------+-----------------------------------
                                  Total |      9,951      100.00


. tab mstatu

Marital/Union status - man |      Freq.     Percent        Cum.
---------------------------+-----------------------------------
Currently married/in union |      6,721       67.54       67.54
 Formerly married/in union |        157        1.58       69.12
    Never married/in union |      3,073       30.88      100.00
---------------------------+-----------------------------------
                     Total |      9,951      100.00

[William Lisowski]

Welcome to Statalist.

Descriptions of data are well-meant but insufficient to help those who want to help you. Even the best descriptions of data are no substitute for an actual example of the data. In order to get a helpful response, you you will increase the likelihood by showing some example data.

Be sure to use the dataex command to do this. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

By default dataex will output the first 100 observations of all your variables, but this can be changed using if and in and the obs() option, and by specifiying a list of variables to include (which should include your individual identifier, and if you have multiple observations per individual, the observation identifier (usually a date or wave number).

The output of dataex will look something like the following.

----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input int(x1 x2 x3) float x4 int x5 byte x6
 4195 24 1   2 10 0
10371 16 3 3.5 17 0
 4647 28 3   2 11 0
...
 5079 24 4 2.5  8 1
 8129 21 4 2.5  8 1
 4296 21 3 2.5 16 1
end
label values x6 yesno
label def yesno 0 "No", modify
label def yesno 1 "Yes", modify
[/CODE]
------------------ copy up to and including the previous line ------------------

In your dataex output you will select the lines between, but not including, "copy starting from the next line" and "copy up to and including the previous line" and then paste that into your reply. The result presented in your post will look something like the following.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(x1 x2 x3) float x4 int x5 byte x6
 4195 24 1   2 10 0
10371 16 3 3.5 17 0
 4647 28 3   2 11 0
...
 5079 24 4 2.5  8 1
 8129 21 4 2.5  8 1
 4296 21 3 2.5 16 1
end
label values x6 yesno
label def yesno 0 "No", modify
label def yesno 1 "Yes", modify

When asking for help with code, always show example data.

[Joseph Stein]

William, I appreciate your quick response.

I have attached the dataset to my post so anyone can seeking to help can easily do so.

[Mike Lacy]

Because of the possibility of "active" contents in an attached file, most experienced people here won't open attachments, even of type.dta. In this regard, check out item 12.5 at https://www.statalist.org/forums/help Per William Lisowski's suggestion, the -dataex- command is the way to go here.

[Joseph Stein]

Because of the possibility of "active" contents in an attached file, most experienced people here won't open attachments, even of type.dta. In this regard, check out item 12.5 at https://www.statalist.org/forums/help Per William Lisowski's suggestion, the -dataex- command is the way to go here.

dataex yields the following:

. dataex
input statement exceeds linesize limit. Try specifying fewer variables
r(1000);

.

If my guess is correct, perhaps only three variables will be of importance to the solution and their descriptions I have shared in my initial post: "ms10" which is age at first intercourse, "magem" which is age at first marriage/union and "mstatu" which is current marital status.

[William Lisowski]

From post #5:

dataex yields the following ...

From post #2:

By default dataex will output the first 100 observations of all your variables, but this can be changed using if and in and the obs() option, and by specifiying a list of variables to include (which should include your individual identifier, and if you have multiple observations per individual, the observation identifier (usually a date or wave number).

For more instructions on using dataex read the output of

help dataex

[Joseph Stein]

Here are the outputs from dataex for the three essential variables, what they imply I have already talked about in my initial post:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ms10
17
25
25
16
25
16
 0
16
23
 0
24
 0
28
 .
20
25
 0
20
18
 0
25
27
23
25
22
19
17
 .
 0
19
28
99
18
 .
19
18
 .
21
20
 .
21
 0
95
15
23
 0
24
 0
 .
 0
17
18
 .
18
14
 0
15
 0
17
 0
21
17
19
20
16
18
25
95
20
 .
 .
20
30
25
23
17
 0
20
 0
 .
17
26
 0
17
22
20
18
 .
15
20
 0
 .
 .
 .
15
15
 0
27
20
23
end
label values ms10 labels70
label def labels70 0 "Never had sexual intercourse", modify
label def labels70 95 "First time when started living with wife/partner", modify
label def labels70 99 "Missing", modify

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte magem
 .
 .
26
26
 .
19
 .
 .
25
 .
26
 .
28
 .
 .
26
 .
17
20
 .
27
 .
 .
29
 .
 .
 .
 .
 .
21
27
 .
 .
 .
 .
13
 .
25
28
 .
21
 .
21
41
29
 .
24
 .
 .
 .
18
15
 .
16
11
 .
 .
 .
18
 .
 .
25
 .
24
 .
20
23
22
36
 .
 .
 .
32
 .
28
21
 .
 .
 .
 .
22
24
 .
11
24
33
19
 .
21
19
 .
 .
 .
 .
 .
17
 .
10
21
25
end

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte mstatu
3
3
1
1
3
1
3
3
1
3
1
3
1
.
3
1
3
1
1
3
1
3
3
1
3
3
3
.
3
1
1
3
3
.
3
1
.
1
1
.
1
3
1
1
1
3
1
3
.
3
1
1
.
1
1
3
3
3
2
3
3
1
3
1
3
1
1
1
1
.
.
3
1
3
1
1
3
3
3
.
1
1
3
1
1
1
1
.
1
1
3
.
.
.
3
1
3
1
1
1
end
label values mstatu labels138
label def labels138 1 "Currently married/in union", modify
label def labels138 2 "Formerly married/in union", modify
label def labels138 3 "Never married/in union", modify

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input float id byte(mstatu ms10 magem)
  1 3 17  .
  2 3 25  .
  3 1 25 26
  4 1 16 26
  5 3 25  .
  6 1 16 19
  7 3  0  .
  8 3 16  .
  9 1 23 25
 10 3  0  .
 11 1 24 26
 12 3  0  .
 13 1 28 28
 14 .  .  .
 15 3 20  .
 16 1 25 26
 17 3  0  .
 18 1 20 17
 19 1 18 20
 20 3  0  .
 21 1 25 27
 22 3 27  .
 23 3 23  .
 24 1 25 29
 25 3 22  .
 26 3 19  .
 27 3 17  .
 28 .  .  .
 29 3  0  .
 30 1 19 21
 31 1 28 27
 32 3 99  .
 33 3 18  .
 34 .  .  .
 35 3 19  .
 36 1 18 13
 37 .  .  .
 38 1 21 25
 39 1 20 28
 40 .  .  .
 41 1 21 21
 42 3  0  .
 43 1 95 21
 44 1 15 41
 45 1 23 29
 46 3  0  .
 47 1 24 24
 48 3  0  .
 49 .  .  .
 50 3  0  .
 51 1 17 18
 52 1 18 15
 53 .  .  .
 54 1 18 16
 55 1 14 11
 56 3  0  .
 57 3 15  .
 58 3  0  .
 59 2 17 18
 60 3  0  .
 61 3 21  .
 62 1 17 25
 63 3 19  .
 64 1 20 24
 65 3 16  .
 66 1 18 20
 67 1 25 23
 68 1 95 22
 69 1 20 36
 70 .  .  .
 71 .  .  .
 72 3 20  .
 73 1 30 32
 74 3 25  .
 75 1 23 28
 76 1 17 21
 77 3  0  .
 78 3 20  .
 79 3  0  .
 80 .  .  .
 81 1 17 22
 82 1 26 24
 83 3  0  .
 84 1 17 11
 85 1 22 24
 86 1 20 33
 87 1 18 19
 88 .  .  .
 89 1 15 21
 90 1 20 19
 91 3  0  .
 92 .  .  .
 93 .  .  .
 94 .  .  .
 95 3 15  .
 96 1 15 17
 97 3  0  .
 98 1 27 10
 99 1 20 21
100 1 23 25
end
label values mstatu labels138
label def labels138 1 "Currently married/in union", modify
label def labels138 2 "Formerly married/in union", modify
label def labels138 3 "Never married/in union", modify
label values ms10 labels70
label def labels70 0 "Never had sexual intercourse", modify
label def labels70 95 "First time when started living with wife/partner", modify
label def labels70 99 "Missing", modify

g premarital= cond((ms10<magem &inrange(ms10, 1, 94) &mstatu<3)| (mstatu==3 &inrange(ms10, 1, 94)), 1, cond(ms10==99|missing(ms10), ., 0))
g premarital_m = premarital if mstatu<3

Res.:

. l, sep(0)

     +-------------------------------------------------------------------------------------------------------------------+
     |  id                       mstatu                                               ms10   magem   premar~l   premar~m |
     |-------------------------------------------------------------------------------------------------------------------|
  1. |   1       Never married/in union                                                 17       .          1          . |
  2. |   2       Never married/in union                                                 25       .          1          . |
  3. |   3   Currently married/in union                                                 25      26          1          1 |
  4. |   4   Currently married/in union                                                 16      26          1          1 |
  5. |   5       Never married/in union                                                 25       .          1          . |
  6. |   6   Currently married/in union                                                 16      19          1          1 |
  7. |   7       Never married/in union                       Never had sexual intercourse       .          0          . |
  8. |   8       Never married/in union                                                 16       .          1          . |
  9. |   9   Currently married/in union                                                 23      25          1          1 |
 10. |  10       Never married/in union                       Never had sexual intercourse       .          0          . |
 11. |  11   Currently married/in union                                                 24      26          1          1 |
 12. |  12       Never married/in union                       Never had sexual intercourse       .          0          . |
 13. |  13   Currently married/in union                                                 28      28          0          0 |
 14. |  14                            .                                                  .       .          .          . |
 15. |  15       Never married/in union                                                 20       .          1          . |
 16. |  16   Currently married/in union                                                 25      26          1          1 |
 17. |  17       Never married/in union                       Never had sexual intercourse       .          0          . |
 18. |  18   Currently married/in union                                                 20      17          0          0 |
 19. |  19   Currently married/in union                                                 18      20          1          1 |
 20. |  20       Never married/in union                       Never had sexual intercourse       .          0          . |
 21. |  21   Currently married/in union                                                 25      27          1          1 |
 22. |  22       Never married/in union                                                 27       .          1          . |
 23. |  23       Never married/in union                                                 23       .          1          . |
 24. |  24   Currently married/in union                                                 25      29          1          1 |
 25. |  25       Never married/in union                                                 22       .          1          . |
 26. |  26       Never married/in union                                                 19       .          1          . |
 27. |  27       Never married/in union                                                 17       .          1          . |
 28. |  28                            .                                                  .       .          .          . |
 29. |  29       Never married/in union                       Never had sexual intercourse       .          0          . |
 30. |  30   Currently married/in union                                                 19      21          1          1 |
 31. |  31   Currently married/in union                                                 28      27          0          0 |
 32. |  32       Never married/in union                                            Missing       .          .          . |
 33. |  33       Never married/in union                                                 18       .          1          . |
 34. |  34                            .                                                  .       .          .          . |
 35. |  35       Never married/in union                                                 19       .          1          . |
 36. |  36   Currently married/in union                                                 18      13          0          0 |
 37. |  37                            .                                                  .       .          .          . |
 38. |  38   Currently married/in union                                                 21      25          1          1 |
 39. |  39   Currently married/in union                                                 20      28          1          1 |
 40. |  40                            .                                                  .       .          .          . |
 41. |  41   Currently married/in union                                                 21      21          0          0 |
 42. |  42       Never married/in union                       Never had sexual intercourse       .          0          . |
 43. |  43   Currently married/in union   First time when started living with wife/partner      21          0          0 |
 44. |  44   Currently married/in union                                                 15      41          1          1 |
 45. |  45   Currently married/in union                                                 23      29          1          1 |
 46. |  46       Never married/in union                       Never had sexual intercourse       .          0          . |
 47. |  47   Currently married/in union                                                 24      24          0          0 |
 48. |  48       Never married/in union                       Never had sexual intercourse       .          0          . |
 49. |  49                            .                                                  .       .          .          . |
 50. |  50       Never married/in union                       Never had sexual intercourse       .          0          . |
 51. |  51   Currently married/in union                                                 17      18          1          1 |
 52. |  52   Currently married/in union                                                 18      15          0          0 |
 53. |  53                            .                                                  .       .          .          . |
 54. |  54   Currently married/in union                                                 18      16          0          0 |
 55. |  55   Currently married/in union                                                 14      11          0          0 |
 56. |  56       Never married/in union                       Never had sexual intercourse       .          0          . |
 57. |  57       Never married/in union                                                 15       .          1          . |
 58. |  58       Never married/in union                       Never had sexual intercourse       .          0          . |
 59. |  59    Formerly married/in union                                                 17      18          1          1 |
 60. |  60       Never married/in union                       Never had sexual intercourse       .          0          . |
 61. |  61       Never married/in union                                                 21       .          1          . |
 62. |  62   Currently married/in union                                                 17      25          1          1 |
 63. |  63       Never married/in union                                                 19       .          1          . |
 64. |  64   Currently married/in union                                                 20      24          1          1 |
 65. |  65       Never married/in union                                                 16       .          1          . |
 66. |  66   Currently married/in union                                                 18      20          1          1 |
 67. |  67   Currently married/in union                                                 25      23          0          0 |
 68. |  68   Currently married/in union   First time when started living with wife/partner      22          0          0 |
 69. |  69   Currently married/in union                                                 20      36          1          1 |
 70. |  70                            .                                                  .       .          .          . |
 71. |  71                            .                                                  .       .          .          . |
 72. |  72       Never married/in union                                                 20       .          1          . |
 73. |  73   Currently married/in union                                                 30      32          1          1 |
 74. |  74       Never married/in union                                                 25       .          1          . |
 75. |  75   Currently married/in union                                                 23      28          1          1 |
 76. |  76   Currently married/in union                                                 17      21          1          1 |
 77. |  77       Never married/in union                       Never had sexual intercourse       .          0          . |
 78. |  78       Never married/in union                                                 20       .          1          . |
 79. |  79       Never married/in union                       Never had sexual intercourse       .          0          . |
 80. |  80                            .                                                  .       .          .          . |
 81. |  81   Currently married/in union                                                 17      22          1          1 |
 82. |  82   Currently married/in union                                                 26      24          0          0 |
 83. |  83       Never married/in union                       Never had sexual intercourse       .          0          . |
 84. |  84   Currently married/in union                                                 17      11          0          0 |
 85. |  85   Currently married/in union                                                 22      24          1          1 |
 86. |  86   Currently married/in union                                                 20      33          1          1 |
 87. |  87   Currently married/in union                                                 18      19          1          1 |
 88. |  88                            .                                                  .       .          .          . |
 89. |  89   Currently married/in union                                                 15      21          1          1 |
 90. |  90   Currently married/in union                                                 20      19          0          0 |
 91. |  91       Never married/in union                       Never had sexual intercourse       .          0          . |
 92. |  92                            .                                                  .       .          .          . |
 93. |  93                            .                                                  .       .          .          . |
 94. |  94                            .                                                  .       .          .          . |
 95. |  95       Never married/in union                                                 15       .          1          . |
 96. |  96   Currently married/in union                                                 15      17          1          1 |
 97. |  97       Never married/in union                       Never had sexual intercourse       .          0          . |
 98. |  98   Currently married/in union                                                 27      10          0          0 |
 99. |  99   Currently married/in union                                                 20      21          1          1 |
100. | 100   Currently married/in union                                                 23      25          1          1 |
     +-------------------------------------------------------------------------------------------------------------------+

.