Hi everyone!

I have a dataset with three variables: province_code (11 province codes), newnsector (14 sector codes) , r_output. In my Satata file, newnsector is shown as "SA" "SB,..."SP" but when I use command dataex, SA is shown as "1", SB is shown as "2" ... ( I do not know why). Now, I want to create a 11x14 matrix (data set) and the value of the matrix is r_output. The first row of this new matrix is values of r_output of province code 01 in sector order: SA, SB, SC, SD, SE, SF, SG, SH, SK, SL, SM, SN, SO, SP). Similarly, the second row is for province 02 and so on until province code 17. Here, there is a difficult issue. Province code 01 has values for all 14 sectors, but province code 02 and some other provinces do not have r_output of all 14 sectors. Therefore, I need to fill in the values of missing sector codes in those provinces with "0" value to makes sure that I have 11x14 matrix. Now, I am doing manually in excel by exporting the file to excel and then copying, pasting, transposing and fill in the missing sectors with 0 values. It takes a lot of time. I am wondering if Stata has any command for this problem. I tried to do by myself but impossible.

The reason for me to need 11x14 matrix is because I need to multiply this matrix with a 14x14 matrix (input output table). My true data set has 63 provinces and 14 sectors. Can you suggest me the code for the sample data set and show me how to modify for the true dataset of 63 provinces. I really appreciate your help. Thank you so much!


Code:
 Example generated by -dataex-. To install: ssc install dataex
clear
input str2 province_code int newnsector float r_output
"01" 14     .05285969
"01"  3     .00626575
"01" 13     .17437804
"01"  9     .09589774
"01"  4     .05790508
"01"  6   .0009028614
"01"  7      .0517438
"01"  8     .05596996
"01" 11     .04816558
"01"  5     .15201323
"01" 10     .05520221
"01"  2      .0496197
"01"  1     .02801064
"01" 12       .159773
"02"  9   .0003512017
"02"  5    .000280617
"02" 12  7.206216e-06
"02"  4   .0023777916
"02" 14  5.672023e-06
"02"  7  1.238403e-06
"02"  1 .000064757674
"02" 13   .0001931883
"02"  2 3.4551356e-06
"02" 10   .0001658643
"04"  5  .00004467173
"04"  1   .0002179262
"04"  9    .000660103
"04" 14 9.0871945e-06
"04"  4   .0006858561
"04" 10   .0030890896
"06" 10   .0006495666
"06" 14   4.08849e-06
"06"  5  .00023494294
"06"  9  .00027400945
"06"  2 .000032480897
"06" 12  9.435477e-08
"06"  7  2.440203e-09
"06"  4  .00018620113
"06"  1 .000015656562
"08"  5     .01765398
"08"  2   .0006631739
"08"  4    .006475695
"08"  8  3.717457e-06
"08"  7 .000011507996
"08" 14  1.726268e-06
"08" 10    .003837108
"08"  1   .0005638853
"08"  9    .003737794
"10" 14  6.785504e-06
"10" 12  .00039115755
"10"  1   .0003236186
"10"  4   .0019831005
"10"  2  1.968738e-07
"10" 10    .000279764
"10"  9   .0006842943
"10"  5   .0001658596
"10"  7     .03733616
"11"  9   .0013166956
"11" 14  .00008154935
"11" 10  .00006602735
"11"  1  .00001089103
"11"  5  .00005325684
"11"  4  .00007391685
"12"  4   .0004276246
"12" 10 .000023588744
"12"  1 1.3328968e-06
"12"  5  5.277554e-06
"12"  8  6.246046e-06
"12"  9  .00018962457
"12" 14 1.3077788e-07
"14"  5    .000132034
"14" 12 .000013313354
"14" 10  .00004675482
"14" 14  9.341277e-08
"14"  1   .0027883044
"14"  9   .0024881726
"14"  7  .00022676193
"14"  4  .00009249443
"14"  2  .00008890821
"15" 10  .00013244175
"15"  7   .0003468687
"15"  2   .0005966048
"15"  1   .0002936966
"15"  8  .00008438806
"15" 12  .00007266354
"15"  9    .013388124
"15" 14 .000016993652
"15"  4    .003852513
"15"  5    .002465888
"17"  3   .0005053952
"17" 12  .00006140318
"17" 14 .000034275014
"17"  1   .0008872058
"17"  7   .0007581466
"17" 10   .0011180259
"17"  5  .00037424185
"17"  8  .00012182483
"17"  2    .003683278
"17"  4    .013263932
"17" 13   .0009010206
end
label values newnsector nsector
label def nsector 1 "SA", modify
label def nsector 2 "SB", modify
label def nsector 3 "SC", modify
label def nsector 4 "SD", modify
label def nsector 5 "SE", modify
label def nsector 6 "SF", modify
label def nsector 7 "SG", modify
label def nsector 8 "SH", modify
label def nsector 9 "SK", modify
label def nsector 10 "SL", modify
label def nsector 11 "SM", modify
label def nsector 12 "SN", modify
label def nsector 13 "SO", modify
label def nsector 14 "SP", modify