[Clyde Schechter]

Well, I thought an example would make it clearer, but now I'm even more confused. Let's look at 20th CENTURY ENGGG.LTD Securities investment services. 1999 is the first year of any reporting for this company. In 1999 sales are 1.009808, and in 2000 they are 0.9702497, so the change is -.039552 in 2000. But you have intensive = -25.66838. Why? Where does that come from? Now let's look at 20th CENTURY ENGG. LTD Technical Consultancy & engineer. In 2001 you show intensive = -.1507953. But they weren't selling this product in 2000. So there should be no intensive margin at all: it isn't even in the intensive product class. And 205h CENTURY ENGG. LTD Trade & Commissioning Agents'... In 2000 you show intensive = -25.66838, but, again, this product isn't sold in 1999, so it isn't in the intensive class. So what is going on?

I haven't proceeded any farther than this investigation. There may well be other things that are puzzling after I look into them. But let's at least get this much straight before I try.

[Sugandha Huria]

* Example generated by -dataex-. To install: ssc install dataex clear input byte co_code str23 company_name double product_code str44 product_name int yr long nic float(dsales intensive growing shrinking extensive added dropped cal_output outputchange) 12 "20TH CENTURY ENGG. LTD." 2.100e+19 "Trade in textiles & apparels" 1999 71100 25.7501 0 0 0 26.75991 26.75991 . 26.75991 26.75991 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 1999 71100 1.009808 0 0 0 26.75991 26.75991 . 26.75991 26.75991 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2000 71100 .9702497 -25.668377 0 -25.668377 0 0 . -25.668377 -25.668377 12 "20TH CENTURY ENGG. LTD." 2.100e+19 "Trade & Commissioning Agents' services" 2000 71100 .1212812 -25.668377 0 -25.668377 0 0 . -25.668377 -25.668377 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Technical Consultancy & engineering services" 2001 71100 8.896934 -.1507953 0 0 7.926684 8.896934 -.9702497 7.926684 8.624858 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2001 71100 .8194544 -.1507953 0 -.1507953 7.926684 8.896934 -.9702497 7.775889 8.624858 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Technical Consultancy & engineering services" 2002 71100 8.762367 -.134567 0 -.134567 -.8194544 0 -.8194544 -.9540214 -.9540214 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2005 71100 8.7 0 0 0 8.7 8.7 0 8.7 8.7 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2006 71100 9.100483 .400483 .400483 0 0 0 0 .400483 .400483 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2007 71100 26.40141 17.300926 17.300926 0 0 0 0 17.300926 17.300926 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2008 71100 26.63156 .23015 .23015 0 0 0 0 .23015 .23015 12 "20TH CENTURY ENGG. LTD." 2.110e+19 "Securities investment services" 2009 71100 3.859529 -22.77203 -22.77203 0 0 0 0 -22.77203 -22.77203 end In 1999, sales are (25.7501 + 1.009808) = (26.75991).This is the total sales in the year 1999. Now, in the first reporting year, intensive margin is always zero whereas extensive margin = sales of the products in year t. In 2000, the firm is producing two products viz. securities and trade and commissiong (both are old products as regards their product coding). So, in this case, intensive margin will be equal to the sum of sales in year t - sum of sales in year t-1 = (0.970+0.1212 - 1.009-25.75)=25.6687. Actually, I computed these based on the product codes and not on the basis of the product names. sorry for all the confusion

[Sugandha Huria]

I'm sorry for the previous post

[Sugandha Huria]

In 1999, sales are (25.7501 + 1.009808) = (26.75991).This is the total sales in the year 1999. Now, in the first reporting year, intensive margin is always zero whereas extensive margin = sales of the products in year t.
In 2000, the firm is producing two products viz. securities and trade and commissiong (both are old products as regards their product coding). So, in this case, intensive margin will be equal to the sum of sales in year t - sum of sales in year t-1 = (0.970+0.1212 - 1.009-25.75)=25.6687. Actually, I computed these based on the product codes and not on the basis of the product names. sorry for all the confusion

[Sugandha Huria]

Please give me few minutes, I will solve another example. Thank you so much for all your help.

[Sugandha Huria]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte co_code int(product_cmie_code yr) float(dsales growing shrinking intensive added dropped extensive cal_output outputchange)
1 3775 1999 1.009808         0         0         0 26.75991         0  26.75991   26.75991   26.75991
1 3624 1999  25.7501         0         0         0 26.75991         0  26.75991   26.75991   26.75991
1 3600 2000 .1212812         0 -.0395583 -.0395583 .1212812  -25.7501 -25.62882 -25.668377 -25.668377
1 3775 2000 .9702497         0 -.0395583 -.0395583 .1212812  -25.7501 -25.62882 -25.668377 -25.668377
1 3775 2001 .8194544         0 -.1507953 -.1507953 8.896934 -.1212812  8.775653   8.624858   8.624858
1 3810 2001 8.896934         0 -.1507953 -.1507953 8.896934 -.1212812  8.775653   8.624858   8.624858
1 3810 2002 8.762367         0  -.134567  -.134567        0 -.8194544 -.8194544  -.9540214  -.9540214
1 3775 2005      8.7         0         0         0      8.7         0       8.7        8.7        8.7
1 3775 2006 9.100483   .400483         0   .400483        0         0         0    .400483    .400483
1 3775 2007 26.40141 17.300926         0 17.300926        0         0         0  17.300926  17.300926
1 3775 2008 26.63156    .23015         0    .23015        0         0         0     .23015     .23015
1 3775 2009 3.859529         0 -22.77203 -22.77203        0         0         0  -22.77203  -22.77203
end

[Sugandha Huria]

Sir, could you please help me in creating these codes? or if you can suggest some reading which I can read to improve my command over the software. I'm sorry to ask you again.

[Clyde Schechter]

OK. I think I have it. In this code, I rename the results that you calculated by prefixing them with your initials, sh. Then I calculate the results and verify that, at least to three decimal places, they agree with yours.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte co_code int(product_cmie_code yr) float(dsales growing shrinking intensive added dropped extensive cal_output outputchange)
1 3775 1999 1.009808         0         0         0 26.75991         0  26.75991   26.75991   26.75991
1 3624 1999  25.7501         0         0         0 26.75991         0  26.75991   26.75991   26.75991
1 3600 2000 .1212812         0 -.0395583 -.0395583 .1212812  -25.7501 -25.62882 -25.668377 -25.668377
1 3775 2000 .9702497         0 -.0395583 -.0395583 .1212812  -25.7501 -25.62882 -25.668377 -25.668377
1 3775 2001 .8194544         0 -.1507953 -.1507953 8.896934 -.1212812  8.775653   8.624858   8.624858
1 3810 2001 8.896934         0 -.1507953 -.1507953 8.896934 -.1212812  8.775653   8.624858   8.624858
1 3810 2002 8.762367         0  -.134567  -.134567        0 -.8194544 -.8194544  -.9540214  -.9540214
1 3775 2005      8.7         0         0         0      8.7         0       8.7        8.7        8.7
1 3775 2006 9.100483   .400483         0   .400483        0         0         0    .400483    .400483
1 3775 2007 26.40141 17.300926         0 17.300926        0         0         0  17.300926  17.300926
1 3775 2008 26.63156    .23015         0    .23015        0         0         0     .23015     .23015
1 3775 2009 3.859529         0 -22.77203 -22.77203        0         0         0  -22.77203  -22.77203
end

egen co_product = group(co_code product_cmie_code), label
xtset co_product yr

rename (growing-outputchange) sh_=

gen product_change = D.dsales
gen byte is_intensive = !missing(product_change)
by co_code, sort: egen final_year = max(yr)

by co_code yr, sort: egen intensive = total(product_change)

by co_code yr, sort: egen growing = total(cond(product_change > 0, product_change, .))
by co_code yr, sort: egen shrinking = total(cond(product_change < 0, product_change, .))
foreach v of varlist growing shrinking intensive {
    assert round(`v', 0.001) == round(sh_`v', 0.001)
}

sort co_product yr
gen added = dsales if missing(L.dsales)
gen x_dropped = -dsales if missing(F.dsales)
by co_code yr, sort: replace added = sum(added)
by co_code yr: replace added = added[_N]
rangestat (sum) dropped = x_dropped, interval(yr -1 -1) by(co_code)
replace dropped = 0 if missing(dropped)
drop x_dropped

foreach v of varlist added dropped {
    assert round(`v', 0.001) == round(sh_`v', 0.001)
}

gen extensive = dropped + added
gen cal_output = growing + shrinking + added + dropped
foreach v of varlist extensive cal_output {
    assert round(`v', 0.001) == round(sh_`v', 0.001)
}

The trickiest one is the dropped margin, because you want to place those figures in years where, by definition, there is no observation for that product! -rangestat- does that. It is written by Robert Picard, Nick Cox, and Roberto Ferrer. You can get it by running -ssc install rangestat-.

Evidently, for production run purposes, you will start with a data set that does not have the variables growing-outputchange, and you will similarly eliminate the blocks of code that serve only to verify that my code produces (to three decimal places) the same results as yours.

[Sugandha Huria]

Thank you so much for all the help. I also tried writing something yesterday evening and could verify my results. Though I used a very long process, in comparison to that of yours. If you don't mind, can I please share my commands with you? I just wanted to get them verified from somebody so as to know if I am on the right track or not.
Thank you.

[Jane Quan]

I have little confidence that I understand what you want here. But perhaps this is it:

by company products_product_code (yr), sort: gen intensive = (yr[_n-1] == yr-1)

by company yr, sort: egen intensive_margin = total(cond(intensive, dsales, .))
by company yr, sort: egen extensive_margin = total(cond(intensive, ., dsales))

By the way, you have a serious problem in your data. The variable product_code is apparently supposed to be 19 or 20 digits long. But you can't squeeze that much information into a double. It is likely that the data is already corrupted and that some product codes that differ in their low order 3 or 4 digits have been stored in the data as the same number. You will need to re-create the data set taking care to create the product code variable as a string variable.

Hello Mr. Schechter,

I have a question about constructing intensive margins with STATA for a slightly different case than the previous one.
The desired intensive margin is calculated as the ratio of the sum of import values for products produced both in periods t0 and t-1 (see the formula below), compared between time t1 and t0.

I implemented a similar approach to the code you provided in #2. My own code and data are presented below for your reference.

- by i k (t), sort: gen intensive = (t[_n-1] == t-1)
- by i t, sort: gen intensive_margin = total(cond(intensive, v, .))/l.total(cond(intensive, v, .))

where t is year, i is export country, k is the code of products, and v is the value of the product.
my problem is that the second codes reports "varlist not allowed", Could you provide some guidance on this matter? Thank you in advance.

* Example generated by -dataex-. For more info, type help dataex
clear
input int t long k float intensive int(i j) double kk float(v q) double ii float extensive_margin
2019  91020 0  4 410  91020     .087    .001  4     .155
2019 190590 0  4 410 190590     .025    .001  4     .155
2019 200819 0  4 410 200819     .043    .002  4     .155
2020 180632 0  4 410 180632     .017    .002  4     .563
2020 210390 0  4 410 210390     .116    .002  4     .563
2020 190590 1  4 410 190590     .028    .003  4     .563
2020 210690 0  4 410 210690     .112    .007  4     .563
2020 180690 0  4 410 180690     .092    .004  4     .563
2020 190219 0  4 410 190219     .009    .011  4     .563
2020  80290 0  4 410  80290     .204    .006  4     .563
2020 190530 0  4 410 190530     .012    .003  4     .563
2020 170490 0  4 410 170490     .001    .001  4     .563
2021 170490 1  4 410 170490     .038    .003  4        0
2021 210690 1  4 410 210690     .072    .009  4        0
2021 190219 1  4 410 190219     .006    .005  4        0
2021 210390 1  4 410 210390     .116    .002  4        0
2009 240120 0  8 410 240120 1497.842  235.62  8 1499.566
2009 121190 0  8 410 121190    1.724    .127  8 1499.566
2010 121299 0  8 410 121299     .338     .05  8   11.117
2010 240120 1  8 410 240120 2489.141 401.964  8   11.117
2010 121190 1  8 410 121190    1.945     .13  8   11.117
2010  30379 0  8 410  30379   10.779  12.125  8   11.117
2011 240110 0  8 410 240110 1783.617  291.06  8 1783.617
2011 240120 1  8 410 240120     .112    .029  8 1783.617
2011 121190 1  8 410 121190    1.041     .07  8 1783.617
2012 240110 1  8 410 240110 1169.183   207.9  8   29.389
2012 121190 1  8 410 121190     5.13    .303  8   29.389
2012 160416 0  8 410 160416   26.044   1.632  8   29.389
2012 210690 0  8 410 210690    3.345    .105  8   29.389
2012 240120 1  8 410 240120     .047    .011  8   29.389
2013 210690 1  8 410 210690    12.45    .373  8        0
2013 240120 1  8 410 240120     .105    .025  8        0
2013 160416 1  8 410 160416   51.668   2.832  8        0
2013 240110 1  8 410 240110 5598.385  845.46  8        0
2013 121190 1  8 410 121190    1.736    .068  8        0
2014 240110 1  8 410 240110 1304.388   207.9  8    3.611
2014  91099 0  8 410  91099    3.611    .694  8    3.611
2014 121190 1  8 410 121190     6.72    .334  8    3.611
2014 210690 1  8 410 210690    7.976    .209  8    3.611
2015 160416 0  8 410 160416    31.77   2.592  8    31.77
2015 121190 1  8 410 121190   14.573     .84  8    31.77
2015  91099 1  8 410  91099    4.641    1.04  8    31.77
2015 210690 1  8 410 210690     .068    .001  8    31.77
2016 160416 1  8 410 160416  183.325  15.552  8 2215.173
2016 210390 0  8 410 210390     .035    .001  8 2215.173
2016 220421 0  8 410 220421     .011    .001  8 2215.173
2016 150990 0  8 410 150990     .027    .002  8 2215.173
2016 151000 0  8 410 151000     .016    .001  8 2215.173
2016 240110 0  8 410 240110 2212.746  582.12  8 2215.173
2016 121190 1  8 410 121190   25.693   2.385  8 2215.173
2016 151919 0  8 410 151919     .013    .001  8 2215.173
2016  91099 1  8 410  91099    5.743   1.272  8 2215.173
2016  81340 0  8 410  81340    2.325      .1  8 2215.173
2016 210690 1  8 410 210690    3.173    .166  8 2215.173
2017 160416 1  8 410 160416  233.296  20.041  8    8.474
2017 121190 1  8 410 121190   37.618   8.025  8    8.474
2017 160590 0  8 410 160590     .109    .001  8    8.474
2017 210690 1  8 410 210690    7.482    .209  8    8.474
2017  30563 0  8 410  30563    8.365    .691  8    8.474
2017  91099 1  8 410  91099    6.162    1.29  8    8.474
2018 121190 1  8 410 121190   155.21  26.881  8     .521
2018 160416 1  8 410 160416  162.965  11.984  8     .521
2018 210390 0  8 410 210390     .504    .011  8     .521
2018 210690 1  8 410 210690   22.112    .531  8     .521
2018  30563 1  8 410  30563    1.639     .13  8     .521
2018 240290 0  8 410 240290     .017    .001  8     .521
2019 121190 1  8 410 121190   89.401   15.51  8  206.686
2019 240110 0  8 410 240110     .213    .022  8  206.686
2019  30379 0  8 410  30379   24.092   1.312  8  206.686
2019  30420 0  8 410  30420    30.41    .713  8  206.686
2019 160416 1  8 410 160416  140.938   9.362  8  206.686
2019 160420 0  8 410 160420   27.296   2.765  8  206.686
2019 190590 0  8 410 190590     .778    .072  8  206.686
2019 210690 1  8 410 210690   32.843    .757  8  206.686
2019  30349 0  8 410  30349  121.583   7.467  8  206.686
2019 121299 0  8 410 121299    2.314      .2  8  206.686
2020  30420 1  8 410  30420  190.858   8.287  8     .005
2020 160416 1  8 410 160416  212.637  14.815  8     .005
2020 210690 1  8 410 210690    9.907     .26  8     .005
2020 160420 1  8 410 160420   18.173    2.16  8     .005
2020 121190 1  8 410 121190   26.687   1.791  8     .005
2020 210120 0  8 410 210120     .005       .  8     .005
2021 160416 1  8 410 160416  332.232  23.509  8    9.107
2021 160415 0  8 410 160415    2.424      .2  8    9.107
2021 210130 0  8 410 210130     .023    .018  8    9.107
2021 151590 0  8 410 151590     .011    .003  8    9.107
2021 121490 0  8 410 121490     .279     .04  8    9.107
2021 121190 1  8 410 121190   64.222   5.903  8    9.107
2021 210390 0  8 410 210390     6.37    .313  8    9.107
2021 210690 1  8 410 210690   17.066    .318  8    9.107
2009 190590 0 12 410 190590     .034    .001 12  511.445
2009  30420 0 12 410  30420  505.743   7.437 12  511.445
2009 200990 0 12 410 200990     .012    .001 12  511.445
2009 220429 0 12 410 220429     .048    .001 12  511.445
2009  30349 0 12 410  30349    5.608    .361 12  511.445
2010  30349 1 12 410  30349  361.801   9.069 12  120.095
2010 210690 0 12 410 210690     .011    .002 12  120.095
2010 121299 0 12 410 121299   59.922 137.611 12  120.095
2010  30420 1 12 410  30420  585.956  12.459 12  120.095
2010 190590 1 12 410 190590     .037    .011 12  120.095
end

[Clyde Schechter]

I cannot reproduce the error message you are getting. I get a different, and much more informative, error message. I wonder whether the code you posted is exactly the code you are running. Or perhaps you are running an older version of Stata which gives a different error message than the one I'm getting.

Be that as it may, the error message I get, is "unknown function total()." This error message is factually correct, because there is no function named -total()- that works with the -gen- command. -total()- is an -egen- function. Even if I were to just fix that, however, you would hit another snag: you cannot use the l. operator without first -xtset-ing your data. But you cannot -xtset i t- in this data because you have multiple observations (indexed by k) per i#t pair. While there are ways to program around this problem within the data set as it stands, it is far easier to do this in a separate frame, where you can -collapse- the data while preserving the integrity of the original data in the original frame.

by i k (t), sort: gen intensive = (t[_n-1] == t-1)

frame put i t  v if intensive, into(working)
frame working {
    collapse (sum) v, by(i t)
    xtset i t
    gen intensive_margin = v/L.v
}
frlink m:1 i t, frame(working)
frget intensive_margin, from(working)
drop working
frame drop working

Note: The formula you show for intensive margin in your post is bristling with symbols, many of which are unexplained. So I'm not entirely certain that my code implements that formula correctly. What I have done is intuit what I think you were trying to calculate with your code, and shown you how that calculation could be done in Stata. Whether that calculation implements that formula I am agnostic about.

[Jane Quan]

I cannot reproduce the error message you are getting. I get a different, and much more informative, error message. I wonder whether the code you posted is exactly the code you are running. Or perhaps you are running an older version of Stata which gives a different error message than the one I'm getting.

Be that as it may, the error message I get, is "unknown function total()." This error message is factually correct, because there is no function named -total()- that works with the -gen- command. -total()- is an -egen- function. Even if I were to just fix that, however, you would hit another snag: you cannot use the l. operator without first -xtset-ing your data. But you cannot -xtset i t- in this data because you have multiple observations (indexed by k) per i#t pair. While there are ways to program around this problem within the data set as it stands, it is far easier to do this in a separate frame, where you can -collapse- the data while preserving the integrity of the original data in the original frame.

by i k (t), sort: gen intensive = (t[_n-1] == t-1)

frame put i t v if intensive, into(working)
frame working {
collapse (sum) v, by(i t)
xtset i t
gen intensive_margin = v/L.v
}
frlink m:1 i t, frame(working)
frget intensive_margin, from(working)
drop working
frame drop working

Note: The formula you show for intensive margin in your post is bristling with symbols, many of which are unexplained. So I'm not entirely certain that my code implements that formula correctly. What I have done is intuit what I think you were trying to calculate with your code, and shown you how that calculation could be done in Stata. Whether that calculation implements that formula I am agnostic about.

Thank you for your comprehensive response, and I apologize for not clearly explaining the formula I utilized.
In fact, I employed the following code instead. While it may not appear as short and tidy, I have verified the results, and they align with my expectations

use kragim, clear
keep if t==2009 | t==2010
duplicates tag k i, gen(flag)
keep if flag
by t i, sort: egen im=sum(v)
duplicates drop t i, force
xtset i t
gen intensivemargin=im/L.im
keep if t==2010
save intensivemargin2010, replace

use kragim, clear
keep if t==2010 | t==2011
duplicates tag k i, gen(flag)
keep if flag
by t i, sort: egen im=sum(v)
duplicates drop t i, force
xtset i t
gen intensivemargin=im/L.im
keep if t==2011
save intensivemargin2011, replace
....
Given that my data spans from 2009 to 2021, I have replicated it until 2021, pairing each two-year interval.