Tabulate two way by summarizing the sum

[Muhammad Arslan Iqbal]

Hi Everyone,

I have the following data on year (first dimension of panel) , market (second dimension of panel) and product code/type (third dimension of panel) along with market share where market is defined as year and market dimension interaction. The market share was generated by :

bysort ye ma: egen tsales=total(qu)
gen share=qu/tsales

where qu is quantity sold of product in a market -year interaction.

clear
input byte(ye ma) int co byte org float(qu share)
70 1 411 2  1100   .004219555
70 1 241 6   544  .0020867616
70 1 499 7  1880   .007211603
70 1 196 1  8600    .03298925
70 1 413 2  1000   .003835959
70 1 488 2  5400    .02071418
70 1 212 1  7150   .027427107
70 1 134 2  6660    .02554749
70 1  64 3  7800    .02992048
70 1 410 2  2100   .008055514
70 1 500 7   690   .002646812
70 1 400 3   800   .003068767
70 1 439 2  2500   .009589897
70 1 435 3  7200   .027618906
70 1 429 3  4300   .016494624
70 1  36 1  6700   .025700925
70 1 458 3   800   .003068767
70 1 481 2   380  .0014576644
70 1 434 3  1100   .004219555
70 1 217 7  7350     .0281943
70 1 530 7   350  .0013425857
70 1 437 2  7600    .02915329
70 1 269 4  9350   .035866216
70 1 408 2  1350   .005178545
70 1  15 2  2700    .01035709
70 1 412 2  2200    .00843911
70 1 172 2 11000    .04219555
70 1 447 4   560   .002148137
70 1 478 2   150 .00057539385
70 1 455 3   220   .000843911
70 1 402 3  1200  .0046031508
70 1 431 3  2300   .008822706
70 1 430 3  3300   .012658665
70 1 417 1  3700    .01419305
70 1 419 1  1300   .004986747
70 1  26 1  3500   .013425857
70 1 497 1  6350    .02435834
70 1 491 1  6000   .023015754
70 1 174 2  1500   .005753939
70 1 418 1  4650    .01783721
70 1 422 8  7475   .028673794
70 1 521 1 14100    .05408702
70 1 503 7   250  .0009589898
70 1 407 2  2200    .00843911
70 1 406 2  3000   .011507877
70 1 214 1  4450   .017070018
70 1 535 6  2000   .007671918
70 1 524 1  3800   .014576645
70 1 213 1  8000   .030687673
70 1 544 2  5200   .019946987
end

Now, org implies the origin country of the product and ma is the market (second dimension of panel) in which the good is sold. What I want is the cross table where columns are destination or ma (Second dimension of panel) and rows as the origin country (location) and each sell to represent how much percentage of sales in a market comes from a certain country. The column sum should be 100. I tried to use the

tab org ma, summarize(share)

which did not give me the result. In order to confirm that shares are correct, I checked the following command, and it showed that calculation of share was correct.

sum share if ma==1 & ye==71

I think the tab command giving me number of observations and their average across the years. Rather than summing up the market share of all the goods from the same origin in the given year. Any help is much appreciated.

[Fei Wang]

Muhammad, I assume a column in the table represents a year-market combination, not just market (the second variable). So the share would be the fraction of sales from an org within each year-market combination.

bys ye ma: egen tqu = total(qu)
bys ye ma org: egen tqu_org = total(qu)
gen share_org = tqu_org / tqu

table org ma ye, c(mean share_org)

[Muhammad Arslan Iqbal]

Thank you so much Fei. I have tried this and It worked. Also suppose we have another variable x in the same data , and we want to define a variable that is sum of variable x in the market m year y for all the other products. How this should be done?

[Fei Wang]

I assume within each pair of year and market, product code is unique.

bys ye ma: egen x_sum = total(x)
replace x_sum = x_sum - x

[Muhammad Arslan Iqbal]

Thank you so much Fei for the help. I was wondering for the above code

 table org ma ye, c(mean share_org)

It gave me year wise market share. However, what I want is average share in sales of the origin place of product. Hence, I used the following

 table org ma, c(mean share_org)

However, then for a given market =1 suppose sum of shares of origin places becomes slightly more than 1. It is surprising. I think this is because there are many observations of share_org with the same values in the given market year. Hence, when we take average across different years, It matters that how many same values are there in each year. In order to solve this, I need just a unique value for each year market combination for a given origin. Do you think, it can be done easily.

[Fei Wang]

Muhammad, my code, cited by you in #5, follows the code in #2. There, I calculated the sales share from an org within each year-market. If you only want sales share from an org within a market, regardless the year, then code in #2 needs revision.

bys ma: egen tqu = total(qu)
bys ma org: egen tqu_org = total(qu)
gen share_org = tqu_org / tqu

table org ma, c(mean share_org)

[Muhammad Arslan Iqbal]

Thank you so much. Now it is giving the right values. Finally, we have a year variable and market variable. But suppose, we want to define market as country and year combination i.e. country 1 in 2000 is separate market and country 1 in year 2001 is another market. We have two separate columns for year and country. How can we have this required "Market" variable which has different values for each year& market combination. This new variable would serve as market identifier rather than for any calculation.

[Fei Wang]

Thank you so much. Now it is giving the right values. Finally, we have a year variable and market variable. But suppose, we want to define market as country and year combination i.e. country 1 in 2000 is separate market and country 1 in year 2001 is another market. We have two separate columns for year and country. How can we have this required "Market" variable which has different values for each year& market combination. This new variable would serve as market identifier rather than for any calculation.

egen market = group(country year)