how to construct the following model

[ALKEBSEE RADWAN]

I was working on the following model
Cost stickiness it = π‘™π‘œπ‘”(Ξ”Cost / Ξ”π‘†π‘Žπ‘™π‘’)𝑖,𝜏1β€“π‘™π‘œπ‘”(Ξ”Cost / Ξ”π‘†π‘Žπ‘™π‘’)𝑖,𝜏2

𝜏1,𝜏2 ∈ {π‘‘βˆ’1,…,π‘‘βˆ’15} where 𝜏1 is the most recent of the last fivteen years with an increase in sales and 𝜏2 is the most recent of the last fivteen years with a decrease in sales
I have a problem how to find the second part of the model above π‘™π‘œπ‘”(Ξ”Cost / Ξ”π‘†π‘Žπ‘™π‘’)𝑖,𝜏2.
I calculate the delta of costs and sales
I created a variable for assigning the most recent years with increase and decrease in sales.

then I stoped i couldnot proceed

Here is my data example

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code int year float(d_sales d_cost decrease increase)
1 2000             .             . 0 1
1 2001     687155072     776453952 0 1
1 2002     958657472    1084733056 0 1
1 2003      51737416     361816768 0 1
1 2004    1351974528    1292766848 0 1
1 2005      76952512      66355264 0 1
1 2006    2231222528     388660736 0 1
1 2007    3502545152    1855511296 0 1
1 2008    3271688960    6624133120 0 1
1 2009     730643008   -4754379776 0 1
1 2010    3267969024    1217410048 0 1
1 2011   11525493760    6337636864 0 1
1 2012    9824071680    5696627200 0 1
1 2013   12382602240   10027013120 0 1
1 2014    1.9343e+10    1.4927e+10 0 1
1 2015    2.2068e+10    2.0107e+10 0 1
1 2016    1.1711e+10    1.0668e+10 0 1
1 2017      4.53e+08    -2.373e+09 0 1
1 2018     1.343e+09     8.848e+09 0 1
1 2019    2.0602e+10   -4.2269e+10 0 1
1 2020    1.6428e+10     4.073e+09 0 1
1 2021    1.0261e+10     3.366e+09 0 1
1 2022     6.966e+09     1.533e+09 0 1
2 2000 -156685336576  -47677440000 1 0
2 2001     671396096     720872576 0 1
2 2002     119294848     -94913640 0 1
2 2003    1805700864    1498755584 0 1
2 2004    1287165824     860388864 0 1
2 2005    2891625472    2106961408 0 1
2 2006    7289358848    5864671232 0 1
2 2007   17678401536   13666197504 0 1
2 2008    5465167872    6796171776 0 1
2 2009    7889233920    6266779136 0 1
2 2010    1832838272   -1540599680 0 1
2 2011   2.10689e+10   17134536704 0 1
2 2012   31333251072   26306793472 0 1
2 2013    3.2303e+10   29138827264 0 1
2 2014    1.0969e+10    1.3417e+10 0 1
2 2015    4.9161e+10    4.0409e+10 0 1
2 2016    4.4928e+10    4.0479e+10 0 1
2 2017      2.42e+09    -8.143e+09 0 1
2 2018    5.4782e+10    3.8728e+10 0 1
2 2019    7.0215e+10    5.7269e+10 0 1
2 2020    5.1218e+10    5.6192e+10 0 1
2 2021    3.3686e+10    5.2597e+10 0 1
2 2022     5.104e+10    5.1901e+10 0 1
3 2000 -5.033733e+11 -4.539966e+11 1 0
3 2001    -428116832    -897682112 1 0
4 2000      94399936      12863759 0 1
4 2001     -69022168     -72329312 1 0
4 2002      59241148      55107264 0 1
4 2003      -1690659      -88354.2 1 0
4 2004     -12787202     -273670.1 1 0
4 2005     -45371028     -25512840 1 0
4 2006     -15737727     -46003852 1 0
4 2007      -5189456       9337137 1 0
4 2008       2865283     3687074.5 0 1
4 2009      16765775    -2104850.8 0 1
4 2010      71250896      55114084 0 1
4 2011     -56827776     -44604908 1 0
4 2012      22859584      23883712 0 1
4 2013     -24578734     -23296362 1 0
4 2014       7824253      14055684 0 1
4 2015      39845604      34588548 0 1
4 2016     167215600     151425232 0 1
4 2017    -149064192    -118633056 1 0
4 2018     228262960     258246800 0 1
4 2019    -258622112    -268939264 1 0
4 2020     172284304      29413868 0 1
4 2021       8413173      52393352 0 1
4 2022    -122480888      -6866073 1 0
5 2000     -20515790     -38597432 1 0
5 2001     -78819272     -25491252 1 0
5 2002     -12184994      28049694 1 0
5 2003      36632112     -20726088 0 1
5 2004     -42197156     -22343782 1 0
5 2005      -3982463     134131488 1 0
5 2006     282666528      58637672 0 1
5 2007     -29030654     -80071816 1 0
5 2008    -190311344     -92928912 1 0
5 2009     -65527788     -29368732 1 0
5 2010      94041760      27918140 0 1
5 2011     -73699392     -15285444 1 0
5 2012      35886388       6142360 0 1
5 2013     -47359524     -46379580 1 0
5 2014      715291.6      11106601 0 1
5 2015      31348976      40612180 0 1
5 2016     397737472     337737504 0 1
5 2017      49058796      67974864 0 1
5 2018     -44741256     -31322890 1 0
5 2019      66428104      61558088 0 1
5 2020    -153688016    -142375312 1 0
5 2021     -15384852     -33489624 1 0
5 2022     -99792552     -80424600 1 0
6 2000    1599978624    1407574400 0 1
6 2001     140467216     160067136 0 1
6 2002      61859420      96501592 0 1
6 2003     -98956128     252396256 1 0
6 2004    -110116608    -375609056 1 0
6 2005    -443111424    -503624416 1 0
end

I hope you help me out with this situation

[Clyde Schechter]

You say you worked out code for identifying the most recent years of increase and discrease in sales, but your data example does not show the results of that. Be that as it may, I think a different approach would be more helpful.

preserve
keep code year d_cost d_sales
tempfile copy
save `copy'

restore
rangejoin year -15 -1 using `copy', by(code)
isid code year year_U, sort missok
by code year (year_U): egen n1 = max(cond(d_sales_U > 0, _n, .))
by code year (year_U): egen n2 = max(cond(d_sales_U < 0, _n, .))
by code year (year_U):gen stickiness = ///
    log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])
by code year: keep if _n == 1
drop *_U

Note that the variables decrease and increase you created are not needed here, as it is just as easy to condition on d_sales < 0 or d_sales > 0.

Note also that we do not calculate the years (t1 and t2) in which the most recent increases and decreases occur: rather we identify the observation numbers that correspond to them. That then makes it easy to pick up the costs and sales in those years and use them to calculate the stickiness.

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also from SSC.

[ALKEBSEE RADWAN]

You say you worked out code for identifying the most recent years of increase and discrease in sales, but your data example does not show the results of that. Be that as it may, I think a different approach would be more helpful.

preserve
keep code year d_cost d_sales
tempfile copy
save `copy'

restore
rangejoin year -15 -1 using `copy', by(code)
isid code year year_U, sort missok
by code year (year_U): egen n1 = max(cond(d_sales_U > 0, _n, .))
by code year (year_U): egen n2 = max(cond(d_sales_U < 0, _n, .))
by code year (year_U):gen stickiness = ///
log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])
by code year: keep if _n == 1
drop *_U

Note that the variables decrease and increase you created are not needed here, as it is just as easy to condition on d_sales < 0 or d_sales > 0.

Note also that we do not calculate the years (t1 and t2) in which the most recent increases and decreases occur: rather we identify the observation numbers that correspond to them. That then makes it easy to pick up the costs and sales in those years and use them to calculate the stickiness.

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also from SSC.

Fisrt of all thank you very very much for these codes.
secondly I have run these codes and i got missing values for stickiness variable
created by

  by code year (year_U):gen stickiness = log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])

can you tell me what is the issue here please

also this is a sample of the created variables by using such codes

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input int year_U float(d_sales_U d_cost_U n1 n2)
   .           .           .   1 .
2000           .           .   2 .
2000           .           .   4 .
2001   687155072   776453952   4 .
2000           .           .   7 .
2001   687155072   776453952   7 .
2002   958657472  1084733056   7 .
2000           .           .  11 .
2001   687155072   776453952  11 .
2002   958657472  1084733056  11 .
2003    51737416   361816768  11 .
2000           .           .  16 .
2001   687155072   776453952  16 .
2002   958657472  1084733056  16 .
2003    51737416   361816768  16 .
2004  1351974528  1292766848  16 .
2000           .           .  22 .
2001   687155072   776453952  22 .
2002   958657472  1084733056  22 .
2003    51737416   361816768  22 .
2004  1351974528  1292766848  22 .
2005    76952512    66355264  22 .
2000           .           .  29 .
2001   687155072   776453952  29 .
2002   958657472  1084733056  29 .
2003    51737416   361816768  29 .
2004  1351974528  1292766848  29 .
2005    76952512    66355264  29 .
2006  2231222528   388660736  29 .
2000           .           .  37 .
2001   687155072   776453952  37 .
2002   958657472  1084733056  37 .
2003    51737416   361816768  37 .
2004  1351974528  1292766848  37 .
2005    76952512    66355264  37 .
2006  2231222528   388660736  37 .
2007  3502545152  1855511296  37 .
2000           .           .  46 .
2001   687155072   776453952  46 .
2002   958657472  1084733056  46 .
2003    51737416   361816768  46 .
2004  1351974528  1292766848  46 .
2005    76952512    66355264  46 .
2006  2231222528   388660736  46 .
2007  3502545152  1855511296  46 .
2008  3271688960  6624133120  46 .
2000           .           .  56 .
2001   687155072   776453952  56 .
2002   958657472  1084733056  56 .
2003    51737416   361816768  56 .
2004  1351974528  1292766848  56 .
2005    76952512    66355264  56 .
2006  2231222528   388660736  56 .
2007  3502545152  1855511296  56 .
2008  3271688960  6624133120  56 .
2009   730643008 -4754379776  56 .
2000           .           .  67 .
2001   687155072   776453952  67 .
2002   958657472  1084733056  67 .
2003    51737416   361816768  67 .
2004  1351974528  1292766848  67 .
2005    76952512    66355264  67 .
2006  2231222528   388660736  67 .
2007  3502545152  1855511296  67 .
2008  3271688960  6624133120  67 .
2009   730643008 -4754379776  67 .
2010  3267969024  1217410048  67 .
2000           .           .  79 .
2001   687155072   776453952  79 .
2002   958657472  1084733056  79 .
2003    51737416   361816768  79 .
2004  1351974528  1292766848  79 .
2005    76952512    66355264  79 .
2006  2231222528   388660736  79 .
2007  3502545152  1855511296  79 .
2008  3271688960  6624133120  79 .
2009   730643008 -4754379776  79 .
2010  3267969024  1217410048  79 .
2011 11525493760  6337636864  79 .
2000           .           .  92 .
2001   687155072   776453952  92 .
2002   958657472  1084733056  92 .
2003    51737416   361816768  92 .
2004  1351974528  1292766848  92 .
2005    76952512    66355264  92 .
2006  2231222528   388660736  92 .
2007  3502545152  1855511296  92 .
2008  3271688960  6624133120  92 .
2009   730643008 -4754379776  92 .
2010  3267969024  1217410048  92 .
2011 11525493760  6337636864  92 .
2012  9824071680  5696627200  92 .
2000           .           . 106 .
2001   687155072   776453952 106 .
2002   958657472  1084733056 106 .
2003    51737416   361816768 106 .
2004  1351974528  1292766848 106 .
2005    76952512    66355264 106 .
2006  2231222528   388660736 106 .
2007  3502545152  1855511296 106 .
end
[/CODE]
-----------

[Clyde Schechter]

According to your definition of stickiness, it is not possible to calculate stickiness in this data because there are no instances of decreasing sales. Consequently no most recent year with decreasing sales can be found--t2 is undefined.

[ALKEBSEE RADWAN]

Dear @Clyde Schechter I have broken this code into two parts, each represents a part of the command of stickiness. That is, X1= log(d_cost_U[n1]/d_sales_U[n1]) and X2= log(d_cost_U[n2]/d_sales_U[n2]).
then i calculate the stickiness=X1-X2. I got some good results

is it fine?
if yes, I want to know why the orginal command does not work ?

if no, please help me to finish such a calculation

[Clyde Schechter]

I do not think you are accurately reporting what you have done. Breaking it up into two parts is not going to change the results. Please post an example of the starting data (not the intermediate or end results) and the exact code you are using. There is no reason that the X1-X2 approach and the original approach should produce different results (except possibly very tiny differences due to rounding errors--which would make the original approach actually more accurate.) X2 cannot be evaluated, because n2 cannot be evaluated, because there are no decreases in d_sales.

[ALKEBSEE RADWAN]

According to your definition of stickiness, it is not possible to calculate stickiness in this data because there are no instances of decreasing sales. Consequently no most recent year with decreasing sales can be found--t2 is undefined.

Yes, according to the sample example shared above but there are many instances of decreasing sales in the dataset.

[ALKEBSEE RADWAN]

Here is the starting data ( I have removed years before 2007)

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input long code int year float(d_sales d_cost)
1 2007  3502545152  1855511296
1 2008  3271688960  6624133120
1 2009   730643008 -4754379776
1 2010  3267969024  1217410048
1 2011 11525493760  6337636864
1 2012  9824071680  5696627200
1 2013 12382602240 10027013120
1 2014  1.9343e+10  1.4927e+10
1 2015  2.2068e+10  2.0107e+10
1 2016  1.1711e+10  1.0668e+10
1 2017    4.53e+08  -2.373e+09
1 2018   1.343e+09   8.848e+09
1 2019  2.0602e+10 -4.2269e+10
1 2020  1.6428e+10   4.073e+09
1 2021  1.0261e+10   3.366e+09
1 2022   6.966e+09   1.533e+09
2 2007 17678401536 13666197504
2 2008  5465167872  6796171776
2 2009  7889233920  6266779136
2 2010  1832838272 -1540599680
2 2011 2.10689e+10 17134536704
2 2012 31333251072 26306793472
2 2013  3.2303e+10 29138827264
2 2014  1.0969e+10  1.3417e+10
2 2015  4.9161e+10  4.0409e+10
2 2016  4.4928e+10  4.0479e+10
2 2017    2.42e+09  -8.143e+09
2 2018  5.4782e+10  3.8728e+10
2 2019  7.0215e+10  5.7269e+10
2 2020  5.1218e+10  5.6192e+10
2 2021  3.3686e+10  5.2597e+10
2 2022   5.104e+10  5.1901e+10
4 2007    -5189456     9337137
4 2008     2865283   3687074.5
4 2009    16765775  -2104850.8
4 2010    71250896    55114084
4 2011   -56827776   -44604908
4 2012    22859584    23883712
4 2013   -24578734   -23296362
4 2014     7824253    14055684
4 2015    39845604    34588548
4 2016   167215600   151425232
4 2017  -149064192  -118633056
4 2018   228262960   258246800
4 2019  -258622112  -268939264
4 2020   172284304    29413868
4 2021     8413173    52393352
4 2022  -122480888    -6866073
5 2007   -29030654   -80071816
5 2008  -190311344   -92928912
5 2009   -65527788   -29368732
5 2010    94041760    27918140
5 2011   -73699392   -15285444
5 2012    35886388     6142360
5 2013   -47359524   -46379580
5 2014    715291.6    11106601
5 2015    31348976    40612180
5 2016   397737472   337737504
5 2017    49058796    67974864
5 2018   -44741256   -31322890
5 2019    66428104    61558088
5 2020  -153688016  -142375312
5 2021   -15384852   -33489624
5 2022   -99792552   -80424600
6 2007  -230500944  -194548432
6 2008    -2293688    63574036
6 2009   827491264   709167232
6 2010   489498720   150480400
6 2011   250540736   303733056
6 2012   486969792   204511344
6 2013  1534609920  1455409408
6 2014 -2282125312 -1722426240
6 2015  1325579904  1085404544
6 2016  -295483040  -148124384
6 2017  -399376192  -476247040
6 2018  -447605824  -490703104
6 2019  1219485696   778538368
6 2020  -796596864  -459209728
6 2021   153837232   254283264
6 2022   611243008   471336960
7 2007    -8434178   -28179252
7 2008   -26317780   -15488804
7 2009   -25678748   -22130838
7 2010     9980938   -43499224
7 2011    22047432   2194339.3
7 2012   147325392   147552000
7 2013   -98316376  -104699552
7 2014   -16250677    28271612
7 2015   -99136400  -120436248
7 2016   -60101064   -36333116
7 2017    783613.3   -16823048
7 2018     2799830    24431104
7 2019   -423000.3   -37500484
7 2020     3125556  -3414696.5
7 2021   133203520   126934608
7 2022    42488120    47262200
8 2007   -450755.9    445298.4
8 2008     1526274  -1841541.5
8 2009  -1339431.6    -1203554
8 2010    344302.8   -768210.1
end
[/CODE]

then I ran all codes you shared above

  preserve keep code year d_cost d_sales tempfile copy save `copy'  restore rangejoin year -15 -1 using `copy', by(code) isid code year year_U, sort missok by code year (year_U): egen n1 = max(cond(d_sales_U > 0, _n, .)) by code year (year_U): egen n2 = max(cond(d_sales_U < 0, _n, .))

I got the following variables ( i dropped firms that have no intances in decreasing sales to show you that I have such intances) see below

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input long code int(year year_U) float(d_sales_U d_cost_U n1 n2)
4 2007    .          .          . 243   .
4 2008 2007   -5189456    9337137   . 244
4 2009 2007   -5189456    9337137 246 245
4 2009 2008    2865283  3687074.5 246 245
4 2010 2007   -5189456    9337137 249 247
4 2010 2008    2865283  3687074.5 249 247
4 2010 2009   16765775 -2104850.8 249 247
4 2011 2007   -5189456    9337137 253 250
4 2011 2008    2865283  3687074.5 253 250
4 2011 2009   16765775 -2104850.8 253 250
4 2011 2010   71250896   55114084 253 250
4 2012 2007   -5189456    9337137 257 258
4 2012 2008    2865283  3687074.5 257 258
4 2012 2009   16765775 -2104850.8 257 258
4 2012 2010   71250896   55114084 257 258
4 2012 2011  -56827776  -44604908 257 258
4 2013 2007   -5189456    9337137 264 263
4 2013 2008    2865283  3687074.5 264 263
4 2013 2009   16765775 -2104850.8 264 263
4 2013 2010   71250896   55114084 264 263
4 2013 2011  -56827776  -44604908 264 263
4 2013 2012   22859584   23883712 264 263
4 2014 2007   -5189456    9337137 270 271
4 2014 2008    2865283  3687074.5 270 271
4 2014 2009   16765775 -2104850.8 270 271
4 2014 2010   71250896   55114084 270 271
4 2014 2011  -56827776  -44604908 270 271
4 2014 2012   22859584   23883712 270 271
4 2014 2013  -24578734  -23296362 270 271
4 2015 2007   -5189456    9337137 279 278
4 2015 2008    2865283  3687074.5 279 278
4 2015 2009   16765775 -2104850.8 279 278
4 2015 2010   71250896   55114084 279 278
4 2015 2011  -56827776  -44604908 279 278
4 2015 2012   22859584   23883712 279 278
4 2015 2013  -24578734  -23296362 279 278
4 2015 2014    7824253   14055684 279 278
4 2016 2007   -5189456    9337137 288 286
4 2016 2008    2865283  3687074.5 288 286
4 2016 2009   16765775 -2104850.8 288 286
4 2016 2010   71250896   55114084 288 286
4 2016 2011  -56827776  -44604908 288 286
4 2016 2012   22859584   23883712 288 286
4 2016 2013  -24578734  -23296362 288 286
4 2016 2014    7824253   14055684 288 286
4 2016 2015   39845604   34588548 288 286
4 2017 2007   -5189456    9337137 298 295
4 2017 2008    2865283  3687074.5 298 295
4 2017 2009   16765775 -2104850.8 298 295
4 2017 2010   71250896   55114084 298 295
4 2017 2011  -56827776  -44604908 298 295
4 2017 2012   22859584   23883712 298 295
4 2017 2013  -24578734  -23296362 298 295
4 2017 2014    7824253   14055684 298 295
4 2017 2015   39845604   34588548 298 295
4 2017 2016  167215600  151425232 298 295
4 2018 2007   -5189456    9337137 308 309
4 2018 2008    2865283  3687074.5 308 309
4 2018 2009   16765775 -2104850.8 308 309
4 2018 2010   71250896   55114084 308 309
4 2018 2011  -56827776  -44604908 308 309
4 2018 2012   22859584   23883712 308 309
4 2018 2013  -24578734  -23296362 308 309
4 2018 2014    7824253   14055684 308 309
4 2018 2015   39845604   34588548 308 309
4 2018 2016  167215600  151425232 308 309
4 2018 2017 -149064192 -118633056 308 309
4 2019 2007   -5189456    9337137 321 320
4 2019 2008    2865283  3687074.5 321 320
4 2019 2009   16765775 -2104850.8 321 320
4 2019 2010   71250896   55114084 321 320
4 2019 2011  -56827776  -44604908 321 320
4 2019 2012   22859584   23883712 321 320
4 2019 2013  -24578734  -23296362 321 320
4 2019 2014    7824253   14055684 321 320
4 2019 2015   39845604   34588548 321 320
4 2019 2016  167215600  151425232 321 320
4 2019 2017 -149064192 -118633056 321 320
4 2019 2018  228262960  258246800 321 320
4 2020 2007   -5189456    9337137 333 334
4 2020 2008    2865283  3687074.5 333 334
4 2020 2009   16765775 -2104850.8 333 334
4 2020 2010   71250896   55114084 333 334
4 2020 2011  -56827776  -44604908 333 334
4 2020 2012   22859584   23883712 333 334
4 2020 2013  -24578734  -23296362 333 334
4 2020 2014    7824253   14055684 333 334
4 2020 2015   39845604   34588548 333 334
4 2020 2016  167215600  151425232 333 334
4 2020 2017 -149064192 -118633056 333 334
4 2020 2018  228262960  258246800 333 334
4 2020 2019 -258622112 -268939264 333 334
4 2021 2007   -5189456    9337137 348 347
4 2021 2008    2865283  3687074.5 348 347
4 2021 2009   16765775 -2104850.8 348 347
4 2021 2010   71250896   55114084 348 347
4 2021 2011  -56827776  -44604908 348 347
4 2021 2012   22859584   23883712 348 347
4 2021 2013  -24578734  -23296362 348 347
4 2021 2014    7824253   14055684 348 347
end
[/CODE]

then I ran the command of stickiness as follows

by code year (year_U):gen stickiness = log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])

I got stickiness with all missing values

after that I tried to find a way to get a results. i did break this command up into two parts as follows

gen x1=log(d_cost_U[n1]/d_sales_U[n1])
gen x2=log(d_cost_U[n2]/d_sales_U[n2])
drop if code ==2
gen stickiness2= x1 -x2

I got the following results ( I added stickiness variable got from the orginal code as well to show you )

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input long code int(year year_U) float(d_sales_U d_cost_U n1 n2 stickiness stickiness2)
4 2007    .          .          . 243   . .          .
4 2008 2007   -5189456    9337137   . 244 .          .
4 2009 2007   -5189456    9337137 246 245 .          .
4 2009 2008    2865283  3687074.5 246 245 .          .
4 2010 2007   -5189456    9337137 249 247 . .015266627
4 2010 2008    2865283  3687074.5 249 247 . .015266627
4 2010 2009   16765775 -2104850.8 249 247 . .015266627
4 2011 2007   -5189456    9337137 253 250 . -1.0099752
4 2011 2008    2865283  3687074.5 253 250 . -1.0099752
4 2011 2009   16765775 -2104850.8 253 250 . -1.0099752
4 2011 2010   71250896   55114084 253 250 . -1.0099752
4 2012 2007   -5189456    9337137 257 258 . -1.3720766
4 2012 2008    2865283  3687074.5 257 258 . -1.3720766
4 2012 2009   16765775 -2104850.8 257 258 . -1.3720766
4 2012 2010   71250896   55114084 257 258 . -1.3720766
4 2012 2011  -56827776  -44604908 257 258 . -1.3720766
4 2013 2007   -5189456    9337137 264 263 . -1.0601063
4 2013 2008    2865283  3687074.5 264 263 . -1.0601063
4 2013 2009   16765775 -2104850.8 264 263 . -1.0601063
4 2013 2010   71250896   55114084 264 263 . -1.0601063
4 2013 2011  -56827776  -44604908 264 263 . -1.0601063
4 2013 2012   22859584   23883712 264 263 . -1.0601063
4 2014 2007   -5189456    9337137 270 271 .  -.8145896
4 2014 2008    2865283  3687074.5 270 271 .  -.8145896
4 2014 2009   16765775 -2104850.8 270 271 .  -.8145896
4 2014 2010   71250896   55114084 270 271 .  -.8145896
4 2014 2011  -56827776  -44604908 270 271 .  -.8145896
4 2014 2012   22859584   23883712 270 271 .  -.8145896
4 2014 2013  -24578734  -23296362 270 271 .  -.8145896
4 2015 2007   -5189456    9337137 279 278 .  -.2283843
4 2015 2008    2865283  3687074.5 279 278 .  -.2283843
4 2015 2009   16765775 -2104850.8 279 278 .  -.2283843
4 2015 2010   71250896   55114084 279 278 .  -.2283843
4 2015 2011  -56827776  -44604908 279 278 .  -.2283843
4 2015 2012   22859584   23883712 279 278 .  -.2283843
4 2015 2013  -24578734  -23296362 279 278 .  -.2283843
4 2015 2014    7824253   14055684 279 278 .  -.2283843
4 2016 2007   -5189456    9337137 288 286 . -.14690828
4 2016 2008    2865283  3687074.5 288 286 . -.14690828
4 2016 2009   16765775 -2104850.8 288 286 . -.14690828
4 2016 2010   71250896   55114084 288 286 . -.14690828
4 2016 2011  -56827776  -44604908 288 286 . -.14690828
4 2016 2012   22859584   23883712 288 286 . -.14690828
4 2016 2013  -24578734  -23296362 288 286 . -.14690828
4 2016 2014    7824253   14055684 288 286 . -.14690828
4 2016 2015   39845604   34588548 288 286 . -.14690828
4 2017 2007   -5189456    9337137 298 295 .  -.6375701
4 2017 2008    2865283  3687074.5 298 295 .  -.6375701
4 2017 2009   16765775 -2104850.8 298 295 .  -.6375701
4 2017 2010   71250896   55114084 298 295 .  -.6375701
4 2017 2011  -56827776  -44604908 298 295 .  -.6375701
4 2017 2012   22859584   23883712 298 295 .  -.6375701
4 2017 2013  -24578734  -23296362 298 295 .  -.6375701
4 2017 2014    7824253   14055684 298 295 .  -.6375701
4 2017 2015   39845604   34588548 298 295 .  -.6375701
4 2017 2016  167215600  151425232 298 295 .  -.6375701
4 2018 2007   -5189456    9337137 308 309 .   -.866592
4 2018 2008    2865283  3687074.5 308 309 .   -.866592
4 2018 2009   16765775 -2104850.8 308 309 .   -.866592
4 2018 2010   71250896   55114084 308 309 .   -.866592
4 2018 2011  -56827776  -44604908 308 309 .   -.866592
4 2018 2012   22859584   23883712 308 309 .   -.866592
4 2018 2013  -24578734  -23296362 308 309 .   -.866592
4 2018 2014    7824253   14055684 308 309 .   -.866592
4 2018 2015   39845604   34588548 308 309 .   -.866592
4 2018 2016  167215600  151425232 308 309 .   -.866592
4 2018 2017 -149064192 -118633056 308 309 .   -.866592
4 2019 2007   -5189456    9337137 321 320 . -.08410662
4 2019 2008    2865283  3687074.5 321 320 . -.08410662
4 2019 2009   16765775 -2104850.8 321 320 . -.08410662
4 2019 2010   71250896   55114084 321 320 . -.08410662
4 2019 2011  -56827776  -44604908 321 320 . -.08410662
4 2019 2012   22859584   23883712 321 320 . -.08410662
4 2019 2013  -24578734  -23296362 321 320 . -.08410662
4 2019 2014    7824253   14055684 321 320 . -.08410662
4 2019 2015   39845604   34588548 321 320 . -.08410662
4 2019 2016  167215600  151425232 321 320 . -.08410662
4 2019 2017 -149064192 -118633056 321 320 . -.08410662
4 2019 2018  228262960  258246800 321 320 . -.08410662
4 2020 2007   -5189456    9337137 333 334 .   .5406925
4 2020 2008    2865283  3687074.5 333 334 .   .5406925
4 2020 2009   16765775 -2104850.8 333 334 .   .5406925
4 2020 2010   71250896   55114084 333 334 .   .5406925
4 2020 2011  -56827776  -44604908 333 334 .   .5406925
4 2020 2012   22859584   23883712 333 334 .   .5406925
4 2020 2013  -24578734  -23296362 333 334 .   .5406925
4 2020 2014    7824253   14055684 333 334 .   .5406925
4 2020 2015   39845604   34588548 333 334 .   .5406925
4 2020 2016  167215600  151425232 333 334 .   .5406925
4 2020 2017 -149064192 -118633056 333 334 .   .5406925
4 2020 2018  228262960  258246800 333 334 .   .5406925
4 2020 2019 -258622112 -268939264 333 334 .   .5406925
4 2021 2007   -5189456    9337137 348 347 . -.10207549
4 2021 2008    2865283  3687074.5 348 347 . -.10207549
4 2021 2009   16765775 -2104850.8 348 347 . -.10207549
4 2021 2010   71250896   55114084 348 347 . -.10207549
4 2021 2011  -56827776  -44604908 348 347 . -.10207549
4 2021 2012   22859584   23883712 348 347 . -.10207549
4 2021 2013  -24578734  -23296362 348 347 . -.10207549
4 2021 2014    7824253   14055684 348 347 . -.10207549
end
[/CODE]
-------

Than you very much for you kindness

[Clyde Schechter]

So, I repeated what you did. It does not give all missing values. It does give some missing values for stickiness, precisely in those cases where there has been no decrease in sales in the preceding 15 years. So that is appropriate.

Your alternative code

gen x1=log(d_cost_U[n1]/d_sales_U[n1])
gen x2=log(d_cost_U[n2]/d_sales_U[n2])
drop if code ==2
gen stickiness2= x1 -x2

does indeed produce different values, different values which are incorrect. The problem with this alternative code is that it does not have the -by code year (year_U):- prefixes, and as a result, the values of n1 and n2 refer to the observations in the entire data set, not the observations associated with the same code and year. It is, instead, picking up values of d_sales_U and d_cost_U from observations relating to other codes or years.

I do have a theory, though, about why we are getting different results with the original code. I wonder if you are using a different version of Stata. I'm using version 18. There is a problem with the original code, which I will fix below. The problem is that it is not permissible to use _n and _N with -egen- functions because -egen- sometimes reshuffles the data. This may work differently in different versions. In this case, the trick is to pull out the right values for the observation numbers within code-year groups before hand and save them in a variable, and then use that variable instead of _n. So here is corrected code:

preserve
keep code year d_cost d_sales
tempfile copy
save `copy'

restore
rangejoin year -15 -1 using `copy', by(code)
isid code year year_U, sort missok
by code year (year_U): gen index = _n
by code year (year_U): egen n1 = max(cond(d_sales_U > 0, index, .))
by code year (year_U): egen n2 = max(cond(d_sales_U < 0, index, .))
by code year (year_U):gen stickiness = ///
    log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])
by code year: keep if _n == 1

On my setup it gives the same results as the original code in #2, but perhaps in yours it will work better than the original did. You should still find all missing results for codes 1, 2, and 8 (because there are no decreased sales within the 15 year timeframes). For the other codes, you will get non-missing values for most years (though not for all of them--sometimes there is no timely year with decreased sales.)

[ALKEBSEE RADWAN]

So, I repeated what you did. It does not give all missing values. It does give some missing values for stickiness, precisely in those cases where there has been no decrease in sales in the preceding 15 years. So that is appropriate.

Your alternative code

gen x1=log(d_cost_U[n1]/d_sales_U[n1])
gen x2=log(d_cost_U[n2]/d_sales_U[n2])
drop if code ==2
gen stickiness2= x1 -x2

does indeed produce different values, different values which are incorrect. The problem with this alternative code is that it does not have the -by code year (year_U):- prefixes, and as a result, the values of n1 and n2 refer to the observations in the entire data set, not the observations associated with the same code and year. It is, instead, picking up values of d_sales_U and d_cost_U from observations relating to other codes or years.

I see.
So, what do you think the problem is ?
Is there a problem related to stata software version or what ?
I am frustrated now.
when I ran the orginal command see what is get as a notification

. by code year (year_U):gen stickiness4 = log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])
(299,048 missing values generated)

I tried to use the following command to fix it but the results are same stickiness2

by code year (year_U):gen stickiness3= x1 -x2

my stata version is 15

kindly do you have suggestions?

[ALKEBSEE RADWAN]

I will try the new codes then come back to you

Thank you very much

[ALKEBSEE RADWAN]

[QUOTE=Clyde Schechter;n1736537]So, I repeated what you did. It does not give all missing values. It does give some missing values for stickiness, precisely in those cases where there has been no decrease in sales in the preceding 15 years. So that is appropriate.

Your alternative code

gen x1=log(d_cost_U[n1]/d_sales_U[n1])
gen x2=log(d_cost_U[n2]/d_sales_U[n2])
drop if code ==2
gen stickiness2= x1 -x2

does indeed produce different values, different values which are incorrect. The problem with this alternative code is that it does not have the -by code year (year_U):- prefixes, and as a result, the values of n1 and n2 refer to the observations in the entire data set, not the observations associated with the same code and year. It is, instead, picking up values of d_sales_U and d_cost_U from observations relating to other codes or years.

I do have a theory, though, about why we are getting different results with the original code. I wonder if you are using a different version of Stata. I'm using version 18. There is a problem with the original code, which I will fix below. The problem is that it is not permissible to use _n and _N with -egen- functions because -egen- sometimes reshuffles the data. This may work differently in different versions. In this case, the trick is to pull out the right values for the observation numbers within code-year groups before hand and save them in a variable, and then use that variable instead of _n. So here is corrected code:

preserve
keep code year d_cost d_sales
tempfile copy
save `copy'

restore
rangejoin year -15 -1 using `copy', by(code)
isid code year year_U, sort missok
by code year (year_U): gen index = _n
by code year (year_U): egen n1 = max(cond(d_sales_U > 0, index, .))
by code year (year_U): egen n2 = max(cond(d_sales_U < 0, index, .))
by code year (year_U):gen stickiness = ///
log(d_cost_U[n1]/d_sales_U[n1]) - log(d_cost_U[n2]/d_sales_U[n2])
by code year: keep if _n == 1

On my setup it gives the same results as the original code in #2, but perhaps in yours it will work better than the original did. You should still find all missing results for codes 1, 2, and 8 (because there are no decreased sales within the 15 year timeframes). For the other codes, you will get non-missing values for most years (though not for all of them--sometimes there is no timely year with decreased sales.)[/QUOT


Thank you very very much
now it is working well


Gratitude