Seeking guidance on data merge and append

[Mohammad Rayhan Miah]

Hello everyone,
Greetings!! I am Rayhan.
I have 26 monthly data sets of exports and imports. Twenty-four files contain export data for 2 years, and two files contain import data for 2 years. I want to combine all 24 export files into one file and 2 import files into one. Then, I want to combine these 2 again. All the export data sets have the same name variables and import data sets too.

I have tried to merge in many ways. However, 1:1. 1:m, m:1 does not work because it says the variable firm, bin (business identification number), and year do not uniquely identify observations in the master data. Only m:m works, is it okay? I have also tried to append my data sets but there are a huge number of observations that show an error message if the disk is full.

I am looking for your kind guidance on how to merge or append my data to make a panel data. I have uploaded an example of my data set using dataex.
Thank you.
Rayhan

* Example generated by -dataex-. For more info, type help dataex
clear
input str50 firm str14 bin int date long hscode double exportvalue str35 countryname double    quantity    byte    portid
"1 & 9 APPAREL CREATOR LTD." "847665"    21608 61091000     18773 "NORWAY"         11600  1
"5 F APPARELS LTD."          "171510036" 21583 61034200    8915.9 "ITALY"           3460  1
"5 F APPARELS LTD."          "171510036" 21583 61091000   6601.03 "ITALY"           2610  1
"5 F APPARELS LTD."          "171510036" 21584 61051000    101807 "SPAIN"          20255  1
"5 F APPARELS LTD."          "171510036" 21585 61034200  21298.13 "ITALY"          11158  1
"5 F APPARELS LTD."          "171510036" 21585 61051000  47996.54 "U.K."           16104  1
"5 F APPARELS LTD."          "171510036" 21587 61102000     21077 "AUSTRALIA"       3011  8
"5 F APPARELS LTD."          "171510036" 21588 61091000  19393.17 "ITALY"          12700  1
"5 F APPARELS LTD."          "171510036" 21588 62071100     23597 "GERMANY"        29268  1
"5 F APPARELS LTD."          "171510036" 21588 61091000  14395.61 "ITALY"          10774  1
"5 F APPARELS LTD."          "171510036" 21588 61091000  20333.81 "ITALY"          13316  1
"5 F APPARELS LTD."          "171510036" 21588 61034200   5407.38 "ITALY"           1988  1
"5 F APPARELS LTD."          "171510036" 21588 61091000  20627.01 "ITALY"          13508  1
"5 F APPARELS LTD."          "171510036" 21589 61102000  46083.74 "ITALY"          26004  8
"5 F APPARELS LTD."          "171510036" 21589 61051000 103466.37 "U.K."           36000  1
"5 F APPARELS LTD."          "171510036" 21589 61051000  33770.27 "U.K."           12000  1
"5 F APPARELS LTD."          "171510036" 21590 61051000     42205 "SPAIN"           6764  1
"5 F APPARELS LTD."          "171510036" 21590 61102000  43579.66 "ITALY"          24591  8
"5 F APPARELS LTD."          "171510036" 21591 61102000     49512 "SPAIN"           8446  8
"5 F APPARELS LTD."          "171510036" 21592 61091000  19393.17 "ITALY"          12700  1
"5 F APPARELS LTD."          "171510036" 21592 61102000   83309.8 "AUSTRALIA"      10628  1
"5 F APPARELS LTD."          "171510036" 21592 61034200  18164.86 "ITALY"           2715  1
"5 F APPARELS LTD."          "171510036" 21592 61091000   9932.53 "ITALY"           2692  1
"5 F APPARELS LTD."          "171510036" 21592 61091000  14395.61 "ITALY"          10774  1
"5 F APPARELS LTD."          "171510036" 21595 61091000  10683.64 "ITALY"           4432  1
"5 F APPARELS LTD."          "171510036" 21597 61091000   4239.46 "BRAZIL"          2960  1
"5 F APPARELS LTD."          "171510036" 21597 61091000   4804.46 "BRAZIL"          3040  1
"5 F APPARELS LTD."          "171510036" 21597 61091000   5926.56 "BRAZIL"          3000  1
"5 F APPARELS LTD."          "171510036" 21597 61091000  30589.91 "BRAZIL"         16740  1
"5 F APPARELS LTD."          "171510036" 21597 61091000   7080.26 "BRAZIL"          4480  1
"5 F APPARELS LTD."          "171510036" 21597 61091000  12828.03 "BRAZIL"          7020  1
"5 F APPARELS LTD."          "171510036" 21597 61091000   1643.63 "BRAZIL"          1040  1
"5 F APPARELS LTD."          "171510036" 21597 61091000     10886 "GERMANY"         6182  1
"5 F APPARELS LTD."          "171510036" 21597 61091000  15569.06 "BRAZIL"          8520  1
"5 F APPARELS LTD."          "171510036" 21598 61091000     76600 "U.S.A."         60444  1
"5 F APPARELS LTD."          "171510036" 21598 61091000   5738.49 "NEW ZEALAND"     5008  1
"5 F APPARELS LTD."          "171510036" 21600 61046200   7282.47 "ITALY"           1110  8
"5 F APPARELS LTD."          "171510036" 21600 61034200   33121.2 "AUSTRALIA"       5672  1
"5 F APPARELS LTD."          "171510036" 21603 61051000 177456.33 "U.K."           63060  1
"5 F APPARELS LTD."          "171510036" 21604 61091000     52501 "ITALY"          28000  1
"5 F APPARELS LTD."          "171510036" 21604 61034200   26931.9 "AUSTRALIA"       4614  1
"5 F APPARELS LTD."          "171510036" 21608 61091000  19493.11 "ITALY"          13480  1
"5 F APPARELS LTD."          "171510036" 21608 61091000   4616.22 "AUSTRALIA"       4000  1
"5 F APPARELS LTD."          "171510036" 21608 61091000  13897.03 "AUSTRALIA"       7692  1
"5 F APPARELS LTD."          "171510036" 21608 61091000  44625.95 "ITALY"          24589  1
"5S SPORTS WEAR LTD"         "1201332"   21588 62041000    7611.9 "GERMANY"         7174  1
"7 MM NUR-E-MANZIL"          "1143774"   21586 62171000    3260.9 "BANGLADESH"     82015 10
"7 MM NUR-E-MANZIL"          "1143774"   21586 62171000      3780 "BANGLADESH"      4000  1
"A & A TRADE INTERNATIONAL"  "628675"    21583 62034200     11000 "INDIA"          40280  3
"A & A TRADE INTERNATIONAL"  "628675"    21583 62034200     11000 "INDIA"          28296  3
"A & A TRADE INTERNATIONAL"  "628675"    21583 62034200     11000 "INDIA"          39125  3
"A & A TRADE INTERNATIONAL"  "628675"    21583 62034200     11000 "INDIA"          24124  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          36832  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          29383  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          27632  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          35050  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          33213  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          31020  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          35528  3
"A & A TRADE INTERNATIONAL"  "628675"    21590 62034200     11000 "INDIA"          29115  3
"A & A TROUSERS LTD."        "181010178" 21581 62046200    135630 "U.K."           22980  1
"A & A TROUSERS LTD."        "181010178" 21581 62034200       694 "SINGAPORE"         82  1
"A & A TROUSERS LTD."        "181010178" 21582 62046200     92788 "GERMANY"        15370  1
"A & A TROUSERS LTD."        "181010178" 21582 62046200     52099 "CZECH REPUBLIC"  8630  1
"A & A TROUSERS LTD."        "181010178" 21583 62034200    115779 "SPAIN"          22239  8
"A & A TROUSERS LTD."        "181010178" 21587 62034200    122825 "GERMANY"        17472  1
"A & A TROUSERS LTD."        "181010178" 21588 62046200     90642 "U.K."           14990  1
"A & A TROUSERS LTD."        "181010178" 21588 62034200       841 "SINGAPORE"        108  1
"A & A TROUSERS LTD."        "181010178" 21588 62034200     54149 "U.K."            6433  1
"A & A TROUSERS LTD."        "181010178" 21588 62034200      2893 "U.K."             338  1
"A & A TROUSERS LTD."        "181010178" 21589 62046200     18700 "U.K."            2000  1
"A & A TROUSERS LTD."        "181010178" 21591 62046200     60643 "U.S.A."         14448  1
"A & A TROUSERS LTD."        "181010178" 21591 62046200    150022 "U.S.A."         35586  1
"A & A TROUSERS LTD."        "181010178" 21591 62046200     25230 "U.S.A."         20448  1
"A & A TROUSERS LTD."        "181010178" 21593 62046200     96036 "GERMANY"        17912  1
"A & A TROUSERS LTD."        "181010178" 21595 62046200     83906 "U.K."           14170  1
"A & A TROUSERS LTD."        "181010178" 21595 62046200     99346 "U.K."           16830  1
"A & A TROUSERS LTD."        "181010178" 21596 62046200     29922 "U.K."            3604  1
"A & A TROUSERS LTD."        "181010178" 21596 62034200     20582 "U.K."            2474  1
"A & A TROUSERS LTD."        "181010178" 21596 62034200     41621 "U.K."            5190  1
"A & A TROUSERS LTD."        "181010178" 21596 62034200     28011 "U.K."            4000  1
"A & A TROUSERS LTD."        "181010178" 21597 62034200      2761 "SINGAPORE"        346  1
"A & A TROUSERS LTD."        "181010178" 21597 62034200      3834 "SINGAPORE"        448  1
"A & A TROUSERS LTD."        "181010178" 21597 62034200      2110 "U.K."             235  1
"A & A TROUSERS LTD."        "181010178" 21601 62034200     71501 "U.K."            8143  1
"A & A TROUSERS LTD."        "181010178" 21606 62046200    145062 "GERMANY"        19032  1
"A & A TROUSERS LTD."        "181010178" 21606 62046200    126258 "GERMANY"        19582  1
"A & A TROUSERS LTD."        "181010178" 21606 62046200    107057 "U.S.A."         26712  1
"A & A TROUSERS LTD."        "181010178" 21606 62046200     50617 "U.S.A."         12552  1
"A & A TROUSERS LTD."        "181010178" 21608 62046200     23490 "U.K."            3000  1
"A & A TROUSERS LTD."        "181010178" 21608 62046200     22033 "U.K."            3010  1
"A & A TROUSERS LTD."        "181010178" 21608 62046200     19999 "U.K."            2801  1
"A & B OUTERWEAR LIMITED"    "244877"    21583 62111100      1350 "MEXICO"          1500  1
"A & B OUTERWEAR LIMITED"    "244877"    21584 62111100    8477.6 "SPAIN"          10597  1
"A & B OUTERWEAR LIMITED"    "244877"    21586 62111100    1670.4 "MEXICO"          2088  1
"A & B OUTERWEAR LIMITED"    "244877"    21588 62111100    7572.6 "SPAIN"           8414  1
"A & B OUTERWEAR LIMITED"    "244877"    21593 62111100   11500.8 "SPAIN"           9584  1
"A & B OUTERWEAR LIMITED"    "244877"    21594 62111100       400 "MEXICO"           500  1
"A & B OUTERWEAR LIMITED"    "244877"    21594 62111100       400 "MEXICO"           500  1
"A & B OUTERWEAR LIMITED"    "244877"    21596 62111100    3604.8 "SPAIN"           4506  1
end
format %tddd-Mon-YY date

[Emil Alnor]

You should use -append to "combine" the export files into one. Assuming all 24 export files have the same variables this should be fairly easy. You did not write how you tried to do it or how the other export files look, but if you provide another example of an export file, it would be easier for other to help you write the code to append these two files.

Regarding the merging of the import and export files, I am not sure, why you would want to do that. What is the unit of analysis (what do each row constitute) in the export file? Looking at your data it does not look as if it is company-day exports, as there for some companys for some days are more than one entry. If each row constitute a, say, export agreement of that company to a company in a different country, I can't see a meaningful way to merge the import and export files. One thing you could do was to -collapse the export data set into company-day observations, do the same with the import dataset (assuming it has a similar structure), and then do a 1:1 merge by company date. But it depends on what the goal of your analysis is.

[Mohammad Rayhan Miah]

Hi,
Thank you for your reply.
All 24 export files have the same variables. I tried to append 24 files to combine into one but the number of observations becomes too high that I can't run any command further. I tried the command: use "jan19.dta", clear
append using "feb19.dta"
My unit of analysis is firm. Each row in export files represents a firm. It's daily data in a monthly file. Some firm has more than one entry on many dates. The import dataset also has the same structure. Here is an example of another export file.

* Example generated by -dataex-. For more info, type help dataex
clear
input str50 firm int date long hscode double exportvalue str35 countryname
"1 & 9 APPAREL CREATOR LTD." 21608 61091000     18773 "NORWAY"        
"5 F APPARELS LTD."          21583 61034200    8915.9 "ITALY"         
"5 F APPARELS LTD."          21583 61091000   6601.03 "ITALY"         
"5 F APPARELS LTD."          21584 61051000    101807 "SPAIN"         
"5 F APPARELS LTD."          21585 61034200  21298.13 "ITALY"         
"5 F APPARELS LTD."          21585 61051000  47996.54 "U.K."          
"5 F APPARELS LTD."          21587 61102000     21077 "AUSTRALIA"     
"5 F APPARELS LTD."          21588 61091000  19393.17 "ITALY"         
"5 F APPARELS LTD."          21588 62071100     23597 "GERMANY"       
"5 F APPARELS LTD."          21588 61091000  14395.61 "ITALY"         
"5 F APPARELS LTD."          21588 61091000  20333.81 "ITALY"         
"5 F APPARELS LTD."          21588 61034200   5407.38 "ITALY"         
"5 F APPARELS LTD."          21588 61091000  20627.01 "ITALY"         
"5 F APPARELS LTD."          21589 61102000  46083.74 "ITALY"         
"5 F APPARELS LTD."          21589 61051000 103466.37 "U.K."          
"5 F APPARELS LTD."          21589 61051000  33770.27 "U.K."          
"5 F APPARELS LTD."          21590 61051000     42205 "SPAIN"         
"5 F APPARELS LTD."          21590 61102000  43579.66 "ITALY"         
"5 F APPARELS LTD."          21591 61102000     49512 "SPAIN"         
"5 F APPARELS LTD."          21592 61091000  19393.17 "ITALY"         
"5 F APPARELS LTD."          21592 61102000   83309.8 "AUSTRALIA"     
"5 F APPARELS LTD."          21592 61034200  18164.86 "ITALY"         
"5 F APPARELS LTD."          21592 61091000   9932.53 "ITALY"         
"5 F APPARELS LTD."          21592 61091000  14395.61 "ITALY"         
"5 F APPARELS LTD."          21595 61091000  10683.64 "ITALY"         
"5 F APPARELS LTD."          21597 61091000   4239.46 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000   4804.46 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000   5926.56 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000  30589.91 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000   7080.26 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000  12828.03 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000   1643.63 "BRAZIL"        
"5 F APPARELS LTD."          21597 61091000     10886 "GERMANY"       
"5 F APPARELS LTD."          21597 61091000  15569.06 "BRAZIL"        
"5 F APPARELS LTD."          21598 61091000     76600 "U.S.A."        
"5 F APPARELS LTD."          21598 61091000   5738.49 "NEW ZEALAND"   
"5 F APPARELS LTD."          21600 61046200   7282.47 "ITALY"         
"5 F APPARELS LTD."          21600 61034200   33121.2 "AUSTRALIA"     
"5 F APPARELS LTD."          21603 61051000 177456.33 "U.K."          
"5 F APPARELS LTD."          21604 61091000     52501 "ITALY"         
"5 F APPARELS LTD."          21604 61034200   26931.9 "AUSTRALIA"     
"5 F APPARELS LTD."          21608 61091000  19493.11 "ITALY"         
"5 F APPARELS LTD."          21608 61091000   4616.22 "AUSTRALIA"     
"5 F APPARELS LTD."          21608 61091000  13897.03 "AUSTRALIA"     
"5 F APPARELS LTD."          21608 61091000  44625.95 "ITALY"         
"5S SPORTS WEAR LTD"         21588 62041000    7611.9 "GERMANY"       
"7 MM NUR-E-MANZIL"          21586 62171000    3260.9 "BANGLADESH"    
"7 MM NUR-E-MANZIL"          21586 62171000      3780 "BANGLADESH"    
"A & A TRADE INTERNATIONAL"  21583 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21583 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21583 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21583 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TRADE INTERNATIONAL"  21590 62034200     11000 "INDIA"         
"A & A TROUSERS LTD."        21581 62046200    135630 "U.K."          
"A & A TROUSERS LTD."        21581 62034200       694 "SINGAPORE"     
"A & A TROUSERS LTD."        21582 62046200     92788 "GERMANY"       
"A & A TROUSERS LTD."        21582 62046200     52099 "CZECH REPUBLIC"
"A & A TROUSERS LTD."        21583 62034200    115779 "SPAIN"         
"A & A TROUSERS LTD."        21587 62034200    122825 "GERMANY"       
"A & A TROUSERS LTD."        21588 62046200     90642 "U.K."          
"A & A TROUSERS LTD."        21588 62034200       841 "SINGAPORE"     
"A & A TROUSERS LTD."        21588 62034200     54149 "U.K."          
"A & A TROUSERS LTD."        21588 62034200      2893 "U.K."          
"A & A TROUSERS LTD."        21589 62046200     18700 "U.K."          
"A & A TROUSERS LTD."        21591 62046200     60643 "U.S.A."        
"A & A TROUSERS LTD."        21591 62046200    150022 "U.S.A."        
"A & A TROUSERS LTD."        21591 62046200     25230 "U.S.A."        
"A & A TROUSERS LTD."        21593 62046200     96036 "GERMANY"       
"A & A TROUSERS LTD."        21595 62046200     83906 "U.K."          
"A & A TROUSERS LTD."        21595 62046200     99346 "U.K."          
"A & A TROUSERS LTD."        21596 62046200     29922 "U.K."          
"A & A TROUSERS LTD."        21596 62034200     20582 "U.K."          
"A & A TROUSERS LTD."        21596 62034200     41621 "U.K."          
"A & A TROUSERS LTD."        21596 62034200     28011 "U.K."          
"A & A TROUSERS LTD."        21597 62034200      2761 "SINGAPORE"     
"A & A TROUSERS LTD."        21597 62034200      3834 "SINGAPORE"     
"A & A TROUSERS LTD."        21597 62034200      2110 "U.K."          
"A & A TROUSERS LTD."        21601 62034200     71501 "U.K."          
"A & A TROUSERS LTD."        21606 62046200    145062 "GERMANY"       
"A & A TROUSERS LTD."        21606 62046200    126258 "GERMANY"       
"A & A TROUSERS LTD."        21606 62046200    107057 "U.S.A."        
"A & A TROUSERS LTD."        21606 62046200     50617 "U.S.A."        
"A & A TROUSERS LTD."        21608 62046200     23490 "U.K."          
"A & A TROUSERS LTD."        21608 62046200     22033 "U.K."          
"A & A TROUSERS LTD."        21608 62046200     19999 "U.K."          
"A & B OUTERWEAR LIMITED"    21583 62111100      1350 "MEXICO"        
"A & B OUTERWEAR LIMITED"    21584 62111100    8477.6 "SPAIN"         
"A & B OUTERWEAR LIMITED"    21586 62111100    1670.4 "MEXICO"        
"A & B OUTERWEAR LIMITED"    21588 62111100    7572.6 "SPAIN"         
"A & B OUTERWEAR LIMITED"    21593 62111100   11500.8 "SPAIN"         
"A & B OUTERWEAR LIMITED"    21594 62111100       400 "MEXICO"        
"A & B OUTERWEAR LIMITED"    21594 62111100       400 "MEXICO"        
"A & B OUTERWEAR LIMITED"    21596 62111100    3604.8 "SPAIN"         
end
format %tddd-Mon-YY date

[Mohammad Rayhan Miah]

Hello everyone,
Could you please help me?
About appending? or merging?

I want to make weekly panel data after combining export and import files.
I am seeking your kind guidance on this please.

With best regards,
Rayhan

@Emil Alnor

[Mohammad Rayhan Miah]

Hello everyone,
Greetings!!

Could you please help me how to append or merge my 24 export files and 2 import files?

And, how to make weekly panel data after combining export and import files??

Your kind guidance is highly appreciated.

With best regards,
Rayhan

@Emil Alnor
@Clyde Schechter sir!

[Emil Alnor]

Mohammad Rayhan Miah

Regarding the appending: In general you are adviced to write both what you write in Stata and what Stata returned. I cannot help you a lot here since you did not do this, but a likely error is that your command directory is not set to where you other files are located.

Regarding the unit of analysis: this is not - as you write - firms in your current data. I would advice you to get a better basic understanding of what panel data is and how it can be described, before seeking further guidance on this question. What it seems your are confusing are the concepts 'unit of analysis' 'variable' and 'value of variable'. After you gotten a better understanding of concepts in panel data, try to finish this sentence "Each row of my data constitute a unique [UNIT OF ANALYSIS]". 'firm' should not be in the brackets here, as there are multiple rows which have the same value on the variable 'firm' - therefore each row does not constitute a unique firm.

Good luck with your project.