Create a variable with an increasing value for different groups

[Daniel Padon]

Dear all,

I am using Stata version 14.2.

I have a data set with groups of products and dates of weeks for each group, and a dummy variable which determines whether an observation is after a certain date, which is different for each group.
I've created a variable which contains the date of the observation if it is after the deciding date, and 0 otherwise.

I want to create a variable which increases in value for each week which is after the deciding date of each group.

I have tried different approaches unsuccessfully, such as loops, counters, and egen (which doesn't work as I need the count to begin anew for each group)

this is an example of the data I have:

group week deciding_date after
1 1.1.19 6.1.19 0
1 8.1.19 6.1.19 8.1.19
1 15.1.19 6.1.19 15.1.19
2 1.11.18 3.11.18 0
2 8.11.18 3.11.18 8.11.18
2 15.11.18 3.11.18 15.11.18

I want to add a counter variable which would do this:
group week deciding_date after counter
1 1.1.19 6.1.19 0 0
1 8.1.19 6.1.19 8.1.19 1
1 15.1.19 6.1.19 15.1.19 2
2 1.11.18 3.11.18 0 0
2 8.11.18 3.11.18 8.11.18 1
2 15.11.18 3.11.18 15.11.18 2



I'd appreciate any help you could provide

[William Lisowski]

Your example data leaves one crucial question unanswered. Are the dates you show string variables, or are they, as they should be, Stata Internal Format (SIF) daily dates displayed with a customized %td format?

In case this question leaves you confused, then welcome to the world of Stata dates and times. Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

If they are indeed string variables, your first step will be to use them to create SIF daily date variables following the guidance in the cited documentation.

When you have SIF dates, could you then please provide your example data using the dataex command to spare those of us who want to help the effort of turning your example into usable data.

Added in edit: I note that the dates you show are 7 days apart. Is that always the case? Are there possibly skipped dates, so that there could be a 14-day gap?
Does something different happen if the 7th day would be a holiday? Basically, the easy thing to do is to count up by one for each succeeding observation, but I'm not certain that's really what you want or need.

[Daniel Padon]

Thank you for the quick response,

They are indeed SIF dates,

clear
input float group int week float(retailer entry_date treatment brand_imp l_p after AT) byte _est_est2 float(a_week counter num)
1 21107 2 21107 0      14.9  2.701361 1 0 1 21107  73  1
1 21107 1 21107 0     18.69  2.927989 1 0 1 21107   .  1
1 21107 3 21107 1      14.9  2.701361 1 1 1 21107   .  1
1 21114 3 21107 1      14.9  2.701361 1 1 1 21114  76  2
1 21114 1 21107 0     18.68 2.9274535 1 0 1 21114   .  2
1 21114 2 21107 0      14.9  2.701361 1 0 1 21114   .  2
1 21121 2 21107 0      14.9  2.701361 1 0 1 21121  79  3
1 21121 3 21107 1      14.9  2.701361 1 1 1 21121   .  3
1 21121 1 21107 0     18.68 2.9274535 1 0 1 21121   .  3
1 21128 1 21107 0     18.72 2.9295924 1 0 1 21128  82  4
1 21128 2 21107 0 14.942373  2.704201 1 0 1 21128   .  4
1 21128 3 21107 1      14.9  2.701361 1 1 1 21128   .  4
1 21135 3 21107 1      14.9  2.701361 1 1 1 21135  85  5
1 21135 2 21107 0 14.934483  2.703673 1 0 1 21135   .  5
1 21135 1 21107 0     18.75  2.931194 1 0 1 21135   .  5
1 21142 1 21107 0     18.73 2.9301264 1 0 1 21142  88  6
1 21142 3 21107 1      14.9  2.701361 1 1 1 21142   .  6
1 21142 2 21107 0 15.692413 2.7531774 1 0 1 21142   .  6
1 21149 1 21107 0     18.73 2.9301264 1 0 1 21149  91  7
1 21149 2 21107 0 16.239483 2.7874455 1 0 1 21149   .  7
1 21149 3 21107 1      14.9  2.701361 1 1 1 21149   .  7
1 21156 2 21107 0 16.224068  2.786496 1 0 1 21156  94  8
1 21156 3 21107 1      14.9  2.701361 1 1 1 21156   .  8
1 21156 1 21107 0      18.7 2.9285235 1 0 1 21156   .  8
1 21163 1 21107 0      18.7 2.9285235 1 0 1 21163  97  9
1 21163 3 21107 1      14.9  2.701361 1 1 1 21163   .  9
1 21163 2 21107 0  16.21678 2.7860465 1 0 1 21163   .  9
1 21170 3 21107 1      14.9  2.701361 1 1 1 21170 100 10
1 21170 1 21107 0     18.74   2.93066 1 0 1 21170   . 10
1 21170 2 21107 0 16.172203  2.783294 1 0 1 21170   . 10
1 21177 1 21107 0     18.72 2.9295924 1 0 1 21177 103 11
1 21177 3 21107 1      14.9  2.701361 1 1 1 21177   . 11
1 21177 2 21107 0 16.172203  2.783294 1 0 1 21177   . 11
1 21184 2 21107 0 16.071228  2.777031 1 0 1 21184 106 12
1 21184 1 21107 0     18.75  2.931194 1 0 1 21184   . 12
1 21184 3 21107 1      14.9  2.701361 1 1 1 21184   . 12
1 21191 3 21107 1      14.9  2.701361 1 1 1 21191 109 13
1 21191 2 21107 0 16.086071 2.7779536 1 0 1 21191   . 13
1 21191 1 21107 0     18.69  2.927989 1 0 1 21191   . 13
1 21198 1 21107 0      18.7 2.9285235 1 0 1 21198 112 14
1 21198 2 21107 0 16.167967  2.783032 1 0 1 21198   . 14
1 21198 3 21107 1      14.9  2.701361 1 1 1 21198   . 14
1 21205 1 21107 0      18.7 2.9285235 1 0 1 21205 115 15
1 21205 3 21107 1      14.9  2.701361 1 1 1 21205   . 15
1 21205 2 21107 0 16.172203  2.783294 1 0 1 21205   . 15
1 21212 2 21107 0 16.172203  2.783294 1 0 1 21212 118 16
1 21212 1 21107 0     18.68 2.9274535 1 0 1 21212   . 16
1 21212 3 21107 1      14.9  2.701361 1 1 1 21212   . 16
1 21219 2 21107 0 16.142542  2.781458 1 0 1 21219 121 17
1 21219 1 21107 0     18.74   2.93066 1 0 1 21219   . 17
1 21219 3 21107 1      14.9  2.701361 1 1 1 21219   . 17
1 21226 1 21107 0     18.74   2.93066 1 0 1 21226 124 18
1 21226 2 21107 0 16.090689  2.778241 1 0 1 21226   . 18
1 21226 3 21107 1      14.9  2.701361 1 1 1 21226   . 18
1 21233 2 21107 0 16.090689  2.778241 1 0 1 21233 127 19
1 21233 3 21107 1      14.9  2.701361 1 1 1 21233   . 19
1 21233 1 21107 0     18.76  2.931727 1 0 1 21233   . 19
1 21240 3 21107 1      14.9  2.701361 1 1 1 21240 130 20
1 21240 1 21107 0     18.75  2.931194 1 0 1 21240   . 20
1 21240 2 21107 0 16.114828   2.77974 1 0 1 21240   . 20
1 21247 1 21107 0     13.95 2.6354795 1 0 1 21247 133 21
1 21247 3 21107 1     12.98 2.5634096 1 1 1 21247   . 21
1 21247 2 21107 0 12.735263  2.544375 1 0 1 21247   . 21
1 21254 1 21107 0      13.9  2.631889 1 0 1 21254 136 22
1 21254 2 21107 0 12.548947  2.529637 1 0 1 21254   . 22
1 21254 3 21107 1      11.9 2.4765384 1 1 1 21254   . 22
1 21261 1 21107 0      13.9  2.631889 1 0 1 21261 139 23
1 21261 2 21107 0 12.565593  2.530962 1 0 1 21261   . 23
1 21261 3 21107 1      11.9 2.4765384 1 1 1 21261   . 23
1 21268 1 21107 0      13.9  2.631889 1 0 1 21268 142 24
1 21268 3 21107 1      11.9 2.4765384 1 1 1 21268   . 24
1 21268 2 21107 0  12.56678  2.531057 1 0 1 21268   . 24
1 21275 3 21107 1     11.91 2.4773784 1 1 1 21275 145 25
1 21275 2 21107 0 16.172203  2.783294 1 0 1 21275   . 25
1 21275 1 21107 0      13.9  2.631889 1 0 1 21275   . 25
1 21282 1 21107 0     18.69  2.927989 1 0 1 21282 148 26
1 21282 2 21107 0 16.172203  2.783294 1 0 1 21282   . 26
1 21282 3 21107 1     14.04   2.64191 1 1 1 21282   . 26
1 21289 3 21107 1      14.9  2.701361 1 1 1 21289 151 27
1 21289 1 21107 0     18.74   2.93066 1 0 1 21289   . 27
1 21289 2 21107 0 16.172203  2.783294 1 0 1 21289   . 27
1 21296 3 21107 1      14.9  2.701361 1 1 1 21296 154 28
1 21296 1 21107 0     18.68 2.9274535 1 0 1 21296   . 28
1 21296 2 21107 0 16.192587 2.7845535 1 0 1 21296   . 28
1 21303 3 21107 1      14.9  2.701361 1 1 1 21303 157 29
1 21303 2 21107 0 16.172203  2.783294 1 0 1 21303   . 29
1 21303 1 21107 0     18.62  2.924236 1 0 1 21303   . 29
1 21310 1 21107 0     18.67  2.926918 1 0 1 21310 160 30
1 21310 3 21107 1      14.9  2.701361 1 1 1 21310   . 30
1 21310 2 21107 0 16.107796  2.779303 1 0 1 21310   . 30
1 21317 2 21107 0 16.065763 2.7766905 1 0 1 21317 163 31
1 21317 1 21107 0     18.78 2.9327924 1 0 1 21317   . 31
1 21317 3 21107 1      14.9  2.701361 1 1 1 21317   . 31
1 21324 1 21107 0     18.74   2.93066 1 0 1 21324 166 32
1 21324 2 21107 0 16.172203  2.783294 1 0 1 21324   . 32
1 21324 3 21107 1      14.9  2.701361 1 1 1 21324   . 32
1 21331 3 21107 1      14.9  2.701361 1 1 1 21331 169 33
1 21331 1 21107 0     18.69  2.927989 1 0 1 21331   . 33
1 21331 2 21107 0 16.172203  2.783294 1 0 1 21331   . 33
1 21338 1 21107 0     18.69  2.927989 1 0 1 21338 172 34
end
format %td week
format %td entry_date
format %td a_week

[William Lisowski]

For the data you show, it appears that retailers 1 and 2 were never treated, and retailer 3 was treated beginning in the first week. The following code seems to generate the counter you need, but I suspect I am not fully understanding how the untreated retailers are to be handled.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float group int week float(retailer entry_date treatment)
1 21107 2 21107 0
1 21107 1 21107 0
1 21107 3 21107 1
1 21114 3 21107 1
1 21114 1 21107 0
1 21114 2 21107 0
1 21121 2 21107 0
1 21121 3 21107 1
1 21121 1 21107 0
1 21128 1 21107 0
1 21128 2 21107 0
1 21128 3 21107 1
1 21135 3 21107 1
1 21135 2 21107 0
1 21135 1 21107 0

end
format %td week
format %td entry_date

bysort group retailer (week): assert week == week[_n-1]+7 if _n>1
bysort group retailer (week): assert treatment == 1 if treatment[_n-1]==1 

bysort group retailer (week): generate counter = sum(week>entry_date & treatment==1)
list, noobs sepby(retailer) abbreviate(12)

. list, noobs sepby(retailer) abbreviate(12)

  +-----------------------------------------------------------------+
  | group        week   retailer   entry_date   treatment   counter |
  |-----------------------------------------------------------------|
  |     1   15oct2017          1    15oct2017           0         0 |
  |     1   22oct2017          1    15oct2017           0         0 |
  |     1   29oct2017          1    15oct2017           0         0 |
  |     1   05nov2017          1    15oct2017           0         0 |
  |     1   12nov2017          1    15oct2017           0         0 |
  |-----------------------------------------------------------------|
  |     1   15oct2017          2    15oct2017           0         0 |
  |     1   22oct2017          2    15oct2017           0         0 |
  |     1   29oct2017          2    15oct2017           0         0 |
  |     1   05nov2017          2    15oct2017           0         0 |
  |     1   12nov2017          2    15oct2017           0         0 |
  |-----------------------------------------------------------------|
  |     1   15oct2017          3    15oct2017           1         0 |
  |     1   22oct2017          3    15oct2017           1         1 |
  |     1   29oct2017          3    15oct2017           1         2 |
  |     1   05nov2017          3    15oct2017           1         3 |
  |     1   12nov2017          3    15oct2017           1         4 |
  +-----------------------------------------------------------------+

[Daniel Padon]

Dear William,

Thank you very much for your help, it works perfectly with minor tweaks to fit my dataset.

Daniel

[Dung Le]

Dear William Lisowski,

Given my question is quite different as compared the Padon but it is also about how to generate a new variable. Unlike Padon, my case is to generate a new variable as a combination of means of 20 other variables, say x1-x20. In fact, I want co create a continuous variable, in which its first value is the mean of x1, second value is the mean of x2 and so on. I have tried some commands with egen but it did not work. Do you have any idea how to solve my issue? Following is an example of my data.

	
	
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(pfitfm0 pfitfm1 pfitfm2 pfitfm3 pfitfm4 pfitfm5 pfitfm6 pfitfm7 pfitfm8 pfitfm9 pfitfm10 pfitfm11 pfitfm12 pfitfm13 pfitfm14 pfitfm15 pfitfm16 pfitfm17 pfitfm18 pfitfm19 pfitfm20)
.10463551 .18859567 .17903563 .12113285   .0700939 .04168195  .02937712  .02450828 .022226144 .020640487  .01918993 .017757878  .01634406  .014968617  .013649706  .012400028  .011227395  .010135796  .009126367  .008198163   .007348758
.10674594 .19206114  .1836562  .1260093 .074436665   .045195 .032063246  .02645224 .023514783 .021353874 .019405574 .017551947 .015789872  .014134712  .012598407  .011186708  .009900246  .008735988  .007688461  .006750648   .005914662
.14631665 .22727574 .19234304 .12168118  .07094832 .04529748 .033570144 .027361417  .02305074 .019496227 .016411878 .013730334 .011421092  .009452714  .007789677   .00639498  .005232575  .004268885  .003473558  .002819768   .002284182
.12738018 .21875027  .2140069 .15357025  .09400337 .05592218  .03560556 .024859926  .01844593  .01402756 .010717482 .008165532 .006191996  .004673286  .003512035  .002629414 .0019620825 .0014598167 .0010832798 .0008019766  .0005924632
.11596175  .2068972  .2068765  .1508547  .09381617 .05707933  .03762115 .027465757 .021393294 .017085772 .013704724 .010959052 .008721266  .006907397   .00544739 .0042798175   .00335135  .002616597 .0020375834 .0015829696    .00122718
 .1565635 .23407882  .1912937 .11810946  .06851536 .04425048 .033138297  .02702083 .022620173  .01896021 .015804155  .01308939  .01077827  .008830732  .007203724  .005854291  .004741856  .003829523  .003084624  .002478782   .001987711
.07259528 .15575953 .18032323 .14732549   .0981221 .06059001  .03947315  .02932053  .02444502   .0216184  .01948727 .017601732 .015846122  .014203521   .01267804  .011274068  .009992384 .0088304095  .007783122  .006843928   .006005317
 .1240795 .21265008   .200156 .13805638  .08337195 .05155048  .03587453  .02783793   .0227942  .01894931 .015744818 .013018537 .010706368  .008761551  .007139072  .005795097  .004688532  .003782122  .003042954  .002442497   .001956371
.14141308 .22993734  .2092177 .14098105   .0837142 .05071128 .034034688   .0250714  .01933596 .015103242  .01178452 .009149483 .007065365  .005429156 .0041538677 .0031661496   .00240529 .0018219053 .0013764028  .001037394  .0007802267
.14719559  .2206785 .17677534  .1055158   .0592681 .03836569  .03011743  .02630435 .023697764  .02138194  .01918138 .017095814 .015148457   .01335554  .011723764  .010252484  .008936097  .007765852  .006731186  .005820666   .005022645
.11469015  .2024765  .1938808 .13490961  .08159777 .05050125 .035567373 .028321164 .023979867  .02066857  .01781734   .0152868  .01304536  .011077855  .009366496  .007889638    .0066236  .005544386   .00462886  .003855436  .0032044344
  .180449 .25629285 .20402105 .12415428 .070719235 .04364121 .030239575  .02245472 .017051762  .01296056 .009797483 .007359704  .00549667 .0040846993  .003022276 .0022277427  .001636638 .0011988443 .0008758586 .0006383868 .00046431494
.12991333 .22118975  .2147115 .15304828  .09321553 .05528016 .035115633 .024442395 .018057588 .013661424  .01038003 .007863636 .005929031 .0044492274  .003324518 .0024747765 .0018361222  .001358282  .001002166 .0007376795  .0005418445
  .106886 .19481774   .192036 .13667215  .08352373 .05144685  .03585189 .028450014 .024219487  .02108762 .018396199 .015981117 .013810705  .011876825  .010169752  .008675197   .00737574  .006252508  .005286452   .00445916    .00375336
.16626485 .24805637  .2072587 .13064462  .07522021 .04588848 .031349137  .02317215 .017656038  .01351455  .01030146  .00780571 .005881134  .004409004 .0032910574 .0024473025 .0018138306 .0013403813 .0009879181 .0007264278  .0005330186
.12335978  .2097462 .19328834  .1303235  .07763296 .04839556 .034726426 .028014855  .02381695  .02050831 .017628195 .015071874   .0128151  .010842258  .009133467  .007664948  .006411205  .005346784 .0044474048 .0036906213  .0030561246
.19366503 .26260272 .19989116 .11800907 .066885844 .04190012 .029463783 .021992877 .016677145 .012622562 .009493326 .007093105 .005268925 .0038942415 .0028657375 .0021009054 .0015350878 .0011183615   .00081263 .0005890901  .0004261373
.14430827 .22087073 .18140927 .11092403  .06321512 .04074424 .031439763 .026959617 .023892656  .02123133   .0187655 .016480446 .014389905   .01250155  .010813907  .009318758 .0080036875  .006854007  .005854104  .004988327  .0042415825
 .1649759 .24178454  .1953727  .1200617  .06955566 .04459278  .03275539  .02599388 .021120323 .017170904 .013880768  .01114927 .008903495  .007074444  .005596768  .004411019  .003464952    .0027138 .0021199216 .0016521156  .0012848126
.08451232 .16858865 .17664875 .12999913  .07887524 .04612721 .030385144 .023945214  .02127933  .01981438  .01863675 .017494963  .01634645   .01520065  .014074638   .01298295  .011936226  .010941663  .010003707  .009124668   .008305235
end

Do you have any ideas of how to generate such a variable?

Thank you.

DL

[Nick Cox]

Dung Le Please start a new thread with that question.