Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Merge data

    Dear all,

    I have quarterly panel data. I want to merge external data related to a factor variable in the panel data.
    This is the panel data:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter WEEK Inde07m FLEXW7 FTPT SEX)
9311094030103 4 10 5 1 2 2
4230193290101 4  1 5 1 1 2
1181393020101 4 13 5 1 2 2
1151093050101 4 10 3 1 1 1
 661193020101 4 11 8 1 1 1
9090791010103 4  7 5 1 1 1
7970793020101 4  7 8 1 2 2
 261391010102 4 13 8 1 2 2
1140193040101 4  1 7 1 2 1
2710693020101 4  6 5 1 1 1
2261291010104 4 12 6 1 2 2
4230294810101 4  2 8 1 1 2
1730191030101 4  1 5 1 2 2
1401194030101 4 11 8 1 2 2
2130193020102 4  1 3 1 1 2
2160593050101 4  5 9 1 2 2
1041091010101 4 10 8 1 2 2
 860793030101 4  7 9 1 1 2
1810393060101 4  3 8 1 2 2
3020193040104 4  1 5 1 1 2
1310693010101 4  6 8 1 2 2
1401291010101 4 12 8 1 2 2
 751394020103 4 13 8 1 2 2
4231091250101 4 10 7 1 2 2
4231393710102 4 13 6 1 1 1
3021293020101 4 12 8 1 2 2
4230594570102 4  5 5 1 2 2
2330894010102 4  8 8 1 1 2
2020293040102 4  2 8 1 2 2
2161093020101 4 10 3 1 2 1
1001193010101 4 11 9 1 2 2
1460891040101 4  8 7 1 1 1
  21391010101 4 13 5 1 1 2
2140991040102 4  9 5 1 2 2
1620193010101 4  1 8 1 2 1
 880393050101 4  3 8 1 2 1
 710493030102 4  4 8 1 2 2
4231094200102 4 10 5 1 1 2
2850394040102 4  3 8 1 2 2
 650293050102 4  2 7 1 1 1
9081393010102 4 13 4 1 1 1
3121393010101 4 13 4 1 1 1
7760993010101 4  9 8 1 1 2
1061291020101 4 12 8 1 2 1
 751394020105 4 13 8 1 2 1
2840593030103 4  5 6 1 1 2
2060393030102 4  3 6 1 2 1
3020293010103 4  2 5 1 2 1
 101091030102 4 10 5 1 1 1
2550393040101 4  3 4 1 1 1
9340991010102 4  9 6 1 2 1
2190491010101 4  4 3 1 2 2
2840491030103 4  4 8 1 2 2
3090494010101 4  4 8 1 2 1
7731291010102 4 12 8 1 2 2
 641093010101 4 10 6 1 1 1
 121294040101 4 12 5 1 2 1
 980691030103 4  6 9 1 2 2
 910793010101 4  7 8 1 2 2
 850791040101 4  7 6 1 1 2
7570893010101 4  8 7 1 2 1
1601091040103 4 10 5 1 2 1
1530593060102 4  5 5 1 2 2
  40593010104 4  5 7 1 2 2
 880991010102 4  9 7 1 1 2
9081393010101 4 13 7 1 1 2
1601091040102 4 10 8 1 1 2
1560793020101 4  7 8 1 2 2
1581191010102 4 11 5 1 2 2
1071394030101 4 13 8 1 2 2
4230193620102 4  1 6 1 2 2
1170891040101 4  8 7 1 2 1
 340291010101 4  2 8 1 2 2
 330894040102 4  8 8 1 2 2
1140193050103 4  1 8 1 2 1
3090293020101 4  2 8 1 2 2
9180394010101 4  3 7 1 1 1
 160593020101 4  5 8 1 2 2
9360193020101 4  1 8 1 2 2
1741093010101 4 10 8 1 2 2
 951191110101 4 11 5 1 2 1
1461094030102 4 10 9 1 2 1
 100191010103 4  1 5 1 2 2
1951394010101 4 13 9 1 2 1
 310293030101 4  2 8 1 2 2
1210791010103 4  7 7 1 2 1
 341094010103 4 10 9 1 2 1
1270591020101 4  5 8 1 2 2
9280491010101 4  4 8 1 2 1
 540691030102 4  6 1 1 1 1
7821393020103 4 13 3 1 2 1
 270491040101 4  4 7 1 2 1
2141193020101 4 11 5 1 1 1
1450691020104 4  6 5 1 2 1
4230591290102 4  5 9 1 2 1
 230991010102 4  9 7 1 2 2
1611391010101 4 13 5 1 1 2
 150593020104 4  5 5 1 2 2
4231193360103 4 11 5 1 2 2
2661093010101 4 10 8 1 1 2
end
label values quarter quarter
label def quarter 4 "Oct-Des 2019", modify
label values WEEK WEEK
label values Inde07m Inde07m5
label def Inde07m5 1 "A - Agriculture, forestry and fishing", modify
label def Inde07m5 3 "C -Manufacturing", modify
label def Inde07m5 4 "F - Construction", modify
label def Inde07m5 5 "G,I -Distribution, hotels and restaurants", modify
label def Inde07m5 6 "H,J -Transport and communication", modify
label def Inde07m5 7 "K,L,M,N - Banking and finance", modify
label def Inde07m5 8 "O,P,Q - Public admin, education and health", modify
label def Inde07m5 9 "R,S,T,U - Other services", modify
label values FLEXW7 FLEXW75
label def FLEXW75 1 "Yes", modify
label values FTPT FTPT5
label def FTPT5 1 "Full time", modify
label def FTPT5 2 "Part time", modify
label values SEX SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify

    it contains the time variable "quarter" and also the week variable in which the week interview was conducted "WEEk" and also the industry sector in the main job variable "Inde07m "but this variable is a factor variable, and it contains all industry (for example manufacturing, accommodation … ..etc.) its range from 1 to 9, in other words, manufacturing industry takes the value "1", the accommodation industry takes the value "2" ... etc.


    The external data is:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str24 A str68 B str19 C str33 D str52 E
"date "                    "Industry"                                                             "Continuing to trade" "Has permanently ceased trading **" "Has temporarily closed or temporarily paused trading"
"06/04/2020 to 19/04/2021" "Manufacturing"                                                        ".772"                ".002"                              ".227"                                                
"06/04/2020 to 19/04/2021" "Water Supply, Sewerage, Waste Management And Remediation Activities"  ".92"                 "0"                                 ".08"                                                 
"06/04/2020 to 19/04/2022" "Construction"                                                         ".709"                "0"                                 ".291"                                                
"06/04/2020 to 19/04/2023" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" ".728"                ".002"                              ".27"                                                 
"06/04/2020 to 19/04/2024" "Accommodation And Food Service Activities"                            ".184"                ".004"                              ".8120000000000001"                                   
"06/04/2020 to 19/04/2025" "Transportation And Storage"                                           ".923"                "0"                                 ".077"                                                
"06/04/2020 to 19/04/2026" "Information And Communication"                                        ".944"                ".005"                              ".051"                                                
"06/04/2020 to 19/04/2027" "Professional, Scientific And Technical Activities"                    ".962"                ".001"                              ".037"                                                
"06/04/2020 to 19/04/2028" "Administrative And Support Service Activities"                        ".897"                ".007"                              ".096"                                                
"06/04/2020 to 19/04/2029" "Education"                                                            ".858"                ".003"                              ".138"                                                
"06/04/2020 to 19/04/2030" "Human Health And Social Work Activities"                              ".956"                ".009"                              ".035"                                                
"06/04/2020 to 19/04/2031" "Arts, Entertainment And Recreation"                                   ".167"                ".011"                              ".822"                                                
"06/04/2020 to 19/04/2032" "All Industries"                                                       ".754"                ".003"                              ".243"                                                
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        ".79"                 "*"                                 ".206"                                                
"06/04/2020 to 19/04/2021" "Water Supply, Sewerage, Waste Management And Remediation Activities"  ".9"                  "*"                                 ".1"                                                  
"06/04/2020 to 19/04/2022" "Construction"                                                         ".732"                "*"                                 ".261"                                                
"06/04/2020 to 19/04/2023" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" ".752"                "*"                                 ".243"                                                
"06/04/2020 to 19/04/2024" "Accommodation And Food Service Activities"                            ".182"                ".012"                              ".806"                                                
"06/04/2020 to 19/04/2025" "Transportation And Storage"                                           ".915"                "*"                                 ".085"                                                
"06/04/2020 to 19/04/2026" "Information And Communication"                                        ".95"                 "*"                                 ".045"                                                
"06/04/2020 to 19/04/2027" "Professional, Scientific And Technical Activities"                    ".967"                "*"                                 ".03"                                                 
"06/04/2020 to 19/04/2028" "Administrative And Support Service Activities"                        ".915"                "*"                                 ".081"                                                
"06/04/2020 to 19/04/2029" "Education"                                                            ".868"                "*"                                 ".126"                                                
"06/04/2020 to 19/04/2030" "Human Health And Social Work Activities"                              ".9379999999999999"   ".014"                              ".049"                                                
"06/04/2020 to 19/04/2031" "Arts, Entertainment And Recreation"                                   ".205"                "*"                                 ".795"                                                
"06/04/2020 to 19/04/2032" "All Industries"                                                       ".767"                "*"                                 ".228"                                                
"20/04/2020 - 03/05/2020"  "Manufacturing"                                                        ".859"                "*"                                 ".139"                                                
"20/04/2020 - 03/05/2021"  "Water Supply, Sewerage, Waste Management And Remediation Activities"  ".971"                "*"                                 ".029"                                                
"20/04/2020 - 03/05/2022"  "Construction"                                                         ".781"                "*"                                 ".216"                                                
"20/04/2020 - 03/05/2023"  "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" ".799"                "*"                                 ".2"                                                  
"20/04/2020 - 03/05/2024"  "Transportation And Storage"                                           ".95"                 "*"                                 ".05"                                                 
"20/04/2020 - 03/05/2025"  "Accommodation And Food Service Activities"                            ".219"                "*"                                 ".781"                                                
"20/04/2020 - 03/05/2026"  "Information And Communication"                                        ".95"                 "*"                                 ".045"                                                
"20/04/2020 - 03/05/2027"  "Real Estate Activities"                                               ".93"                 ".014"                              ".056"                                                
"20/04/2020 - 03/05/2028"  "Professional, Scientific And Technical Activities"                    ".955"                ".011"                              ".034"                                                
"20/04/2020 - 03/05/2029"  "Administrative And Support Service Activities"                        ".927"                "*"                                 ".071"                                                
"20/04/2020 - 03/05/2030"  "Education"                                                            ".892"                ".01"                               ".098"                                                
"20/04/2020 - 03/05/2031"  "Human Health And Social Work Activities"                              ".954"                "*"                                 ".038"                                                
"20/04/2020 - 03/05/2032"  "Arts, Entertainment And Recreation"                                   ".196"                "*"                                 ".801"                                                
"20/04/2020 - 03/05/2033"  "All Industries"                                                       ".793"                "*"                                 ".203"                                                
"04/05/2020 - 17/05/2020"  "Manufacturing"                                                        ".923"                "*"                                 ".074"                                                
"04/05/2020 - 17/05/2021"  "Water Supply, Sewerage, Waste Management And Remediation Activities"  ".955"                "*"                                 ".045"                                                
"04/05/2020 - 17/05/2022"  "Construction"                                                         ".804"                "*"                                 ".191"                                                
"04/05/2020 - 17/05/2023"  "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" ".85"                 "*"                                 ".148"                                                
"04/05/2020 - 17/05/2024"  "Transportation And Storage"                                           ".945"                "*"                                 ".055"                                                
"04/05/2020 - 17/05/2025"  "Accommodation And Food Service Activities"                            ".254"                "*"                                 ".741"                                                
"04/05/2020 - 17/05/2026"  "Information And Communication"                                        ".9379999999999999"   "*"                                 ".058"                                                
"04/05/2020 - 17/05/2027"  "Real Estate Activities"                                               ".957"                ".014"                              ".029"                                                
"04/05/2020 - 17/05/2028"  "Professional, Scientific And Technical Activities"                    ".971"                "*"                                 ".026"                                                
"04/05/2020 - 17/05/2029"  "Administrative And Support Service Activities"                        ".911"                "*"                                 ".086"                                                
"04/05/2020 - 17/05/2030"  "Education"                                                            ".903"                "*"                                 ".094"                                                
"04/05/2020 - 17/05/2031"  "Human Health And Social Work Activities"                              ".959"                "*"                                 ".033"                                                
"04/05/2020 - 17/05/2032"  "Arts, Entertainment And Recreation"                                   ".25"                 "*"                                 ".746"                                                
"04/05/2020 - 17/05/2033"  "All Industries"                                                       ".82"                 "*"                                 ".177"                                                
"17/05/2020 - 31/05/2020"  "Manufacturing"                                                        ".951"                "*"                                 ".048"                                                
"17/05/2020 - 31/05/2021"  "Water Supply, Sewerage, Waste Management And Remediation Activities"  "1"                   "0"                                 "0"                                                   
"17/05/2020 - 31/05/2022"  "Construction"                                                         ".826"                "*"                                 ".17"                                                 
"17/05/2020 - 31/05/2023"  "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" ".882"                "*"                                 ".115"                                                
"17/05/2020 - 31/05/2024"  "Transportation And Storage"                                           ".957"                "*"                                 ".04"                                                 
"17/05/2020 - 31/05/2025"  "Accommodation And Food Service Activities"                            ".344"                "*"                                 ".653"                                                
"17/05/2020 - 31/05/2026"  "Information And Communication"                                        ".945"                "*"                                 ".048"                                                
"17/05/2020 - 31/05/2027"  "Real Estate Activities"                                               ".9399999999999999"   ".012"                              ".048"                                                
"17/05/2020 - 31/05/2028"  "Professional, Scientific And Technical Activities"                    ".966"                ".011"                              ".024"                                                
"17/05/2020 - 31/05/2029"  "Administrative And Support Service Activities"                        ".91"                 "*"                                 ".087"                                                
"17/05/2020 - 31/05/2030"  "Education"                                                            ".893"                "*"                                 ".104"                                                
"17/05/2020 - 31/05/2031"  "Human Health And Social Work Activities"                              ".947"                "*"                                 ".045"                                                
"17/05/2020 - 31/05/2032"  "Arts, Entertainment And Recreation"                                   ".299"                "*"                                 ".698"                                                
"17/05/2020 - 31/05/2033"  "All Industries"                                                       ".841"                "*"                                 ".155"                                                
end

    It has six waves of how Covid-19 affected the industry in the labour market (each wave constitutes two weeks) containing divisions of the variable "Inde07m" and coded as a percentage : continuing to trade , or permanently ceased trading ,or temporarily paused trading during the covid-19.

    I want to combine this external data based on time and industry. In other words, I want to get a column in the data after the merge, saying that the manufacturing industry was temporarily paused trading from April 20 to May 3, and so forth.


    I'm a new user of Stata. What is the best way to merge these two datasets? Could we solve this directly from the merge, and how can we do that? Or must you code the data differently before merging?

    I appreciate any contribution to solving this problem.
    Tags: None
  • #2
    I'm sure it can be done. But it isn't going to be easy and there are several obstacles to overcome.

    Your second data set is a mess. First of all, it purports to give you information about events that will be happening as late as May 2033. So something is seriously wrong there--that date variable needs to be fixed. Also, its "Industry" categories do not match the categories in your panel data set. I imagine that with knowledge in this area you can create some kind of crosswalk between the industry categories of the two data sets, but you can't make any progress on combining them until you do that.

    Also, it seems to have been badly imported from a spreadsheet as it variables are named A through E, and the first observation seems to contain, not data, but variable labels. So the import needs to be redone: use the -firstrow- option of -import excel- to fix this particular problem..

    Another problem is that the panel data set, while nicely giving information on quarter and week, does not mention the year. Also, you need to clarify how week is defined in your panel data set. There is no universal standard way of enumerating weeks. Some people would consider the first week of 2023Q2 to be April 1 through April 7. But others would consider it to be April 2 (the first Sunday) through April 8. Still others use April 3 (the first Monday) through April 9. Still others count forward weeks starting from January 1, and would look at the first such week starting on or after April 1. (Since 2023 started on a Sunday, this would be the same as April 2 through April 8--but in other years this could be yet a different reckoning of the weeks.) Yet other complications arise at the end of the quarter. The lengths of quarters are not always multiples of 7 days. So, for example, if a quarter is 92 days (3rd and 4th quarters of any year), there is one "extra" day at the end. Do you treat this as a new, one-day week, or do you count the final day as the 8th day of an extended final week?

    Finally, (after you fix the dates in the second data set), there will likely be weeks in the panel data set that overlap with more than one date range in the second data set, and also date ranges in the second data set that encompass multiple weeks (in whole or in part). You need to decide on what you want to combine with what in these circumstances.

    Until you resolve these questions and fix up the second data set, it is premature to suggest code for merging.
    Last edited by Clyde Schechter; 08 Mar 2023, 10:35.

    Comment

    • #3



      Originally posted by Clyde Schechter View Post
      I'm sure it can be done. But it isn't going to be easy and there are several obstacles to overcome.

      Your second data set is a mess. First of all, it purports to give you information about events that will be happening as late as May 2033. So something is seriously wrong there--that date variable needs to be fixed. Also, its "Industry" categories do not match the categories in your panel data set. I imagine that with knowledge in this area you can create some kind of crosswalk between the industry categories of the two data sets, but you can't make any progress on combining them until you do that.
      Yes, the External data indeed had an error in the time series, and I modified it in the Excel file and exported it to Stata.

      Regarding the industry, I changed the variable in panel data to fit the sequence in both data, but I have three industries that don't exist in the second data (External). So I am not interested in these industries in my analysis. Can we combine it this way by ignoring these three industries?

      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter WEEK SEX Inds07m)
9311094030103 4 10 2  9
4230193290101 4  1 2  7
1181393020101 4 13 2  9
1151093050101 4 10 1  3
 661193020101 4 11 1 17
9090791010103 4  7 1  9
7970793020101 4  7 2 16
 261391010102 4 13 2 17
1140193040101 4  1 1 13
2710693020101 4  6 1  9
2261291010104 4 12 2  8
4230294810101 4  2 2 17
1730191030101 4  1 2  9
1401194030101 4 11 2 16
2130193020102 4  1 2  3
2160593050101 4  5 2 18
1041091010101 4 10 2 16
 860793030101 4  7 2 18
1810393060101 4  3 2 17
3020193040104 4  1 2  9
1310693010101 4  6 2 15
1401291010101 4 12 2 17
 751394020103 4 13 2 16
4231091250101 4 10 2 14
4231393710102 4 13 1 10
3021293020101 4 12 2 16
4230594570102 4  5 2  7
2330894010102 4  8 2 17
2020293040102 4  2 2 16
2161093020101 4 10 1  3
1001193010101 4 11 2 18
1460891040101 4  8 1 14
  21391010101 4 13 2  9
2140991040102 4  9 2  9
1620193010101 4  1 1 17
 880393050101 4  3 1 17
 710493030102 4  4 2 16
4231094200102 4 10 2  9
2850394040102 4  3 2 17
 650293050102 4  2 1 13
9081393010102 4 13 1  6
3121393010101 4 13 1  6
7760993010101 4  9 2 17
1061291020101 4 12 1 15
 751394020105 4 13 1 16
2840593030103 4  5 2 10
2060393030102 4  3 1  8
3020293010103 4  2 1  9
 101091030102 4 10 1  9
2550393040101 4  3 1  6
9340991010102 4  9 1 10
2190491010101 4  4 2  3
2840491030103 4  4 2 16
3090494010101 4  4 1 17
7731291010102 4 12 2 17
 641093010101 4 10 1  8
 121294040101 4 12 1  7
 980691030103 4  6 2 18
 910793010101 4  7 2 17
 850791040101 4  7 2 10
7570893010101 4  8 1 13
1601091040103 4 10 1  7
1530593060102 4  5 2  9
  40593010104 4  5 2 14
 880991010102 4  9 2 13
9081393010101 4 13 2 13
1601091040102 4 10 2 17
1560793020101 4  7 2 17
1581191010102 4 11 2  9
1071394030101 4 13 2 16
4230193620102 4  1 2  8
1170891040101 4  8 1 13
 340291010101 4  2 2 17
 330894040102 4  8 2 17
1140193050103 4  1 1 17
3090293020101 4  2 2 17
9180394010101 4  3 1 14
 160593020101 4  5 2 16
9360193020101 4  1 2 16
1741093010101 4 10 2 17
 951191110101 4 11 1  9
1461094030102 4 10 1 19
 100191010103 4  1 2  9
1951394010101 4 13 1 18
 310293030101 4  2 2 15
1210791010103 4  7 1 14
 341094010103 4 10 1 18
1270591020101 4  5 2 15
9280491010101 4  4 1 17
 540691030102 4  6 1  1
7821393020103 4 13 1  3
 270491040101 4  4 1 11
2141193020101 4 11 1  9
1450691020104 4  6 1  9
4230591290102 4  5 1 19
 230991010102 4  9 2 14
1611391010101 4 13 2  9
 150593020104 4  5 2  9
4231193360103 4 11 2  7
2661093010101 4 10 2 17
end
label values quarter quarter
label def quarter 4 "Oct-Des 2019", modify
label values WEEK WEEK
label values SEX SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify
label values Inds07m Inds07m5
label def Inds07m5 1 "A  Agriculture, forestry and fishing", modify
label def Inds07m5 3 "C  Manufacturing", modify
label def Inds07m5 6 "F  Construction", modify
label def Inds07m5 7 "G  Wholesale, retail, repair of vehicles", modify
label def Inds07m5 8 "H  Transport and storage", modify
label def Inds07m5 9 "I  Accommodation and food services", modify
label def Inds07m5 10 "J  Information and communication", modify
label def Inds07m5 11 "K  Financial and insurance activities", modify
label def Inds07m5 13 "M  Prof, scientific, technical activ.", modify
label def Inds07m5 14 "N  Admin and support services", modify
label def Inds07m5 15 "O  Public admin and defence", modify
label def Inds07m5 16 "P  Education", modify
label def Inds07m5 17 "Q  Health and social work", modify
label def Inds07m5 18 "R  Arts, entertainment and recreation", modify
label def Inds07m5 19 "S  Other service activities", modify



      Originally posted by Clyde Schechter View Post
      Also, it seems to have been badly imported from a spreadsheet as it variables are named A through E, and the first observation seems to contain, not data, but variable labels. So the import needs to be redone: use the -firstrow- option of -import excel- to fix this particular problem.
      I've re-exported. I hope it's OK now.

      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input str24 date str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril
"23/03/2020 to 05/04/2020" "Manufacturing"                                                        "77.2%"  "0.2%" "22.7%"
"23/03/2020 to 05/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "92.0%"  "0.0%" "8.0%" 
"23/03/2020 to 05/04/2020" "Construction"                                                         "70.9%"  "0.0%" "29.1%"
"23/03/2020 to 05/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "72.8%"  "0.2%" "27.0%"
"23/03/2020 to 05/04/2020" "Accommodation And Food Service Activities"                            "18.4%"  "0.4%" "81.2%"
"23/03/2020 to 05/04/2020" "Transportation And Storage"                                           "92.3%"  "0.0%" "7.7%" 
"23/03/2020 to 05/04/2020" "Information And Communication"                                        "94.4%"  "0.5%" "5.1%" 
"23/03/2020 to 05/04/2020" "Professional, Scientific And Technical Activities"                    "96.2%"  "0.1%" "3.7%" 
"23/03/2020 to 05/04/2020" "Administrative And Support Service Activities"                        "89.7%"  "0.7%" "9.6%" 
"23/03/2020 to 05/04/2020" "Education"                                                            "85.8%"  "0.3%" "13.8%"
"23/03/2020 to 05/04/2020" "Human Health And Social Work Activities"                              "95.6%"  "0.9%" "3.5%" 
"23/03/2020 to 05/04/2020" "Arts, Entertainment And Recreation"                                   "16.7%"  "1.1%" "82.2%"
"23/03/2020 to 05/04/2020" "All Industries"                                                       "75.4%"  "0.3%" "24.3%"
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        "79.0%"  "0.4%" "20.6%"
"06/04/2020 to 19/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "90.0%"  "0.0%" "10.0%"
"06/04/2020 to 19/04/2020" "Construction"                                                         "73.2%"  "0.7%" "26.1%"
"06/04/2020 to 19/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "75.2%"  "0.5%" "24.3%"
"06/04/2020 to 19/04/2020" "Accommodation And Food Service Activities"                            "18.2%"  "1.2%" "80.6%"
"06/04/2020 to 19/04/2020" "Transportation And Storage"                                           "91.5%"  "0.0%" "8.5%" 
"06/04/2020 to 19/04/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%" 
"06/04/2020 to 19/04/2020" "Professional, Scientific And Technical Activities"                    "96.7%"  "0.3%" "3.0%" 
"06/04/2020 to 19/04/2020" "Administrative And Support Service Activities"                        "91.5%"  "0.4%" "8.1%" 
"06/04/2020 to 19/04/2020" "Education"                                                            "86.8%"  "0.6%" "12.6%"
"06/04/2020 to 19/04/2020" "Human Health And Social Work Activities"                              "93.8%"  "1.4%" "4.9%" 
"06/04/2020 to 19/04/2020" "Arts, Entertainment And Recreation"                                   "20.5%"  "0.0%" "79.5%"
"06/04/2020 to 19/04/2020" "All Industries"                                                       "76.7%"  "0.5%" "22.8%"
"20/04/2020 to 03/05/2020" "Manufacturing"                                                        "85.9%"  "0.2%" "13.9%"
"20/04/2020 to 03/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "97.1%"  "0.0%" "2.9%" 
"20/04/2020 to 03/05/2020" "Construction"                                                         "78.1%"  "0.3%" "21.6%"
"20/04/2020 to 03/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "79.9%"  "0.1%" "20.0%"
"20/04/2020 to 03/05/2020" "Transportation And Storage"                                           "95.0%"  "0.0%" "5.0%" 
"20/04/2020 to 03/05/2020" "Accommodation And Food Service Activities"                            "21.9%"  "0.0%" "78.1%"
"20/04/2020 to 03/05/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%" 
"20/04/2020 to 03/05/2020" "Real Estate Activities"                                               "93.0%"  "1.4%" "5.6%" 
"20/04/2020 to 03/05/2020" "Professional, Scientific And Technical Activities"                    "95.5%"  "1.1%" "3.4%" 
"20/04/2020 to 03/05/2020" "Administrative And Support Service Activities"                        "92.7%"  "0.2%" "7.1%" 
"20/04/2020 to 03/05/2020" "Education"                                                            "89.2%"  "1.0%" "9.8%" 
"20/04/2020 to 03/05/2020" "Human Health And Social Work Activities"                              "95.4%"  "0.8%" "3.8%" 
"20/04/2020 to 03/05/2020" "Arts, Entertainment And Recreation"                                   "19.6%"  "0.3%" "80.1%"
"20/04/2020 to 03/05/2020" "All Industries"                                                       "79.3%"  "0.4%" "20.3%"
"04/05/2020 to 17/05/2020" "Manufacturing"                                                        "92.3%"  "0.3%" "7.4%" 
"04/05/2020 to 17/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "95.5%"  "0.0%" "4.5%" 
"04/05/2020 to 17/05/2020" "Construction"                                                         "80.4%"  "0.5%" "19.1%"
"04/05/2020 to 17/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "85.0%"  "0.2%" "14.8%"
"04/05/2020 to 17/05/2020" "Transportation And Storage"                                           "94.5%"  "0.0%" "5.5%" 
"04/05/2020 to 17/05/2020" "Accommodation And Food Service Activities"                            "25.4%"  "0.5%" "74.1%"
"04/05/2020 to 17/05/2020" "Information And Communication"                                        "93.8%"  "0.4%" "5.8%" 
"04/05/2020 to 17/05/2020" "Real Estate Activities"                                               "95.7%"  "1.4%" "2.9%" 
"04/05/2020 to 17/05/2020" "Professional, Scientific And Technical Activities"                    "97.1%"  "0.3%" "2.6%" 
"04/05/2020 to 17/05/2020" "Administrative And Support Service Activities"                        "91.1%"  "0.3%" "8.6%" 
"04/05/2020 to 17/05/2020" "Education"                                                            "90.3%"  "0.3%" "9.4%" 
"04/05/2020 to 17/05/2020" "Human Health And Social Work Activities"                              "95.9%"  "0.8%" "3.3%" 
"04/05/2020 to 17/05/2020" "Arts, Entertainment And Recreation"                                   "25.0%"  "0.4%" "74.6%"
"04/05/2020 to 17/05/2020" "All Industries"                                                       "82.0%"  "0.3%" "17.7%"
"18/05/2020 to 31/05/2020" "Manufacturing"                                                        "95.1%"  "0.1%" "4.8%" 
"18/05/2020 to 31/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "100.0%" "0.0%" "0.0%" 
"18/05/2020 to 31/05/2020" "Construction"                                                         "82.6%"  "0.4%" "17.0%"
"18/05/2020 to 31/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "88.2%"  "0.3%" "11.5%"
"18/05/2020 to 31/05/2020" "Transportation And Storage"                                           "95.7%"  "0.3%" "4.0%" 
"18/05/2020 to 31/05/2020" "Accommodation And Food Service Activities"                            "34.4%"  "0.3%" "65.3%"
"18/05/2020 to 31/05/2020" "Information And Communication"                                        "94.5%"  "0.7%" "4.8%" 
"18/05/2020 to 31/05/2020" "Real Estate Activities"                                               "94.0%"  "1.2%" "4.8%" 
"18/05/2020 to 31/05/2020" "Professional, Scientific And Technical Activities"                    "96.6%"  "1.1%" "2.4%" 
"18/05/2020 to 31/05/2020" "Administrative And Support Service Activities"                        "91.0%"  "0.3%" "8.7%" 
"18/05/2020 to 31/05/2020" "Education"                                                            "89.3%"  "0.3%" "10.4%"
"18/05/2020 to 31/05/2020" "Human Health And Social Work Activities"                              "94.7%"  "0.8%" "4.5%" 
"18/05/2020 to 31/05/2020" "Arts, Entertainment And Recreation"                                   "29.9%"  "0.3%" "69.8%"
"18/05/2020 to 31/05/2020" "All Industries"                                                       "84.1%"  "0.4%" "15.5%"
end
      Originally posted by Clyde Schechter View Post
      Another problem is that the panel data set, while nicely giving information on quarter and week, does not mention the year. Also, you need to clarify how week is defined in your panel data set. There is no universal standard way of enumerating weeks. Some people would consider the first week of 2023Q2 to be April 1 through April 7. But others would consider it to be April 2 (the first Sunday) through April 8. Still others use April 3 (the first Monday) through April 9. Still others count forward weeks starting from January 1, and would look at the first such week starting on or after April 1. (Since 2023 started on a Sunday, this would be the same as April 2 through April 8--but in other years this could be yet a different reckoning of the weeks.) Yet other complications arise at the end of the quarter. The lengths of quarters are not always multiples of 7 days. So, for example, if a quarter is 92 days (3rd and 4th quarters of any year), there is one "extra" day at the end. Do you treat this as a new, one-day week, or do you count the final day as the 8th day of an extended final week?

      Finally, (after you fix the dates in the second data set), there will likely be weeks in the panel data set that overlap with more than one date range in the second data set, and also date ranges in the second data set that encompass multiple weeks (in whole or in part). You need to decide on what you want to combine with what in these circumstances.
      I am a bit confused. I did not find a solution according to my understanding, but let me explain my Panal data. My panel data is longitudinal for five consecutive quarters. And I have five longitudinal datasets, so I merged five longitudinal data, each panel data containing five quarters. I created the variable "quarter" and labelled it by month and year, for example ( Oct - Des 2019.....) Also, the variable of the week shows when the interview was conducted and is spread over all quarters. For example, if we take an individual who was interviewed in the quarter Oct- Des 2019 and the number “7” appears for the variable WEEK, this means that he was interviewed in the seventh week of Oct-Dec 2019.
      Is there a solution or a correct way to merge in this case?



      Do you think there is a way we can go through the merger by relying on the quarter instead of the week? Could this be an alternative solution?

      Comment

      • #4
        Well, this is better. And I like the idea about matching by quarter and avoiding all the problems associated with weeks. But how to do it? Looking at the dates in the second data set, we can see, for just one example, information about Manufacturing with date = "23/03/2020 to 05/04/2020", which overlaps both the 1st and 2nd quarters of 2020. There are several observations, just in the example data, with a date range overlapping two quarters. So I'm sure it is pretty common in the data set as a whole. So, in situations like that, do you want to pair that with both of them? Or, if not, then what is the rule for deciding which quarter. (And if it is just one of them, the rule has to say what to do if a date range overlaps even more than 2 quarters.)

        I have one other question. In creating your quarter variable, you assigned, for example, 4 to the 4th quarter of 2019. Does this mean that you also assigned 3 to the third quarter of 2019, and 5 to the first quarter of 2020, and so on, using consecutive numbers in the same order as the quarters themselves? If you did, I can work with that. If not, then you need to show the exact and complete correspondence between your number variable named quarter and the actual calendar quarters being covered.

        There is some other data management that needs to be done to harmonize these for combining, but that is fairly straightforward. If you can answer my questions in the preceding paragraphs, I think I can write code that will put them together.

        Comment

        • #5
          Originally posted by Clyde Schechter View Post
          Well, this is better. And I like the idea about matching by quarter and avoiding all the problems associated with weeks. But how to do it? Looking at the dates in the second data set, we can see, for just one example, information about Manufacturing with date = "23/03/2020 to 05/04/2020", which overlaps both the 1st and 2nd quarters of 2020. There are several observations, just in the example data, with a date range overlapping two quarters. So I'm sure it is pretty common in the data set as a whole. So, in situations like that, do you want to pair that with both of them? Or, if not, then what is the rule for deciding which quarter. (And if it is just one of them, the rule has to say what to do if a date range overlaps even more than 2 quarters.)
          I am interested in the period from March 23 to May 31 2020, because this period represents the closing period in the labour market. Therefore, I think it is better to pair it with both quarters.


          Note that all data overlap only in the last week of the first quarter and the rest in the second quarter. Would pairing it with both quarters be logical? Or is it better to tie it in the second quarter only?


          Originally posted by Clyde Schechter View Post
          I have one other question. In creating your quarter variable, you assigned, for example, 4 to the 4th quarter of 2019. Does this mean that you also assigned 3 to the third quarter of 2019, and 5 to the first quarter of 2020, and so on, using consecutive numbers in the same order as the quarters themselves? If you did, I can work with that. If not, then you need to show the exact and complete correspondence between your number variable named quarter and the actual calendar quarters being covered.

          There is some other data management that needs to be done to harmonize these for combining, but that is fairly straightforward. If you can answer my questions in the preceding paragraphs, I think I can write code that will put them together.

          Yes, this is correct. I have data starting from the first quarter of 2019, "Jan- Mar 2019", and I assigned it number "1".....etc. But I dropped the first three-quarter from my data (Jan -Mar, April - June, and July -Sep 2019) because I'm not interested in this period.




          Comment

          • #6
            Note that all data overlap only in the last week of the first quarter and the rest in the second quarter. Would pairing it with both quarters be logical? Or is it better to tie it in the second quarter only?
            That's not a question I can answer. It's not a Stata question, nor a statistical question. Its question that depends on the uses to which you will put this data--only you can answer this.

            Assuming you stick with your decision to match with both quarters, I believe the following code will do it:
            Code:
            * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter WEEK SEX Inds07m)
9311094030103 4 10 2  9
4230193290101 4  1 2  7
1181393020101 4 13 2  9
1151093050101 4 10 1  3
 661193020101 4 11 1 17
9090791010103 4  7 1  9
7970793020101 4  7 2 16
 261391010102 4 13 2 17
1140193040101 4  1 1 13
2710693020101 4  6 1  9
2261291010104 4 12 2  8
4230294810101 4  2 2 17
1730191030101 4  1 2  9
1401194030101 4 11 2 16
2130193020102 4  1 2  3
2160593050101 4  5 2 18
1041091010101 4 10 2 16
 860793030101 4  7 2 18
1810393060101 4  3 2 17
3020193040104 4  1 2  9
1310693010101 4  6 2 15
1401291010101 4 12 2 17
 751394020103 4 13 2 16
4231091250101 4 10 2 14
4231393710102 4 13 1 10
3021293020101 4 12 2 16
4230594570102 4  5 2  7
2330894010102 4  8 2 17
2020293040102 4  2 2 16
2161093020101 4 10 1  3
1001193010101 4 11 2 18
1460891040101 4  8 1 14
  21391010101 4 13 2  9
2140991040102 4  9 2  9
1620193010101 4  1 1 17
 880393050101 4  3 1 17
 710493030102 4  4 2 16
4231094200102 4 10 2  9
2850394040102 4  3 2 17
 650293050102 4  2 1 13
9081393010102 4 13 1  6
3121393010101 4 13 1  6
7760993010101 4  9 2 17
1061291020101 4 12 1 15
 751394020105 4 13 1 16
2840593030103 4  5 2 10
2060393030102 4  3 1  8
3020293010103 4  2 1  9
 101091030102 4 10 1  9
2550393040101 4  3 1  6
9340991010102 4  9 1 10
2190491010101 4  4 2  3
2840491030103 4  4 2 16
3090494010101 4  4 1 17
7731291010102 4 12 2 17
 641093010101 4 10 1  8
 121294040101 4 12 1  7
 980691030103 4  6 2 18
 910793010101 4  7 2 17
 850791040101 4  7 2 10
7570893010101 4  8 1 13
1601091040103 4 10 1  7
1530593060102 4  5 2  9
  40593010104 4  5 2 14
 880991010102 4  9 2 13
9081393010101 4 13 2 13
1601091040102 4 10 2 17
1560793020101 4  7 2 17
1581191010102 4 11 2  9
1071394030101 4 13 2 16
4230193620102 4  1 2  8
1170891040101 4  8 1 13
 340291010101 4  2 2 17
 330894040102 4  8 2 17
1140193050103 4  1 1 17
3090293020101 4  2 2 17
9180394010101 4  3 1 14
 160593020101 4  5 2 16
9360193020101 4  1 2 16
1741093010101 4 10 2 17
 951191110101 4 11 1  9
1461094030102 4 10 1 19
 100191010103 4  1 2  9
1951394010101 4 13 1 18
 310293030101 4  2 2 15
1210791010103 4  7 1 14
 341094010103 4 10 1 18
1270591020101 4  5 2 15
9280491010101 4  4 1 17
 540691030102 4  6 1  1
7821393020103 4 13 1  3
 270491040101 4  4 1 11
2141193020101 4 11 1  9
1450691020104 4  6 1  9
4230591290102 4  5 1 19
 230991010102 4  9 2 14
1611391010101 4 13 2  9
 150593020104 4  5 2  9
4231193360103 4 11 2  7
2661093010101 4 10 2 17
end
label values quarter quarter
label def quarter 4 "Oct-Des 2019", modify
label values WEEK WEEK
label values SEX SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify
label values Inds07m Inds07m5
label def Inds07m5 1 "A  Agriculture, forestry and fishing", modify
label def Inds07m5 3 "C  Manufacturing", modify
label def Inds07m5 6 "F  Construction", modify
label def Inds07m5 7 "G  Wholesale, retail, repair of vehicles", modify
label def Inds07m5 8 "H  Transport and storage", modify
label def Inds07m5 9 "I  Accommodation and food services", modify
label def Inds07m5 10 "J  Information and communication", modify
label def Inds07m5 11 "K  Financial and insurance activities", modify
label def Inds07m5 13 "M  Prof, scientific, technical activ.", modify
label def Inds07m5 14 "N  Admin and support services", modify
label def Inds07m5 15 "O  Public admin and defence", modify
label def Inds07m5 16 "P  Education", modify
label def Inds07m5 17 "Q  Health and social work", modify
label def Inds07m5 18 "R  Arts, entertainment and recreation", modify
label def Inds07m5 19 "S  Other service activities", modify
tempfile panel
save `panel'

* Example generated by -dataex-. For more info, type help dataex
clear
input str24 date str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril
"23/03/2020 to 05/04/2020" "Manufacturing"                                                        "77.2%"  "0.2%" "22.7%"
"23/03/2020 to 05/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "92.0%"  "0.0%" "8.0%"
"23/03/2020 to 05/04/2020" "Construction"                                                         "70.9%"  "0.0%" "29.1%"
"23/03/2020 to 05/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "72.8%"  "0.2%" "27.0%"
"23/03/2020 to 05/04/2020" "Accommodation And Food Service Activities"                            "18.4%"  "0.4%" "81.2%"
"23/03/2020 to 05/04/2020" "Transportation And Storage"                                           "92.3%"  "0.0%" "7.7%"
"23/03/2020 to 05/04/2020" "Information And Communication"                                        "94.4%"  "0.5%" "5.1%"
"23/03/2020 to 05/04/2020" "Professional, Scientific And Technical Activities"                    "96.2%"  "0.1%" "3.7%"
"23/03/2020 to 05/04/2020" "Administrative And Support Service Activities"                        "89.7%"  "0.7%" "9.6%"
"23/03/2020 to 05/04/2020" "Education"                                                            "85.8%"  "0.3%" "13.8%"
"23/03/2020 to 05/04/2020" "Human Health And Social Work Activities"                              "95.6%"  "0.9%" "3.5%"
"23/03/2020 to 05/04/2020" "Arts, Entertainment And Recreation"                                   "16.7%"  "1.1%" "82.2%"
"23/03/2020 to 05/04/2020" "All Industries"                                                       "75.4%"  "0.3%" "24.3%"
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        "79.0%"  "0.4%" "20.6%"
"06/04/2020 to 19/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "90.0%"  "0.0%" "10.0%"
"06/04/2020 to 19/04/2020" "Construction"                                                         "73.2%"  "0.7%" "26.1%"
"06/04/2020 to 19/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "75.2%"  "0.5%" "24.3%"
"06/04/2020 to 19/04/2020" "Accommodation And Food Service Activities"                            "18.2%"  "1.2%" "80.6%"
"06/04/2020 to 19/04/2020" "Transportation And Storage"                                           "91.5%"  "0.0%" "8.5%"
"06/04/2020 to 19/04/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%"
"06/04/2020 to 19/04/2020" "Professional, Scientific And Technical Activities"                    "96.7%"  "0.3%" "3.0%"
"06/04/2020 to 19/04/2020" "Administrative And Support Service Activities"                        "91.5%"  "0.4%" "8.1%"
"06/04/2020 to 19/04/2020" "Education"                                                            "86.8%"  "0.6%" "12.6%"
"06/04/2020 to 19/04/2020" "Human Health And Social Work Activities"                              "93.8%"  "1.4%" "4.9%"
"06/04/2020 to 19/04/2020" "Arts, Entertainment And Recreation"                                   "20.5%"  "0.0%" "79.5%"
"06/04/2020 to 19/04/2020" "All Industries"                                                       "76.7%"  "0.5%" "22.8%"
"20/04/2020 to 03/05/2020" "Manufacturing"                                                        "85.9%"  "0.2%" "13.9%"
"20/04/2020 to 03/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "97.1%"  "0.0%" "2.9%"
"20/04/2020 to 03/05/2020" "Construction"                                                         "78.1%"  "0.3%" "21.6%"
"20/04/2020 to 03/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "79.9%"  "0.1%" "20.0%"
"20/04/2020 to 03/05/2020" "Transportation And Storage"                                           "95.0%"  "0.0%" "5.0%"
"20/04/2020 to 03/05/2020" "Accommodation And Food Service Activities"                            "21.9%"  "0.0%" "78.1%"
"20/04/2020 to 03/05/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%"
"20/04/2020 to 03/05/2020" "Real Estate Activities"                                               "93.0%"  "1.4%" "5.6%"
"20/04/2020 to 03/05/2020" "Professional, Scientific And Technical Activities"                    "95.5%"  "1.1%" "3.4%"
"20/04/2020 to 03/05/2020" "Administrative And Support Service Activities"                        "92.7%"  "0.2%" "7.1%"
"20/04/2020 to 03/05/2020" "Education"                                                            "89.2%"  "1.0%" "9.8%"
"20/04/2020 to 03/05/2020" "Human Health And Social Work Activities"                              "95.4%"  "0.8%" "3.8%"
"20/04/2020 to 03/05/2020" "Arts, Entertainment And Recreation"                                   "19.6%"  "0.3%" "80.1%"
"20/04/2020 to 03/05/2020" "All Industries"                                                       "79.3%"  "0.4%" "20.3%"
"04/05/2020 to 17/05/2020" "Manufacturing"                                                        "92.3%"  "0.3%" "7.4%"
"04/05/2020 to 17/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "95.5%"  "0.0%" "4.5%"
"04/05/2020 to 17/05/2020" "Construction"                                                         "80.4%"  "0.5%" "19.1%"
"04/05/2020 to 17/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "85.0%"  "0.2%" "14.8%"
"04/05/2020 to 17/05/2020" "Transportation And Storage"                                           "94.5%"  "0.0%" "5.5%"
"04/05/2020 to 17/05/2020" "Accommodation And Food Service Activities"                            "25.4%"  "0.5%" "74.1%"
"04/05/2020 to 17/05/2020" "Information And Communication"                                        "93.8%"  "0.4%" "5.8%"
"04/05/2020 to 17/05/2020" "Real Estate Activities"                                               "95.7%"  "1.4%" "2.9%"
"04/05/2020 to 17/05/2020" "Professional, Scientific And Technical Activities"                    "97.1%"  "0.3%" "2.6%"
"04/05/2020 to 17/05/2020" "Administrative And Support Service Activities"                        "91.1%"  "0.3%" "8.6%"
"04/05/2020 to 17/05/2020" "Education"                                                            "90.3%"  "0.3%" "9.4%"
"04/05/2020 to 17/05/2020" "Human Health And Social Work Activities"                              "95.9%"  "0.8%" "3.3%"
"04/05/2020 to 17/05/2020" "Arts, Entertainment And Recreation"                                   "25.0%"  "0.4%" "74.6%"
"04/05/2020 to 17/05/2020" "All Industries"                                                       "82.0%"  "0.3%" "17.7%"
"18/05/2020 to 31/05/2020" "Manufacturing"                                                        "95.1%"  "0.1%" "4.8%"
"18/05/2020 to 31/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "100.0%" "0.0%" "0.0%"
"18/05/2020 to 31/05/2020" "Construction"                                                         "82.6%"  "0.4%" "17.0%"
"18/05/2020 to 31/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "88.2%"  "0.3%" "11.5%"
"18/05/2020 to 31/05/2020" "Transportation And Storage"                                           "95.7%"  "0.3%" "4.0%"
"18/05/2020 to 31/05/2020" "Accommodation And Food Service Activities"                            "34.4%"  "0.3%" "65.3%"
"18/05/2020 to 31/05/2020" "Information And Communication"                                        "94.5%"  "0.7%" "4.8%"
"18/05/2020 to 31/05/2020" "Real Estate Activities"                                               "94.0%"  "1.2%" "4.8%"
"18/05/2020 to 31/05/2020" "Professional, Scientific And Technical Activities"                    "96.6%"  "1.1%" "2.4%"
"18/05/2020 to 31/05/2020" "Administrative And Support Service Activities"                        "91.0%"  "0.3%" "8.7%"
"18/05/2020 to 31/05/2020" "Education"                                                            "89.3%"  "0.3%" "10.4%"
"18/05/2020 to 31/05/2020" "Human Health And Social Work Activities"                              "94.7%"  "0.8%" "4.5%"
"18/05/2020 to 31/05/2020" "Arts, Entertainment And Recreation"                                   "29.9%"  "0.3%" "69.8%"
"18/05/2020 to 31/05/2020" "All Industries"                                                       "84.1%"  "0.4%" "15.5%"
end                                                    
tempfile trade
save `trade'

//  PREPARE PANEL DATA SET FOR MERGER
use `panel'
gen qdate = tq(2018q4) + quarter
format qdate %tq
decode Inds07m, gen(industry)
replace industry = substr(industry, 3, .)
format PERSID %15.0f
isid PERSID qdate, sort
tempfile cleaned_treat
save `cleaned_treat'

//  PREPARE TRADE DATA SET FOR MERGER
use `trade', clear
split date, gen(d) parse(" to ")
drop date
forvalues i = 1/2 {
    gen date`i' = daily(d`i', "DMY")
    assert missing(date`i') == missing(d`i')
    format date`i' %td
    drop d`i'
    gen qdate`i' = qofd(date`i')
    format qdate`i' %tq
}

//  NOW JOIN EACH OBSERVATION IN TRADE DATA SET WITH EVERY PANEL OBSERVATION
//  THAT AGREES ON INDUSTRY AND HAS DATE FALLING WITHIN THE RANGE OF DATES
rangejoin qdate qdate1 qdate2 using `cleaned_treat', by(industry)
keep if !missing(PERSID) // DROP UNMATCHED OBSERVATIONS

//  NOW MERGE THIS BACK TO
merge m:1 PERSID qdate using `cleaned_treat', keep(match using) nogenerate
            Disclaimer: In your example data, the panel data set is entirely from year 2019 and the trade data is entirely from year 2020. So there are no matches between them. Consequently, I can't claim that this code is fully tested. Nevertheless, I am reasonably confident it will work properly in your full data sets. If it does not, when posting back, please show new example data (with -dataex-, of course) that contains observations that should pair up across the data sets.

            Notes:
            1. Both of the data sets have problems that needed to be fixed before combining. Consequently each one is separately cleaned in this code. To illustrate the process with your example data, I have stored each of them in a tempfile, named `panel', and `trade', respectively. Of course, in your situation, you will not do that: you will just use the data sets under their real names. On the other hand, the tempfile `cleaned_panel' is specifically created by the code to hold the modified version of the panel data set. So you will, when adapting to your setup, retain all references to that one as they are.
            2. The string date variables in the two data sets cannot be used for matching. Stata internal format numeric date variables, quarterly ones in this case, are needed. The code creates those.
            3. The industry variable has only been partially harmonized in the data examples you show. First, in the panel data, it is actually a numeric variable with value labels that, I imagine, you created with the -encode- command. In the trade data, however, it is a string variable. To combine the data sets, the variable has to be set up the same way in both. When you are working with data sets that you plan to combine and where matching on some text variable like industry is needed, it is a bad idea to use -encode- before you combine the data sets. That's because there is no guarantee that -encode- will assign the same numeric values to the same string values in both data sets, so you can end up with really severe mismatches that way. That's why in the panel data set I revert to a string variable, which in turn had to have the leading numbers trimmed out so that it would actually align with the industry string variable in the trade data set.
            4. -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, and also available from SSC.
            If you are going to work with longitudinal data like this on an ongoing basis, you should familiarize yourself with Stata datetime variables and the functions used to create them from strings, format them for displaying in Stata, and convert among the different types (daily, quarterly, monthly, clock, etc.). The Stata documentation on this is extensive and clear, and you can link to it from the -help datetime- page. It is also very lengthy and has too much detail for anybody to remember. But that's OK. What you need to learn is the overall approach and the most fundamental functions. You will find that there are some that you use repeatedly, and the details of those will stick in your mind, whereas others you use only occasionally. For the latter you will have to refer back to the -help- for a reminder of the details when you need them. Going through this is an investment of time that will be rapidly and abundantly repaid if you will be using Stata with longitudinal data going forward.
            Last edited by Clyde Schechter; 10 Mar 2023, 11:24. Reason: Fix -dataex- output that got mangled in original post.

            Comment

            • #7
              Originally posted by Clyde Schechter View Post

              Disclaimer: In your example data, the panel data set is entirely from year 2019 and the trade data is entirely from year 2020. So there are no matches between them. Consequently, I can't claim that this code is fully tested. Nevertheless, I am reasonably confident it will work properly in your full data sets. If it does not, when posting back, please show new example data (with -dataex-, of course) that contains observations that should pair up across the data sets.

              Thanks Clyder for your suggestions and work in this matter. I followed your suggestion commands, but that give me missing data an all variables in the external data sets

              This example for the new data:

              Code:
              * Example generated by -dataex-. For more info, type help dataex
clear
input str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril float(date1 qdate1 date2 qdate2) double PERSID float qdate byte(WEEK SEX)
"es not apply"                           "" "" "" . . . . 10493040101 238  4 2
"es not apply"                           "" "" "" . . . . 10493040101 239  4 2
"es not apply"                           "" "" "" . . . . 10493040101 240  4 2
"es not apply"                           "" "" "" . . . . 10493040101 241  4 2
"es not apply"                           "" "" "" . . . . 10493040101 242  4 2
"es not apply"                           "" "" "" . . . . 10694020101 239  6 2
"es not apply"                           "" "" "" . . . . 10694020101 240  6 2
"es not apply"                           "" "" "" . . . . 10694020101 241  6 2
"es not apply"                           "" "" "" . . . . 10694020101 242  6 2
"es not apply"                           "" "" "" . . . . 10694020101 243  6 2
"es not apply"                           "" "" "" . . . . 10694020102 239  6 1
"es not apply"                           "" "" "" . . . . 10694020102 240  6 1
"es not apply"                           "" "" "" . . . . 10694020102 241  6 1
"es not apply"                           "" "" "" . . . . 10694020102 242  6 1
"es not apply"                           "" "" "" . . . . 10694020102 243  6 1
"es not apply"                           "" "" "" . . . . 10792030101 237  7 2
"es not apply"                           "" "" "" . . . . 10792030101 238  7 2
"es not apply"                           "" "" "" . . . . 10792030101 239  7 2
"es not apply"                           "" "" "" . . . . 10792030101 240  7 2
"es not apply"                           "" "" "" . . . . 10792030101 241  7 2
" Public admin and defence"              "" "" "" . . . . 10793010101 238  7 2
" Public admin and defence"              "" "" "" . . . . 10793010101 239  7 2
" Public admin and defence"              "" "" "" . . . . 10793010101 240  7 2
" Public admin and defence"              "" "" "" . . . . 10793010101 241  7 2
" Public admin and defence"              "" "" "" . . . . 10793010101 242  7 2
" Accommodation and food services"       "" "" "" . . . . 10794010101 239  7 1
" Accommodation and food services"       "" "" "" . . . . 10794010101 240  7 1
" Accommodation and food services"       "" "" "" . . . . 10794010101 241  7 1
" Accommodation and food services"       "" "" "" . . . . 10794010101 242  7 1
" Accommodation and food services"       "" "" "" . . . . 10794010101 243  7 1
" Accommodation and food services"       "" "" "" . . . . 10794010102 239  7 2
" Accommodation and food services"       "" "" "" . . . . 10794010102 240  7 2
" Accommodation and food services"       "" "" "" . . . . 10794010102 241  7 2
" Accommodation and food services"       "" "" "" . . . . 10794010102 242  7 2
" Accommodation and food services"       "" "" "" . . . . 10794010102 243  7 2
" Education"                             "" "" "" . . . . 10993020101 238  9 2
" Education"                             "" "" "" . . . . 10993020101 239  9 2
" Education"                             "" "" "" . . . . 10993020101 240  9 2
" Education"                             "" "" "" . . . . 10993020101 241  9 2
"es not apply"                           "" "" "" . . . . 10993020101 242  9 2
" Public admin and defence"              "" "" "" . . . . 10993020102 238  9 1
" Public admin and defence"              "" "" "" . . . . 10993020102 239  9 1
" Public admin and defence"              "" "" "" . . . . 10993020102 240  9 1
" Public admin and defence"              "" "" "" . . . . 10993020102 241  9 1
" Public admin and defence"              "" "" "" . . . . 10993020102 242  9 1
" Education"                             "" "" "" . . . . 11091010101 236 10 2
" Education"                             "" "" "" . . . . 11091010101 237 10 2
" Education"                             "" "" "" . . . . 11091010101 238 10 2
" Education"                             "" "" "" . . . . 11091010101 239 10 2
" Education"                             "" "" "" . . . . 11091010101 240 10 2
"es not apply"                           "" "" "" . . . . 11093030101 238 10 2
"es not apply"                           "" "" "" . . . . 11093030101 239 10 2
"es not apply"                           "" "" "" . . . . 11093030101 240 10 2
"es not apply"                           "" "" "" . . . . 11093030101 241 10 2
"es not apply"                           "" "" "" . . . . 11093030101 242 10 2
" Public admin and defence"              "" "" "" . . . . 11094010101 239 10 1
"es not apply"                           "" "" "" . . . . 11094010101 240 10 1
"es not apply"                           "" "" "" . . . . 11094010101 241 10 1
"es not apply"                           "" "" "" . . . . 11094010101 242 10 1
"es not apply"                           "" "" "" . . . . 11094010101 243 10 1
" Health and social work"                "" "" "" . . . . 11094010102 239 10 2
" Health and social work"                "" "" "" . . . . 11094010102 240 10 2
" Health and social work"                "" "" "" . . . . 11094010102 241 10 2
" Health and social work"                "" "" "" . . . . 11094010102 242 10 2
" Health and social work"                "" "" "" . . . . 11094010102 243 10 2
"es not apply"                           "" "" "" . . . . 11291020101 236 12 2
"es not apply"                           "" "" "" . . . . 11291020101 237 12 2
"es not apply"                           "" "" "" . . . . 11291020101 238 12 2
"es not apply"                           "" "" "" . . . . 11291020101 239 12 2
"es not apply"                           "" "" "" . . . . 11291020101 240 12 2
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 11292020101 237 12 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 11292020101 238 12 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 11292020101 239 12 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 11292020101 240 12 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 11292020101 241 12 1
" Public admin and defence"              "" "" "" . . . . 11294030101 239 12 2
" Public admin and defence"              "" "" "" . . . . 11294030101 240 12 2
" Public admin and defence"              "" "" "" . . . . 11294030101 241 12 2
" Public admin and defence"              "" "" "" . . . . 11294030101 242 12 2
" Public admin and defence"              "" "" "" . . . . 11294030101 243 12 2
"es not apply"                           "" "" "" . . . . 11294030102 239 12 1
"es not apply"                           "" "" "" . . . . 11294030102 240 12 1
"es not apply"                           "" "" "" . . . . 11294030102 241 12 1
"es not apply"                           "" "" "" . . . . 11294030102 242 12 1
"es not apply"                           "" "" "" . . . . 11294030102 243 12 1
" Education"                             "" "" "" . . . . 20191020101 236  1 1
" Education"                             "" "" "" . . . . 20191020101 237  1 1
" Education"                             "" "" "" . . . . 20191020101 238  1 1
" Education"                             "" "" "" . . . . 20191020101 239  1 1
" Education"                             "" "" "" . . . . 20191020101 240  1 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 20191020102 236  1 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 20191020102 237  1 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 20191020102 238  1 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 20191020102 239  1 1
" Wholesale, retail, repair of vehicles" "" "" "" . . . . 20191020102 240  1 1
" Other service activities"              "" "" "" . . . . 20191040101 236  1 2
" Other service activities"              "" "" "" . . . . 20191040101 237  1 2
" Other service activities"              "" "" "" . . . . 20191040101 238  1 2
" Other service activities"              "" "" "" . . . . 20191040101 239  1 2
" Other service activities"              "" "" "" . . . . 20191040101 240  1 2
end
format %td date1
format %tq qdate1
format %td date2
format %tq qdate2
format %tq qdate
label values WEEK WEEK
label values SEX SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify
              Is there any way to solve this ?

              I appreciate you time and work

              Comment

              • #8
                I see what may be the problem. Try this:
                Code:
                * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter WEEK SEX Inds07m)
9311094030103 4 10 2  9
4230193290101 4  1 2  7
1181393020101 4 13 2  9
1151093050101 4 10 1  3
 661193020101 4 11 1 17
9090791010103 4  7 1  9
7970793020101 4  7 2 16
 261391010102 4 13 2 17
1140193040101 4  1 1 13
2710693020101 4  6 1  9
2261291010104 4 12 2  8
4230294810101 4  2 2 17
1730191030101 4  1 2  9
1401194030101 4 11 2 16
2130193020102 4  1 2  3
2160593050101 4  5 2 18
1041091010101 4 10 2 16
 860793030101 4  7 2 18
1810393060101 4  3 2 17
3020193040104 4  1 2  9
1310693010101 4  6 2 15
1401291010101 4 12 2 17
 751394020103 4 13 2 16
4231091250101 4 10 2 14
4231393710102 4 13 1 10
3021293020101 4 12 2 16
4230594570102 4  5 2  7
2330894010102 4  8 2 17
2020293040102 4  2 2 16
2161093020101 4 10 1  3
1001193010101 4 11 2 18
1460891040101 4  8 1 14
  21391010101 4 13 2  9
2140991040102 4  9 2  9
1620193010101 4  1 1 17
 880393050101 4  3 1 17
 710493030102 4  4 2 16
4231094200102 4 10 2  9
2850394040102 4  3 2 17
 650293050102 4  2 1 13
9081393010102 4 13 1  6
3121393010101 4 13 1  6
7760993010101 4  9 2 17
1061291020101 4 12 1 15
 751394020105 4 13 1 16
2840593030103 4  5 2 10
2060393030102 4  3 1  8
3020293010103 4  2 1  9
 101091030102 4 10 1  9
2550393040101 4  3 1  6
9340991010102 4  9 1 10
2190491010101 4  4 2  3
2840491030103 4  4 2 16
3090494010101 4  4 1 17
7731291010102 4 12 2 17
 641093010101 4 10 1  8
 121294040101 4 12 1  7
 980691030103 4  6 2 18
 910793010101 4  7 2 17
 850791040101 4  7 2 10
7570893010101 4  8 1 13
1601091040103 4 10 1  7
1530593060102 4  5 2  9
  40593010104 4  5 2 14
 880991010102 4  9 2 13
9081393010101 4 13 2 13
1601091040102 4 10 2 17
1560793020101 4  7 2 17
1581191010102 4 11 2  9
1071394030101 4 13 2 16
4230193620102 4  1 2  8
1170891040101 4  8 1 13
 340291010101 4  2 2 17
 330894040102 4  8 2 17
1140193050103 4  1 1 17
3090293020101 4  2 2 17
9180394010101 4  3 1 14
 160593020101 4  5 2 16
9360193020101 4  1 2 16
1741093010101 4 10 2 17
 951191110101 4 11 1  9
1461094030102 4 10 1 19
 100191010103 4  1 2  9
1951394010101 4 13 1 18
 310293030101 4  2 2 15
1210791010103 4  7 1 14
 341094010103 4 10 1 18
1270591020101 4  5 2 15
9280491010101 4  4 1 17
 540691030102 4  6 1  1
7821393020103 4 13 1  3
 270491040101 4  4 1 11
2141193020101 4 11 1  9
1450691020104 4  6 1  9
4230591290102 4  5 1 19
 230991010102 4  9 2 14
1611391010101 4 13 2  9
 150593020104 4  5 2  9
4231193360103 4 11 2  7
2661093010101 4 10 2 17
end
label values quarter quarter
label def quarter 4 "Oct-Des 2019", modify
label values WEEK WEEK
label values SEX SEX
label def SEX 1 "Male", modify
label def SEX 2 "Female", modify
label values Inds07m Inds07m5
label def Inds07m5 1 "A  Agriculture, forestry and fishing", modify
label def Inds07m5 3 "C  Manufacturing", modify
label def Inds07m5 6 "F  Construction", modify
label def Inds07m5 7 "G  Wholesale, retail, repair of vehicles", modify
label def Inds07m5 8 "H  Transport and storage", modify
label def Inds07m5 9 "I  Accommodation and food services", modify
label def Inds07m5 10 "J  Information and communication", modify
label def Inds07m5 11 "K  Financial and insurance activities", modify
label def Inds07m5 13 "M  Prof, scientific, technical activ.", modify
label def Inds07m5 14 "N  Admin and support services", modify
label def Inds07m5 15 "O  Public admin and defence", modify
label def Inds07m5 16 "P  Education", modify
label def Inds07m5 17 "Q  Health and social work", modify
label def Inds07m5 18 "R  Arts, entertainment and recreation", modify
label def Inds07m5 19 "S  Other service activities", modify
tempfile panel
save `panel'

* Example generated by -dataex-. For more info, type help dataex
clear
input str24 date str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril
"23/03/2020 to 05/04/2020" "Manufacturing"                                                        "77.2%"  "0.2%" "22.7%"
"23/03/2020 to 05/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "92.0%"  "0.0%" "8.0%"
"23/03/2020 to 05/04/2020" "Construction"                                                         "70.9%"  "0.0%" "29.1%"
"23/03/2020 to 05/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "72.8%"  "0.2%" "27.0%"
"23/03/2020 to 05/04/2020" "Accommodation And Food Service Activities"                            "18.4%"  "0.4%" "81.2%"
"23/03/2020 to 05/04/2020" "Transportation And Storage"                                           "92.3%"  "0.0%" "7.7%"
"23/03/2020 to 05/04/2020" "Information And Communication"                                        "94.4%"  "0.5%" "5.1%"
"23/03/2020 to 05/04/2020" "Professional, Scientific And Technical Activities"                    "96.2%"  "0.1%" "3.7%"
"23/03/2020 to 05/04/2020" "Administrative And Support Service Activities"                        "89.7%"  "0.7%" "9.6%"
"23/03/2020 to 05/04/2020" "Education"                                                            "85.8%"  "0.3%" "13.8%"
"23/03/2020 to 05/04/2020" "Human Health And Social Work Activities"                              "95.6%"  "0.9%" "3.5%"
"23/03/2020 to 05/04/2020" "Arts, Entertainment And Recreation"                                   "16.7%"  "1.1%" "82.2%"
"23/03/2020 to 05/04/2020" "All Industries"                                                       "75.4%"  "0.3%" "24.3%"
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        "79.0%"  "0.4%" "20.6%"
"06/04/2020 to 19/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "90.0%"  "0.0%" "10.0%"
"06/04/2020 to 19/04/2020" "Construction"                                                         "73.2%"  "0.7%" "26.1%"
"06/04/2020 to 19/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "75.2%"  "0.5%" "24.3%"
"06/04/2020 to 19/04/2020" "Accommodation And Food Service Activities"                            "18.2%"  "1.2%" "80.6%"
"06/04/2020 to 19/04/2020" "Transportation And Storage"                                           "91.5%"  "0.0%" "8.5%"
"06/04/2020 to 19/04/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%"
"06/04/2020 to 19/04/2020" "Professional, Scientific And Technical Activities"                    "96.7%"  "0.3%" "3.0%"
"06/04/2020 to 19/04/2020" "Administrative And Support Service Activities"                        "91.5%"  "0.4%" "8.1%"
"06/04/2020 to 19/04/2020" "Education"                                                            "86.8%"  "0.6%" "12.6%"
"06/04/2020 to 19/04/2020" "Human Health And Social Work Activities"                              "93.8%"  "1.4%" "4.9%"
"06/04/2020 to 19/04/2020" "Arts, Entertainment And Recreation"                                   "20.5%"  "0.0%" "79.5%"
"06/04/2020 to 19/04/2020" "All Industries"                                                       "76.7%"  "0.5%" "22.8%"
"20/04/2020 to 03/05/2020" "Manufacturing"                                                        "85.9%"  "0.2%" "13.9%"
"20/04/2020 to 03/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "97.1%"  "0.0%" "2.9%"
"20/04/2020 to 03/05/2020" "Construction"                                                         "78.1%"  "0.3%" "21.6%"
"20/04/2020 to 03/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "79.9%"  "0.1%" "20.0%"
"20/04/2020 to 03/05/2020" "Transportation And Storage"                                           "95.0%"  "0.0%" "5.0%"
"20/04/2020 to 03/05/2020" "Accommodation And Food Service Activities"                            "21.9%"  "0.0%" "78.1%"
"20/04/2020 to 03/05/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%"
"20/04/2020 to 03/05/2020" "Real Estate Activities"                                               "93.0%"  "1.4%" "5.6%"
"20/04/2020 to 03/05/2020" "Professional, Scientific And Technical Activities"                    "95.5%"  "1.1%" "3.4%"
"20/04/2020 to 03/05/2020" "Administrative And Support Service Activities"                        "92.7%"  "0.2%" "7.1%"
"20/04/2020 to 03/05/2020" "Education"                                                            "89.2%"  "1.0%" "9.8%"
"20/04/2020 to 03/05/2020" "Human Health And Social Work Activities"                              "95.4%"  "0.8%" "3.8%"
"20/04/2020 to 03/05/2020" "Arts, Entertainment And Recreation"                                   "19.6%"  "0.3%" "80.1%"
"20/04/2020 to 03/05/2020" "All Industries"                                                       "79.3%"  "0.4%" "20.3%"
"04/05/2020 to 17/05/2020" "Manufacturing"                                                        "92.3%"  "0.3%" "7.4%"
"04/05/2020 to 17/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "95.5%"  "0.0%" "4.5%"
"04/05/2020 to 17/05/2020" "Construction"                                                         "80.4%"  "0.5%" "19.1%"
"04/05/2020 to 17/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "85.0%"  "0.2%" "14.8%"
"04/05/2020 to 17/05/2020" "Transportation And Storage"                                           "94.5%"  "0.0%" "5.5%"
"04/05/2020 to 17/05/2020" "Accommodation And Food Service Activities"                            "25.4%"  "0.5%" "74.1%"
"04/05/2020 to 17/05/2020" "Information And Communication"                                        "93.8%"  "0.4%" "5.8%"
"04/05/2020 to 17/05/2020" "Real Estate Activities"                                               "95.7%"  "1.4%" "2.9%"
"04/05/2020 to 17/05/2020" "Professional, Scientific And Technical Activities"                    "97.1%"  "0.3%" "2.6%"
"04/05/2020 to 17/05/2020" "Administrative And Support Service Activities"                        "91.1%"  "0.3%" "8.6%"
"04/05/2020 to 17/05/2020" "Education"                                                            "90.3%"  "0.3%" "9.4%"
"04/05/2020 to 17/05/2020" "Human Health And Social Work Activities"                              "95.9%"  "0.8%" "3.3%"
"04/05/2020 to 17/05/2020" "Arts, Entertainment And Recreation"                                   "25.0%"  "0.4%" "74.6%"
"04/05/2020 to 17/05/2020" "All Industries"                                                       "82.0%"  "0.3%" "17.7%"
"18/05/2020 to 31/05/2020" "Manufacturing"                                                        "95.1%"  "0.1%" "4.8%"
"18/05/2020 to 31/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "100.0%" "0.0%" "0.0%"
"18/05/2020 to 31/05/2020" "Construction"                                                         "82.6%"  "0.4%" "17.0%"
"18/05/2020 to 31/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "88.2%"  "0.3%" "11.5%"
"18/05/2020 to 31/05/2020" "Transportation And Storage"                                           "95.7%"  "0.3%" "4.0%"
"18/05/2020 to 31/05/2020" "Accommodation And Food Service Activities"                            "34.4%"  "0.3%" "65.3%"
"18/05/2020 to 31/05/2020" "Information And Communication"                                        "94.5%"  "0.7%" "4.8%"
"18/05/2020 to 31/05/2020" "Real Estate Activities"                                               "94.0%"  "1.2%" "4.8%"
"18/05/2020 to 31/05/2020" "Professional, Scientific And Technical Activities"                    "96.6%"  "1.1%" "2.4%"
"18/05/2020 to 31/05/2020" "Administrative And Support Service Activities"                        "91.0%"  "0.3%" "8.7%"
"18/05/2020 to 31/05/2020" "Education"                                                            "89.3%"  "0.3%" "10.4%"
"18/05/2020 to 31/05/2020" "Human Health And Social Work Activities"                              "94.7%"  "0.8%" "4.5%"
"18/05/2020 to 31/05/2020" "Arts, Entertainment And Recreation"                                   "29.9%"  "0.3%" "69.8%"
"18/05/2020 to 31/05/2020" "All Industries"                                                       "84.1%"  "0.4%" "15.5%"
end
tempfile trade
save `trade'



//  PREPARE PANEL DATA SET FOR MERGER
use `panel'
gen qdate = tq(2018q4) + quarter
format qdate %tq
decode Inds07m, gen(industry)
replace industry = substr(industry, 4, .)
format PERSID %15.0f
isid PERSID qdate, sort
tempfile cleaned_treat
save `cleaned_treat'

//  PREPARE TRADE DATA SET FOR MERGER
use `trade', clear
split date, gen(d) parse(" to ")
drop date
forvalues i = 1/2 {
    gen date`i' = daily(d`i', "DMY")
    assert missing(date`i') == missing(d`i')
    format date`i' %td
    drop d`i'
    gen qdate`i' = qofd(date`i')
    format qdate`i' %tq
}

//  NOW JOIN EACH OBSERVATION IN TRADE DATA SET WITH EVERY PANEL OBSERVATION
//  THAT AGREES ON INDUSTRY AND HAS DATE FALLING WITHIN THE RANGE OF DATES
rangejoin qdate qdate1 qdate2 using `cleaned_treat', by(industry)
keep if !missing(PERSID) // DROP UNMATCHED OBSERVATIONS

//  NOW MERGE THIS BACK TO THE PANEL DATA SET
merge m:1 PERSID qdate using `cleaned_treat', keep(match using) nogenerate
                When I looked at the data previously, I didn't appreciate that the labels for industry in the panel data set had two spaces between the single letter code and the start of the name of the industry. So, that is fixed here. But if that does not solve the problem, you need to give me fresh examples from both the panel data set and the trade data set, including examples that will overlap. Showing me the incorrect results you are getting is also helpful, but not sufficient.

                Comment

                • #9
                  Originally posted by Clyde Schechter View Post
                  When I looked at the data previously, I didn't appreciate that the labels for industry in the panel data set had two spaces between the single letter code and the start of the name of the industry. So, that is fixed here. But if that does not solve the problem, you need to give me fresh examples from both the panel data set and the trade data set, including examples that will overlap. Showing me the incorrect results you are getting is also helpful, but not sufficient.

                  Thanks for your help with that, and I really apricate.

                  When I implement the new command, it matches just around 12000 observations, and I have over 75000 observations in my dataset. This is the first 12000 obs :
                  Code:
                  * Example generated by -dataex-. For more info, type help dataex
clear
input str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril double PERSID float qdate byte quarter
"Education"     "85.8%" "0.3%" "13.8%" 10993020101 240 5
"Education"     "86.8%" "0.6%" "12.6%" 10993020101 241 6
"Education"     "90.3%" "0.3%" "9.4%"  10993020101 241 6
"Education"     "89.3%" "0.3%" "10.4%" 10993020101 241 6
"Education"     "85.8%" "0.3%" "13.8%" 10993020101 241 6
"Education"     "89.2%" "1.0%" "9.8%"  10993020101 241 6
"Education"     "85.8%" "0.3%" "13.8%" 11091010101 240 5
"Education"     "85.8%" "0.3%" "13.8%" 20191020101 240 5
"Education"     "85.8%" "0.3%" "13.8%" 20494030102 240 5
"Education"     "90.3%" "0.3%" "9.4%"  20494030102 241 6
"Education"     "86.8%" "0.6%" "12.6%" 20494030102 241 6
"Education"     "89.2%" "1.0%" "9.8%"  20494030102 241 6
"Education"     "89.3%" "0.3%" "10.4%" 20494030102 241 6
"Education"     "85.8%" "0.3%" "13.8%" 20494030102 241 6
"Education"     "85.8%" "0.3%" "13.8%" 20494050101 240 5
"Education"     "85.8%" "0.3%" "13.8%" 20494050101 241 6
"Education"     "86.8%" "0.6%" "12.6%" 20494050101 241 6
"Education"     "90.3%" "0.3%" "9.4%"  20494050101 241 6
"Education"     "89.2%" "1.0%" "9.8%"  20494050101 241 6
"Education"     "89.3%" "0.3%" "10.4%" 20494050101 241 6
"Education"     "85.8%" "0.3%" "13.8%" 21391030101 240 5
"Manufacturing" "77.2%" "0.2%" "22.7%" 30192030102 240 5
"Manufacturing" "95.1%" "0.1%" "4.8%"  30192030102 241 6
"Manufacturing" "79.0%" "0.4%" "20.6%" 30192030102 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 30192030102 241 6
"Manufacturing" "92.3%" "0.3%" "7.4%"  30192030102 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 30192030102 241 6
"Education"     "85.8%" "0.3%" "13.8%" 30594010101 240 5
"Education"     "85.8%" "0.3%" "13.8%" 30594010101 241 6
"Education"     "90.3%" "0.3%" "9.4%"  30594010101 241 6
"Education"     "86.8%" "0.6%" "12.6%" 30594010101 241 6
"Education"     "89.2%" "1.0%" "9.8%"  30594010101 241 6
"Education"     "89.3%" "0.3%" "10.4%" 30594010101 241 6
"Construction"  "70.9%" "0.0%" "29.1%" 31392050101 240 5
"Construction"  "70.9%" "0.0%" "29.1%" 31392050101 241 6
"Construction"  "80.4%" "0.5%" "19.1%" 31392050101 241 6
"Construction"  "78.1%" "0.3%" "21.6%" 31392050101 241 6
"Construction"  "82.6%" "0.4%" "17.0%" 31392050101 241 6
"Construction"  "73.2%" "0.7%" "26.1%" 31392050101 241 6
"Education"     "85.8%" "0.3%" "13.8%" 31392070101 240 5
"Manufacturing" "77.2%" "0.2%" "22.7%" 50194010101 240 5
"Manufacturing" "79.0%" "0.4%" "20.6%" 50194010101 241 6
"Manufacturing" "95.1%" "0.1%" "4.8%"  50194010101 241 6
"Manufacturing" "92.3%" "0.3%" "7.4%"  50194010101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 50194010101 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 50194010101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 50393040101 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 50393040101 241 6
"Manufacturing" "92.3%" "0.3%" "7.4%"  50393040101 241 6
"Manufacturing" "79.0%" "0.4%" "20.6%" 50393040101 241 6
"Manufacturing" "95.1%" "0.1%" "4.8%"  50393040101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 50792010101 240 5
"Manufacturing" "92.3%" "0.3%" "7.4%"  50792010101 241 6
"Manufacturing" "95.1%" "0.1%" "4.8%"  50792010101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 50792010101 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 50792010101 241 6
"Manufacturing" "79.0%" "0.4%" "20.6%" 50792010101 241 6
"Construction"  "70.9%" "0.0%" "29.1%" 50792010102 240 5
"Construction"  "78.1%" "0.3%" "21.6%" 50792010102 241 6
"Construction"  "80.4%" "0.5%" "19.1%" 50792010102 241 6
"Construction"  "73.2%" "0.7%" "26.1%" 50792010102 241 6
"Construction"  "70.9%" "0.0%" "29.1%" 50792010102 241 6
"Construction"  "82.6%" "0.4%" "17.0%" 50792010102 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 50893020101 240 5
"Manufacturing" "77.2%" "0.2%" "22.7%" 50893020101 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 50893020101 241 6
"Manufacturing" "92.3%" "0.3%" "7.4%"  50893020101 241 6
"Manufacturing" "79.0%" "0.4%" "20.6%" 50893020101 241 6
"Manufacturing" "95.1%" "0.1%" "4.8%"  50893020101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 50991020101 240 5
"Manufacturing" "77.2%" "0.2%" "22.7%" 50991030101 240 5
"Manufacturing" "77.2%" "0.2%" "22.7%" 50991030102 240 5
"Education"     "85.8%" "0.3%" "13.8%" 51194020102 240 5
"Education"     "86.8%" "0.6%" "12.6%" 51194020102 241 6
"Education"     "85.8%" "0.3%" "13.8%" 51194020102 241 6
"Education"     "89.2%" "1.0%" "9.8%"  51194020102 241 6
"Education"     "89.3%" "0.3%" "10.4%" 51194020102 241 6
"Education"     "90.3%" "0.3%" "9.4%"  51194020102 241 6
"Education"     "85.8%" "0.3%" "13.8%" 60494030101 240 5
"Education"     "86.8%" "0.6%" "12.6%" 60494030101 241 6
"Education"     "89.2%" "1.0%" "9.8%"  60494030101 241 6
"Education"     "85.8%" "0.3%" "13.8%" 60494030101 241 6
"Education"     "89.3%" "0.3%" "10.4%" 60494030101 241 6
"Education"     "90.3%" "0.3%" "9.4%"  60494030101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 60693010102 240 5
"Manufacturing" "77.2%" "0.2%" "22.7%" 60693010102 241 6
"Manufacturing" "79.0%" "0.4%" "20.6%" 60693010102 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 60693010102 241 6
"Manufacturing" "92.3%" "0.3%" "7.4%"  60693010102 241 6
"Manufacturing" "95.1%" "0.1%" "4.8%"  60693010102 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 61194020102 240 5
"Manufacturing" "95.1%" "0.1%" "4.8%"  61194020102 241 6
"Manufacturing" "79.0%" "0.4%" "20.6%" 61194020102 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 61194020102 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 61194020102 241 6
"Manufacturing" "92.3%" "0.3%" "7.4%"  61194020102 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 70694020101 240 5
"Manufacturing" "92.3%" "0.3%" "7.4%"  70694020101 241 6
"Manufacturing" "77.2%" "0.2%" "22.7%" 70694020101 241 6
"Manufacturing" "85.9%" "0.2%" "13.9%" 70694020101 241 6
end
format %tq qdate
label values quarter quarter
label def quarter 5 "Jan-Mar 2020", modify
label def quarter 6 "April-June 2020", modify

                  However, I got missing data in new data in the rest of the observations (after 12000 to 75000):
                  Code:
                  * Example generated by -dataex-. For more info, type help dataex
clear
input str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril double PERSID byte quarter float qdate
"Wholesale, retail, repair of vehicles" "" "" "" 80891020102 1 236
"Wholesale, retail, repair of vehicles" "" "" "" 80891020102 2 237
"Wholesale, retail, repair of vehicles" "" "" "" 80891020102 3 238
"Public admin and defence"              "" "" "" 80891020102 4 239
"Wholesale, retail, repair of vehicles" "" "" "" 80891020102 5 240
"Accommodation and food services"       "" "" "" 80892010101 2 237
"Accommodation and food services"       "" "" "" 80892010101 3 238
"Accommodation and food services"       "" "" "" 80892010101 4 239
"Accommodation and food services"       "" "" "" 80892010101 5 240
"Accommodation and food services"       "" "" "" 80892010101 6 241
"Education"                             "" "" "" 80893010101 3 238
"Education"                             "" "" "" 80893010101 4 239
"Education"                             "" "" "" 80893010101 7 242
"Prof, scientific, technical activ."    "" "" "" 80893010103 3 238
"Prof, scientific, technical activ."    "" "" "" 80893010103 4 239
"Prof, scientific, technical activ."    "" "" "" 80893010103 5 240
"Prof, scientific, technical activ."    "" "" "" 80893010103 6 241
"Prof, scientific, technical activ."    "" "" "" 80893010103 7 242
"Health and social work"                "" "" "" 80991030101 1 236
"Health and social work"                "" "" "" 80991030101 2 237
"Health and social work"                "" "" "" 80991030101 3 238
"Health and social work"                "" "" "" 80991030101 4 239
"Health and social work"                "" "" "" 80991030101 5 240
"Construction"                          "" "" "" 80991030102 1 236
"Construction"                          "" "" "" 80991030102 2 237
"Construction"                          "" "" "" 80991030102 3 238
"Construction"                          "" "" "" 80991030102 4 239
"s not apply"                           "" "" "" 80993030101 3 238
"s not apply"                           "" "" "" 80993030101 4 239
"s not apply"                           "" "" "" 80993030101 5 240
"s not apply"                           "" "" "" 80993030101 6 241
"s not apply"                           "" "" "" 80993030101 7 242
"s not apply"                           "" "" "" 81094010102 4 239
"s not apply"                           "" "" "" 81094010102 5 240
"s not apply"                           "" "" "" 81094010102 6 241
"s not apply"                           "" "" "" 81094010102 7 242
"s not apply"                           "" "" "" 81094010102 8 243
"Health and social work"                "" "" "" 81094020101 4 239
"Health and social work"                "" "" "" 81094020101 5 240
"Health and social work"                "" "" "" 81094020101 6 241
"Health and social work"                "" "" "" 81094020101 7 242
"Health and social work"                "" "" "" 81094020101 8 243
"Manufacturing"                         "" "" "" 81094020102 4 239
"Manufacturing"                         "" "" "" 81094020102 7 242
"Manufacturing"                         "" "" "" 81094020102 8 243
"Wholesale, retail, repair of vehicles" "" "" "" 81193030101 3 238
"Wholesale, retail, repair of vehicles" "" "" "" 81193030101 4 239
"Wholesale, retail, repair of vehicles" "" "" "" 81193030101 5 240
"Wholesale, retail, repair of vehicles" "" "" "" 81193030101 6 241
"Wholesale, retail, repair of vehicles" "" "" "" 81193030101 7 242
"s not apply"                           "" "" "" 81392020101 2 237
"s not apply"                           "" "" "" 81392020101 3 238
"s not apply"                           "" "" "" 81392020101 4 239
"s not apply"                           "" "" "" 81392020101 5 240
"s not apply"                           "" "" "" 81392020101 6 241
"s not apply"                           "" "" "" 81392020102 2 237
"s not apply"                           "" "" "" 81392020102 3 238
"s not apply"                           "" "" "" 81392020102 4 239
"s not apply"                           "" "" "" 81392020102 5 240
"s not apply"                           "" "" "" 81392020102 6 241
"Construction"                          "" "" "" 81394020101 4 239
"Construction"                          "" "" "" 81394020101 7 242
"Construction"                          "" "" "" 81394020101 8 243
"s not apply"                           "" "" "" 81394030101 4 239
"s not apply"                           "" "" "" 81394030101 5 240
"s not apply"                           "" "" "" 81394030101 6 241
"s not apply"                           "" "" "" 81394030101 7 242
"s not apply"                           "" "" "" 81394030101 8 243
"s not apply"                           "" "" "" 81394030102 4 239
"s not apply"                           "" "" "" 81394030102 5 240
"s not apply"                           "" "" "" 81394030102 6 241
"s not apply"                           "" "" "" 81394030102 7 242
"s not apply"                           "" "" "" 81394030102 8 243
"Education"                             "" "" "" 90392010101 2 237
"Education"                             "" "" "" 90392010101 3 238
"Education"                             "" "" "" 90392010101 4 239
"s not apply"                           "" "" "" 90591020101 1 236
"s not apply"                           "" "" "" 90591020101 2 237
"s not apply"                           "" "" "" 90591020101 3 238
"s not apply"                           "" "" "" 90591020101 4 239
"s not apply"                           "" "" "" 90591020101 5 240
"Public admin and defence"              "" "" "" 90691030101 1 236
"Public admin and defence"              "" "" "" 90691030101 2 237
"Public admin and defence"              "" "" "" 90691030101 3 238
"Public admin and defence"              "" "" "" 90691030101 4 239
"Public admin and defence"              "" "" "" 90691030101 5 240
"Prof, scientific, technical activ."    "" "" "" 90691030102 1 236
"Prof, scientific, technical activ."    "" "" "" 90691030102 2 237
"Prof, scientific, technical activ."    "" "" "" 90691030102 3 238
"Prof, scientific, technical activ."    "" "" "" 90691030102 4 239
"Prof, scientific, technical activ."    "" "" "" 90691030102 5 240
"Health and social work"                "" "" "" 90691050101 1 236
"Health and social work"                "" "" "" 90691050101 2 237
"Health and social work"                "" "" "" 90691050101 3 238
"Health and social work"                "" "" "" 90691050101 4 239
"Health and social work"                "" "" "" 90691050101 5 240
"s not apply"                           "" "" "" 90892050101 2 237
"s not apply"                           "" "" "" 90892050101 3 238
"s not apply"                           "" "" "" 90892050101 4 239
"s not apply"                           "" "" "" 90892050101 5 240
end
format %tq qdate
label values quarter quarter
label def quarter 1 "Jan-Mar 2019", modify
label def quarter 2 "April-June 2019", modify
label def quarter 3 "July-Sep 2019", modify
label def quarter 4 "Oct-Des 2019", modify
label def quarter 5 "Jan-Mar 2020", modify
label def quarter 6 "April-June 2020", modify
label def quarter 7 "July-Sep 2020", modify
label def quarter 8 "Oct-Des 2020", modify


                  So, I noted that I just got matched for three industries only "Constriction Education, and Manufacturing." and the rest industries got missing value

                  Note that the variable industry is Factor variable which has 21 industry.


                  There is the panel data :
                  Code:
                  * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter Inds07m)
10493040101 3 -9
10493040101 4 -9
10493040101 5 -9
10493040101 6 -9
10493040101 7 -9
10694020101 4 -9
10694020101 5 -9
10694020101 6 -9
10694020101 7 -9
10694020101 8 -9
10694020102 4 -9
10694020102 5 -9
10694020102 6 -9
10694020102 7 -9
10694020102 8 -9
10792030101 2 -9
10792030101 3 -9
10792030101 4 -9
10792030101 5 -9
10792030101 6 -9
10793010101 3 15
10793010101 4 15
10793010101 5 15
10793010101 6 15
10793010101 7 15
10794010101 4  9
10794010101 5  9
10794010101 6  9
10794010101 7  9
10794010101 8  9
10794010102 4  9
10794010102 5  9
10794010102 6  9
10794010102 7  9
10794010102 8  9
10993020101 3 16
10993020101 4 16
10993020101 5 16
10993020101 6 16
10993020101 7 -9
10993020102 3 15
10993020102 4 15
10993020102 5 15
10993020102 6 15
10993020102 7 15
11091010101 1 16
11091010101 2 16
11091010101 3 16
11091010101 4 16
11091010101 5 16
11093030101 3 -9
11093030101 4 -9
11093030101 5 -9
11093030101 6 -9
11093030101 7 -9
11094010101 4 15
11094010101 5 -9
11094010101 6 -9
11094010101 7 -9
11094010101 8 -9
11094010102 4 17
11094010102 5 17
11094010102 6 17
11094010102 7 17
11094010102 8 17
11291020101 1 -9
11291020101 2 -9
11291020101 3 -9
11291020101 4 -9
11291020101 5 -9
11292020101 2  7
11292020101 3  7
11292020101 4  7
11292020101 5  7
11292020101 6  7
11294030101 4 15
11294030101 5 15
11294030101 6 15
11294030101 7 15
11294030101 8 15
11294030102 4 -9
11294030102 5 -9
11294030102 6 -9
11294030102 7 -9
11294030102 8 -9
20191020101 1 16
20191020101 2 16
20191020101 3 16
20191020101 4 16
20191020101 5 16
20191020102 1  7
20191020102 2  7
20191020102 3  7
20191020102 4  7
20191020102 5  7
20191040101 1 19
20191040101 2 19
20191040101 3 19
20191040101 4 19
20191040101 5 19
end
label values quarter quarter
label def quarter 1 "Jan-Mar 2019", modify
label def quarter 2 "April-June 2019", modify
label def quarter 3 "July-Sep 2019", modify
label def quarter 4 "Oct-Des 2019", modify
label def quarter 5 "Jan-Mar 2020", modify
label def quarter 6 "April-June 2020", modify
label def quarter 7 "July-Sep 2020", modify
label def quarter 8 "Oct-Des 2020", modify
label values Inds07m Inds07m5
label def Inds07m5 -9 "Does not apply", modify
label def Inds07m5 7 "G  Wholesale, retail, repair of vehicles", modify
label def Inds07m5 9 "I  Accommodation and food services", modify
label def Inds07m5 15 "O  Public admin and defence", modify
label def Inds07m5 16 "P  Education", modify
label def Inds07m5 17 "Q  Health and social work", modify
label def Inds07m5 19 "S  Other service activities", modify

                  and this is the Trade data:
                  Code:
                  * Example generated by -dataex-. For more info, type help dataex
clear
input str24 date str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril
"23/03/2020 to 05/04/2020" "Manufacturing"                                                        "77.2%"  "0.2%" "22.7%"
"23/03/2020 to 05/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "92.0%"  "0.0%" "8.0%" 
"23/03/2020 to 05/04/2020" "Construction"                                                         "70.9%"  "0.0%" "29.1%"
"23/03/2020 to 05/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "72.8%"  "0.2%" "27.0%"
"23/03/2020 to 05/04/2020" "Accommodation And Food Service Activities"                            "18.4%"  "0.4%" "81.2%"
"23/03/2020 to 05/04/2020" "Transportation And Storage"                                           "92.3%"  "0.0%" "7.7%" 
"23/03/2020 to 05/04/2020" "Information And Communication"                                        "94.4%"  "0.5%" "5.1%" 
"23/03/2020 to 05/04/2020" "Professional, Scientific And Technical Activities"                    "96.2%"  "0.1%" "3.7%" 
"23/03/2020 to 05/04/2020" "Administrative And Support Service Activities"                        "89.7%"  "0.7%" "9.6%" 
"23/03/2020 to 05/04/2020" "Education"                                                            "85.8%"  "0.3%" "13.8%"
"23/03/2020 to 05/04/2020" "Human Health And Social Work Activities"                              "95.6%"  "0.9%" "3.5%" 
"23/03/2020 to 05/04/2020" "Arts, Entertainment And Recreation"                                   "16.7%"  "1.1%" "82.2%"
"23/03/2020 to 05/04/2020" "All Industries"                                                       "75.4%"  "0.3%" "24.3%"
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        "79.0%"  "0.4%" "20.6%"
"06/04/2020 to 19/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "90.0%"  "0.0%" "10.0%"
"06/04/2020 to 19/04/2020" "Construction"                                                         "73.2%"  "0.7%" "26.1%"
"06/04/2020 to 19/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "75.2%"  "0.5%" "24.3%"
"06/04/2020 to 19/04/2020" "Accommodation And Food Service Activities"                            "18.2%"  "1.2%" "80.6%"
"06/04/2020 to 19/04/2020" "Transportation And Storage"                                           "91.5%"  "0.0%" "8.5%" 
"06/04/2020 to 19/04/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%" 
"06/04/2020 to 19/04/2020" "Professional, Scientific And Technical Activities"                    "96.7%"  "0.3%" "3.0%" 
"06/04/2020 to 19/04/2020" "Administrative And Support Service Activities"                        "91.5%"  "0.4%" "8.1%" 
"06/04/2020 to 19/04/2020" "Education"                                                            "86.8%"  "0.6%" "12.6%"
"06/04/2020 to 19/04/2020" "Human Health And Social Work Activities"                              "93.8%"  "1.4%" "4.9%" 
"06/04/2020 to 19/04/2020" "Arts, Entertainment And Recreation"                                   "20.5%"  "0.0%" "79.5%"
"06/04/2020 to 19/04/2020" "All Industries"                                                       "76.7%"  "0.5%" "22.8%"
"20/04/2020 to 03/05/2020" "Manufacturing"                                                        "85.9%"  "0.2%" "13.9%"
"20/04/2020 to 03/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "97.1%"  "0.0%" "2.9%" 
"20/04/2020 to 03/05/2020" "Construction"                                                         "78.1%"  "0.3%" "21.6%"
"20/04/2020 to 03/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "79.9%"  "0.1%" "20.0%"
"20/04/2020 to 03/05/2020" "Transportation And Storage"                                           "95.0%"  "0.0%" "5.0%" 
"20/04/2020 to 03/05/2020" "Accommodation And Food Service Activities"                            "21.9%"  "0.0%" "78.1%"
"20/04/2020 to 03/05/2020" "Information And Communication"                                        "95.0%"  "0.5%" "4.5%" 
"20/04/2020 to 03/05/2020" "Real Estate Activities"                                               "93.0%"  "1.4%" "5.6%" 
"20/04/2020 to 03/05/2020" "Professional, Scientific And Technical Activities"                    "95.5%"  "1.1%" "3.4%" 
"20/04/2020 to 03/05/2020" "Administrative And Support Service Activities"                        "92.7%"  "0.2%" "7.1%" 
"20/04/2020 to 03/05/2020" "Education"                                                            "89.2%"  "1.0%" "9.8%" 
"20/04/2020 to 03/05/2020" "Human Health And Social Work Activities"                              "95.4%"  "0.8%" "3.8%" 
"20/04/2020 to 03/05/2020" "Arts, Entertainment And Recreation"                                   "19.6%"  "0.3%" "80.1%"
"20/04/2020 to 03/05/2020" "All Industries"                                                       "79.3%"  "0.4%" "20.3%"
"04/05/2020 to 17/05/2020" "Manufacturing"                                                        "92.3%"  "0.3%" "7.4%" 
"04/05/2020 to 17/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "95.5%"  "0.0%" "4.5%" 
"04/05/2020 to 17/05/2020" "Construction"                                                         "80.4%"  "0.5%" "19.1%"
"04/05/2020 to 17/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "85.0%"  "0.2%" "14.8%"
"04/05/2020 to 17/05/2020" "Transportation And Storage"                                           "94.5%"  "0.0%" "5.5%" 
"04/05/2020 to 17/05/2020" "Accommodation And Food Service Activities"                            "25.4%"  "0.5%" "74.1%"
"04/05/2020 to 17/05/2020" "Information And Communication"                                        "93.8%"  "0.4%" "5.8%" 
"04/05/2020 to 17/05/2020" "Real Estate Activities"                                               "95.7%"  "1.4%" "2.9%" 
"04/05/2020 to 17/05/2020" "Professional, Scientific And Technical Activities"                    "97.1%"  "0.3%" "2.6%" 
"04/05/2020 to 17/05/2020" "Administrative And Support Service Activities"                        "91.1%"  "0.3%" "8.6%" 
"04/05/2020 to 17/05/2020" "Education"                                                            "90.3%"  "0.3%" "9.4%" 
"04/05/2020 to 17/05/2020" "Human Health And Social Work Activities"                              "95.9%"  "0.8%" "3.3%" 
"04/05/2020 to 17/05/2020" "Arts, Entertainment And Recreation"                                   "25.0%"  "0.4%" "74.6%"
"04/05/2020 to 17/05/2020" "All Industries"                                                       "82.0%"  "0.3%" "17.7%"
"18/05/2020 to 31/05/2020" "Manufacturing"                                                        "95.1%"  "0.1%" "4.8%" 
"18/05/2020 to 31/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "100.0%" "0.0%" "0.0%" 
"18/05/2020 to 31/05/2020" "Construction"                                                         "82.6%"  "0.4%" "17.0%"
"18/05/2020 to 31/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "88.2%"  "0.3%" "11.5%"
"18/05/2020 to 31/05/2020" "Transportation And Storage"                                           "95.7%"  "0.3%" "4.0%" 
"18/05/2020 to 31/05/2020" "Accommodation And Food Service Activities"                            "34.4%"  "0.3%" "65.3%"
"18/05/2020 to 31/05/2020" "Information And Communication"                                        "94.5%"  "0.7%" "4.8%" 
"18/05/2020 to 31/05/2020" "Real Estate Activities"                                               "94.0%"  "1.2%" "4.8%" 
"18/05/2020 to 31/05/2020" "Professional, Scientific And Technical Activities"                    "96.6%"  "1.1%" "2.4%" 
"18/05/2020 to 31/05/2020" "Administrative And Support Service Activities"                        "91.0%"  "0.3%" "8.7%" 
"18/05/2020 to 31/05/2020" "Education"                                                            "89.3%"  "0.3%" "10.4%"
"18/05/2020 to 31/05/2020" "Human Health And Social Work Activities"                              "94.7%"  "0.8%" "4.5%" 
"18/05/2020 to 31/05/2020" "Arts, Entertainment And Recreation"                                   "29.9%"  "0.3%" "69.8%"
"18/05/2020 to 31/05/2020" "All Industries"                                                       "84.1%"  "0.4%" "15.5%"
end


                  I don't know if I made mastic in my merge or if there is something else.

                  I hope this clear



                  Comment

                  • #10
                    I see the problem. The way you have named the sectors is different in the two data sets, so they are not matching with each other. For example, in one data set you have "Wholesale, retail, repair of vehicles" but in the other you have "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles." In order to get things to match up properly, the descriptors must be identical in all respects. Same words, same punctuation, same spacing, same use of upper and lower case, same spelling.... You will have to change one of the data sets to use the same nomenclature the other uses. That will involve a series of -replace- statements.

                    Also, while you refer to industry as a factor variable, you have it that way only in the panel data set. In the trade data set it is a string variable. And, actually, in this case, the string variable is what we need to rely on. The conversion to a factor variable in the panel data produces a particular correspondence between numbers and the names of the sectors, but there is no guarantee that the same correspondence would occur if you were to -encode- the sector variable in the trade data. Then you would be in an even worse situation: you would get "matches" but many of them would be incorrect matches!

                    So, decide which data set's nomenclature for industry you want to work with, and then change the other data set to correspond go that. Then everything will join up correctly.

                    Comment

                    • #11
                      Originally posted by Clyde Schechter View Post
                      I see the problem. The way you have named the sectors is different in the two data sets, so they are not matching with each other. For example, in one data set you have "Wholesale, retail, repair of vehicles" but in the other you have "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles." In order to get things to match up properly, the descriptors must be identical in all respects. Same words, same punctuation, same spacing, same use of upper and lower case, same spelling.... You will have to change one of the data sets to use the same nomenclature the other uses. That will involve a series of -replace- statements.

                      Also, while you refer to industry as a factor variable, you have it that way only in the panel data set. In the trade data set it is a string variable. And, actually, in this case, the string variable is what we need to rely on. The conversion to a factor variable in the panel data produces a particular correspondence between numbers and the names of the sectors, but there is no guarantee that the same correspondence would occur if you were to -encode- the sector variable in the trade data. Then you would be in an even worse situation: you would get "matches" but many of them would be incorrect matches!

                      So, decide which data set's nomenclature for industry you want to work with, and then change the other data set to correspond go that. Then everything will join up correctly.
                      I appreciate this clarification. And sorry to bother you, but I did not get this, and I might made something wrong. what I did is that: I changed the sector variable define in the panel data to be identical to trade data by this command
                      Code:
                      label define Inds07m5 -9 "Does not apply" -8 "No answer" 1 "Agriculture, forestry and fishing" 2 "Mining and quarrying" 3 "Manufacturing" 4 "Electricity, gas, air cond supply" 5 "Water Supply, Sewerage, Waste Management And Remediation Activities" 6 "Construction" 7 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 8 "Transportation And Storage" 9 "Accommodation And Food Service Activities" 10 "Information And Communication" 11 "Financial and insurance activities" 12 "Real estate activities" 13 "Professional, Scientific And Technical Activities" 14 "Administrative And Support Service Activities" 15 "Public admin and defence" 16 "Education" 17 "Human Health And Social Work Activities" 18 "Arts, Entertainment And Recreation" 19 "Other service activities" 20 "Households as employers" 21 "Extraterritorial organisations", replace
                      Then I followed your suggestion above to merge the two datasets :
                      Code:
                       
 //  PREPARE PANEL DATA SET FOR MERGER use `panel' gen qdate = tq(2018q4) + quarter format qdate %tq decode Inds07m, gen(industry) replace industry = substr(industry, 4, .) format PERSID %15.0f isid PERSID qdate, sort tempfile cleaned_treat save `cleaned_treat'

                      However, I noticed that the generated (industry) has some error with the name label (it deletes some letters in each industry), so I think that does not give me a correct join-up. Example after I run the prepare panel data command :
                      Code:
                      * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID float qdate byte Inds07m str68 industry
10493040101 238 -9 "s not apply"                                                      
10493040101 239 -9 "s not apply"                                                      
10493040101 240 -9 "s not apply"                                                      
10493040101 241 -9 "s not apply"                                                      
10493040101 242 -9 "s not apply"                                                      
10694020101 239 -9 "s not apply"                                                      
10694020101 240 -9 "s not apply"                                                      
10694020101 241 -9 "s not apply"                                                      
10694020101 242 -9 "s not apply"                                                      
10694020101 243 -9 "s not apply"                                                      
10694020102 239 -9 "s not apply"                                                      
10694020102 240 -9 "s not apply"                                                      
10694020102 241 -9 "s not apply"                                                      
10694020102 242 -9 "s not apply"                                                      
10694020102 243 -9 "s not apply"                                                      
10792030101 237 -9 "s not apply"                                                      
10792030101 238 -9 "s not apply"                                                      
10792030101 239 -9 "s not apply"                                                      
10792030101 240 -9 "s not apply"                                                      
10792030101 241 -9 "s not apply"                                                      
10793010101 238 15 "lic admin and defence"                                            
10793010101 239 15 "lic admin and defence"                                            
10793010101 240 15 "lic admin and defence"                                            
10793010101 241 15 "lic admin and defence"                                            
10793010101 242 15 "lic admin and defence"                                            
10794010101 239  9 "ommodation And Food Service Activities"                           
10794010101 240  9 "ommodation And Food Service Activities"                           
10794010101 241  9 "ommodation And Food Service Activities"                           
10794010101 242  9 "ommodation And Food Service Activities"                           
10794010101 243  9 "ommodation And Food Service Activities"                           
10794010102 239  9 "ommodation And Food Service Activities"                           
10794010102 240  9 "ommodation And Food Service Activities"                           
10794010102 241  9 "ommodation And Food Service Activities"                           
10794010102 242  9 "ommodation And Food Service Activities"                           
10794010102 243  9 "ommodation And Food Service Activities"                           
10993020101 238 16 "cation"                                                           
10993020101 239 16 "cation"                                                           
10993020101 240 16 "cation"                                                           
10993020101 241 16 "cation"                                                           
10993020101 242 -9 "s not apply"                                                      
10993020102 238 15 "lic admin and defence"                                            
10993020102 239 15 "lic admin and defence"                                            
10993020102 240 15 "lic admin and defence"                                            
10993020102 241 15 "lic admin and defence"                                            
10993020102 242 15 "lic admin and defence"                                            
11091010101 236 16 "cation"                                                           
11091010101 237 16 "cation"                                                           
11091010101 238 16 "cation"                                                           
11091010101 239 16 "cation"                                                           
11091010101 240 16 "cation"                                                           
11093030101 238 -9 "s not apply"                                                      
11093030101 239 -9 "s not apply"                                                      
11093030101 240 -9 "s not apply"                                                      
11093030101 241 -9 "s not apply"                                                      
11093030101 242 -9 "s not apply"                                                      
11094010101 239 15 "lic admin and defence"                                            
11094010101 240 -9 "s not apply"                                                      
11094010101 241 -9 "s not apply"                                                      
11094010101 242 -9 "s not apply"                                                      
11094010101 243 -9 "s not apply"                                                      
11094010102 239 17 "an Health And Social Work Activities"                             
11094010102 240 17 "an Health And Social Work Activities"                             
11094010102 241 17 "an Health And Social Work Activities"                             
11094010102 242 17 "an Health And Social Work Activities"                             
11094010102 243 17 "an Health And Social Work Activities"                             
11291020101 236 -9 "s not apply"                                                      
11291020101 237 -9 "s not apply"                                                      
11291020101 238 -9 "s not apply"                                                      
11291020101 239 -9 "s not apply"                                                      
11291020101 240 -9 "s not apply"                                                      
11292020101 237  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
11292020101 238  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
11292020101 239  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
11292020101 240  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
11292020101 241  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
11294030101 239 15 "lic admin and defence"                                            
11294030101 240 15 "lic admin and defence"                                            
11294030101 241 15 "lic admin and defence"                                            
11294030101 242 15 "lic admin and defence"                                            
11294030101 243 15 "lic admin and defence"                                            
11294030102 239 -9 "s not apply"                                                      
11294030102 240 -9 "s not apply"                                                      
11294030102 241 -9 "s not apply"                                                      
11294030102 242 -9 "s not apply"                                                      
11294030102 243 -9 "s not apply"                                                      
20191020101 236 16 "cation"                                                           
20191020101 237 16 "cation"                                                           
20191020101 238 16 "cation"                                                           
20191020101 239 16 "cation"                                                           
20191020101 240 16 "cation"                                                           
20191020102 236  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
20191020102 237  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
20191020102 238  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
20191020102 239  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
20191020102 240  7 "lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles"
20191040101 236 19 "er service activities"                                            
20191040101 237 19 "er service activities"                                            
20191040101 238 19 "er service activities"                                            
20191040101 239 19 "er service activities"                                            
20191040101 240 19 "er service activities"                                            
end
format %tq qdate
label values Inds07m Inds07m5
label def Inds07m5 -9 "Does not apply", modify
label def Inds07m5 7 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles", modify
label def Inds07m5 9 "Accommodation And Food Service Activities", modify
label def Inds07m5 15 "Public admin and defence", modify
label def Inds07m5 16 "Education", modify
label def Inds07m5 17 "Human Health And Social Work Activities", modify
label def Inds07m5 19 "Other service activities", modify

                      Therefore, in the end, I did not get matched and did not correctly join up :

                      Code:
                      * Example generated by -dataex-. For more info, type help dataex
clear
input str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril float(date1 qdate1 date2 qdate2) double PERSID float qdate byte Inds07m
"s not apply"                                                       "" "" "" . . . . 10493040101 238 -9
"s not apply"                                                       "" "" "" . . . . 10493040101 239 -9
"s not apply"                                                       "" "" "" . . . . 10493040101 240 -9
"s not apply"                                                       "" "" "" . . . . 10493040101 241 -9
"s not apply"                                                       "" "" "" . . . . 10493040101 242 -9
"s not apply"                                                       "" "" "" . . . . 10694020101 239 -9
"s not apply"                                                       "" "" "" . . . . 10694020101 240 -9
"s not apply"                                                       "" "" "" . . . . 10694020101 241 -9
"s not apply"                                                       "" "" "" . . . . 10694020101 242 -9
"s not apply"                                                       "" "" "" . . . . 10694020101 243 -9
"s not apply"                                                       "" "" "" . . . . 10694020102 239 -9
"s not apply"                                                       "" "" "" . . . . 10694020102 240 -9
"s not apply"                                                       "" "" "" . . . . 10694020102 241 -9
"s not apply"                                                       "" "" "" . . . . 10694020102 242 -9
"s not apply"                                                       "" "" "" . . . . 10694020102 243 -9
"s not apply"                                                       "" "" "" . . . . 10792030101 237 -9
"s not apply"                                                       "" "" "" . . . . 10792030101 238 -9
"s not apply"                                                       "" "" "" . . . . 10792030101 239 -9
"s not apply"                                                       "" "" "" . . . . 10792030101 240 -9
"s not apply"                                                       "" "" "" . . . . 10792030101 241 -9
"lic admin and defence"                                             "" "" "" . . . . 10793010101 238 15
"lic admin and defence"                                             "" "" "" . . . . 10793010101 239 15
"lic admin and defence"                                             "" "" "" . . . . 10793010101 240 15
"lic admin and defence"                                             "" "" "" . . . . 10793010101 241 15
"lic admin and defence"                                             "" "" "" . . . . 10793010101 242 15
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010101 239  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010101 240  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010101 241  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010101 242  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010101 243  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010102 239  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010102 240  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010102 241  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010102 242  9
"ommodation And Food Service Activities"                            "" "" "" . . . . 10794010102 243  9
"cation"                                                            "" "" "" . . . . 10993020101 238 16
"cation"                                                            "" "" "" . . . . 10993020101 239 16
"cation"                                                            "" "" "" . . . . 10993020101 240 16
"cation"                                                            "" "" "" . . . . 10993020101 241 16
"s not apply"                                                       "" "" "" . . . . 10993020101 242 -9
"lic admin and defence"                                             "" "" "" . . . . 10993020102 238 15
"lic admin and defence"                                             "" "" "" . . . . 10993020102 239 15
"lic admin and defence"                                             "" "" "" . . . . 10993020102 240 15
"lic admin and defence"                                             "" "" "" . . . . 10993020102 241 15
"lic admin and defence"                                             "" "" "" . . . . 10993020102 242 15
"cation"                                                            "" "" "" . . . . 11091010101 236 16
"cation"                                                            "" "" "" . . . . 11091010101 237 16
"cation"                                                            "" "" "" . . . . 11091010101 238 16
"cation"                                                            "" "" "" . . . . 11091010101 239 16
"cation"                                                            "" "" "" . . . . 11091010101 240 16
"s not apply"                                                       "" "" "" . . . . 11093030101 238 -9
"s not apply"                                                       "" "" "" . . . . 11093030101 239 -9
"s not apply"                                                       "" "" "" . . . . 11093030101 240 -9
"s not apply"                                                       "" "" "" . . . . 11093030101 241 -9
"s not apply"                                                       "" "" "" . . . . 11093030101 242 -9
"lic admin and defence"                                             "" "" "" . . . . 11094010101 239 15
"s not apply"                                                       "" "" "" . . . . 11094010101 240 -9
"s not apply"                                                       "" "" "" . . . . 11094010101 241 -9
"s not apply"                                                       "" "" "" . . . . 11094010101 242 -9
"s not apply"                                                       "" "" "" . . . . 11094010101 243 -9
"an Health And Social Work Activities"                              "" "" "" . . . . 11094010102 239 17
"an Health And Social Work Activities"                              "" "" "" . . . . 11094010102 240 17
"an Health And Social Work Activities"                              "" "" "" . . . . 11094010102 241 17
"an Health And Social Work Activities"                              "" "" "" . . . . 11094010102 242 17
"an Health And Social Work Activities"                              "" "" "" . . . . 11094010102 243 17
"s not apply"                                                       "" "" "" . . . . 11291020101 236 -9
"s not apply"                                                       "" "" "" . . . . 11291020101 237 -9
"s not apply"                                                       "" "" "" . . . . 11291020101 238 -9
"s not apply"                                                       "" "" "" . . . . 11291020101 239 -9
"s not apply"                                                       "" "" "" . . . . 11291020101 240 -9
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 11292020101 239  7
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 11292020101 240  7
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 11292020101 241  7
"lic admin and defence"                                             "" "" "" . . . . 11294030101 241 15
"lic admin and defence"                                             "" "" "" . . . . 11294030101 242 15
"lic admin and defence"                                             "" "" "" . . . . 11294030101 243 15
"s not apply"                                                       "" "" "" . . . . 11294030102 239 -9
"s not apply"                                                       "" "" "" . . . . 11294030102 240 -9
"s not apply"                                                       "" "" "" . . . . 11294030102 241 -9
"s not apply"                                                       "" "" "" . . . . 11294030102 242 -9
"s not apply"                                                       "" "" "" . . . . 11294030102 243 -9
"cation"                                                            "" "" "" . . . . 20191020101 239 16
"cation"                                                            "" "" "" . . . . 20191020101 240 16
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 20191020102 236  7
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 20191020102 237  7
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 20191020102 238  7
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 20191020102 239  7
"lesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "" "" "" . . . . 20191020102 240  7

end
format %td date1
format %tq qdate1
format %td date2
format %tq qdate2
format %tq qdate
label values Inds07m Inds07m5
label def Inds07m5 -9 "Does not apply", modify
label def Inds07m5 7 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles", modify
label def Inds07m5 9 "Accommodation And Food Service Activities", modify
label def Inds07m5 15 "Public admin and defence", modify
label def Inds07m5 16 "Education", modify
label def Inds07m5 17 "Human Health And Social Work Activities", modify
label def Inds07m5 19 "Other service activities", modify



                      I think there is something to fix in the beginning, I tried a different way to solve this, but I failed to do it.





                      Comment

                      • #12
                        This is the original panel data after I renamed the industry variable to be the same as the trade data :
                        Code:
                        * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter Inds07m)
10493040101 3 -9
10493040101 4 -9
10493040101 5 -9
10493040101 6 -9
10493040101 7 -9
10694020101 4 -9
10694020101 5 -9
10694020101 6 -9
10694020101 7 -9
10694020101 8 -9
10694020102 4 -9
10694020102 5 -9
10694020102 6 -9
10694020102 7 -9
10694020102 8 -9
10792030101 2 -9
10792030101 3 -9
10792030101 4 -9
10792030101 5 -9
10792030101 6 -9
10793010101 3 15
10793010101 4 15
10793010101 5 15
10793010101 6 15
10793010101 7 15
10794010101 4  9
10794010101 5  9
10794010101 6  9
10794010101 7  9
10794010101 8  9
10794010102 4  9
10794010102 5  9
10794010102 6  9
10794010102 7  9
10794010102 8  9
10993020101 3 16
10993020101 4 16
10993020101 5 16
10993020101 6 16
10993020101 7 -9
10993020102 3 15
10993020102 4 15
10993020102 5 15
10993020102 6 15
10993020102 7 15
11091010101 1 16
11091010101 2 16
11091010101 3 16
11091010101 4 16
11091010101 5 16
11093030101 3 -9
11093030101 4 -9
11093030101 5 -9
11093030101 6 -9
11093030101 7 -9
11094010101 4 15
11094010101 5 -9
11094010101 6 -9
11094010101 7 -9
11094010101 8 -9
11094010102 4 17
11094010102 5 17
11094010102 6 17
11094010102 7 17
11094010102 8 17
11291020101 1 -9
11291020101 2 -9
11291020101 3 -9
11291020101 4 -9
11291020101 5 -9
11292020101 2  7
11292020101 3  7
11292020101 4  7
11292020101 5  7
11292020101 6  7
11294030101 4 15
11294030101 5 15
11294030101 6 15
11294030101 7 15
11294030101 8 15
11294030102 4 -9
11294030102 5 -9
11294030102 6 -9
11294030102 7 -9
11294030102 8 -9
20191020101 1 16
20191020101 2 16
20191020101 3 16
20191020101 4 16
20191020101 5 16
20191020102 1  7
20191020102 2  7
20191020102 3  7
20191020102 4  7
20191020102 5  7
20191040101 1 19
20191040101 2 19
20191040101 3 19
20191040101 4 19
20191040101 5 19
end
label values quarter quarter
label def quarter 1 "Jan-Mar 2019", modify
label def quarter 2 "April-June 2019", modify
label def quarter 3 "July-Sep 2019", modify
label def quarter 4 "Oct-Des 2019", modify
label def quarter 5 "Jan-Mar 2020", modify
label def quarter 6 "April-June 2020", modify
label def quarter 7 "July-Sep 2020", modify
label def quarter 8 "Oct-Des 2020", modify
label values Inds07m Inds07m5
label def Inds07m5 -9 "Does not apply", modify
label def Inds07m5 7 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles", modify
label def Inds07m5 9 "Accommodation And Food Service Activities", modify
label def Inds07m5 15 "Public admin and defence", modify
label def Inds07m5 16 "Education", modify
label def Inds07m5 17 "Human Health And Social Work Activities", modify
label def Inds07m5 19 "Other service activities", modify

                        And this is the trade data :
                        Code:
                        * Example generated by -dataex-. For more info, type help dataex
clear
input str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril float(date1 qdate1 date2 qdate2)
"Manufacturing"                                                        "77.2%"  "0.2%" "22.7%" 21997 240 22010 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "92.0%"  "0.0%" "8.0%"  21997 240 22010 241
"Construction"                                                         "70.9%"  "0.0%" "29.1%" 21997 240 22010 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "72.8%"  "0.2%" "27.0%" 21997 240 22010 241
"Accommodation And Food Service Activities"                            "18.4%"  "0.4%" "81.2%" 21997 240 22010 241
"Transportation And Storage"                                           "92.3%"  "0.0%" "7.7%"  21997 240 22010 241
"Information And Communication"                                        "94.4%"  "0.5%" "5.1%"  21997 240 22010 241
"Professional, Scientific And Technical Activities"                    "96.2%"  "0.1%" "3.7%"  21997 240 22010 241
"Administrative And Support Service Activities"                        "89.7%"  "0.7%" "9.6%"  21997 240 22010 241
"Education"                                                            "85.8%"  "0.3%" "13.8%" 21997 240 22010 241
"Human Health And Social Work Activities"                              "95.6%"  "0.9%" "3.5%"  21997 240 22010 241
"Arts, Entertainment And Recreation"                                   "16.7%"  "1.1%" "82.2%" 21997 240 22010 241
"All Industries"                                                       "75.4%"  "0.3%" "24.3%" 21997 240 22010 241
"Manufacturing"                                                        "79.0%"  "0.4%" "20.6%" 22011 241 22024 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "90.0%"  "0.0%" "10.0%" 22011 241 22024 241
"Construction"                                                         "73.2%"  "0.7%" "26.1%" 22011 241 22024 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "75.2%"  "0.5%" "24.3%" 22011 241 22024 241
"Accommodation And Food Service Activities"                            "18.2%"  "1.2%" "80.6%" 22011 241 22024 241
"Transportation And Storage"                                           "91.5%"  "0.0%" "8.5%"  22011 241 22024 241
"Information And Communication"                                        "95.0%"  "0.5%" "4.5%"  22011 241 22024 241
"Professional, Scientific And Technical Activities"                    "96.7%"  "0.3%" "3.0%"  22011 241 22024 241
"Administrative And Support Service Activities"                        "91.5%"  "0.4%" "8.1%"  22011 241 22024 241
"Education"                                                            "86.8%"  "0.6%" "12.6%" 22011 241 22024 241
"Human Health And Social Work Activities"                              "93.8%"  "1.4%" "4.9%"  22011 241 22024 241
"Arts, Entertainment And Recreation"                                   "20.5%"  "0.0%" "79.5%" 22011 241 22024 241
"All Industries"                                                       "76.7%"  "0.5%" "22.8%" 22011 241 22024 241
"Manufacturing"                                                        "85.9%"  "0.2%" "13.9%" 22025 241 22038 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "97.1%"  "0.0%" "2.9%"  22025 241 22038 241
"Construction"                                                         "78.1%"  "0.3%" "21.6%" 22025 241 22038 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "79.9%"  "0.1%" "20.0%" 22025 241 22038 241
"Transportation And Storage"                                           "95.0%"  "0.0%" "5.0%"  22025 241 22038 241
"Accommodation And Food Service Activities"                            "21.9%"  "0.0%" "78.1%" 22025 241 22038 241
"Information And Communication"                                        "95.0%"  "0.5%" "4.5%"  22025 241 22038 241
"Real Estate Activities"                                               "93.0%"  "1.4%" "5.6%"  22025 241 22038 241
"Professional, Scientific And Technical Activities"                    "95.5%"  "1.1%" "3.4%"  22025 241 22038 241
"Administrative And Support Service Activities"                        "92.7%"  "0.2%" "7.1%"  22025 241 22038 241
"Education"                                                            "89.2%"  "1.0%" "9.8%"  22025 241 22038 241
"Human Health And Social Work Activities"                              "95.4%"  "0.8%" "3.8%"  22025 241 22038 241
"Arts, Entertainment And Recreation"                                   "19.6%"  "0.3%" "80.1%" 22025 241 22038 241
"All Industries"                                                       "79.3%"  "0.4%" "20.3%" 22025 241 22038 241
"Manufacturing"                                                        "92.3%"  "0.3%" "7.4%"  22039 241 22052 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "95.5%"  "0.0%" "4.5%"  22039 241 22052 241
"Construction"                                                         "80.4%"  "0.5%" "19.1%" 22039 241 22052 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "85.0%"  "0.2%" "14.8%" 22039 241 22052 241
"Transportation And Storage"                                           "94.5%"  "0.0%" "5.5%"  22039 241 22052 241
"Accommodation And Food Service Activities"                            "25.4%"  "0.5%" "74.1%" 22039 241 22052 241
"Information And Communication"                                        "93.8%"  "0.4%" "5.8%"  22039 241 22052 241
"Real Estate Activities"                                               "95.7%"  "1.4%" "2.9%"  22039 241 22052 241
"Professional, Scientific And Technical Activities"                    "97.1%"  "0.3%" "2.6%"  22039 241 22052 241
"Administrative And Support Service Activities"                        "91.1%"  "0.3%" "8.6%"  22039 241 22052 241
"Education"                                                            "90.3%"  "0.3%" "9.4%"  22039 241 22052 241
"Human Health And Social Work Activities"                              "95.9%"  "0.8%" "3.3%"  22039 241 22052 241
"Arts, Entertainment And Recreation"                                   "25.0%"  "0.4%" "74.6%" 22039 241 22052 241
"All Industries"                                                       "82.0%"  "0.3%" "17.7%" 22039 241 22052 241
"Manufacturing"                                                        "95.1%"  "0.1%" "4.8%"  22053 241 22066 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "100.0%" "0.0%" "0.0%"  22053 241 22066 241
"Construction"                                                         "82.6%"  "0.4%" "17.0%" 22053 241 22066 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "88.2%"  "0.3%" "11.5%" 22053 241 22066 241
"Transportation And Storage"                                           "95.7%"  "0.3%" "4.0%"  22053 241 22066 241
"Accommodation And Food Service Activities"                            "34.4%"  "0.3%" "65.3%" 22053 241 22066 241
"Information And Communication"                                        "94.5%"  "0.7%" "4.8%"  22053 241 22066 241
"Real Estate Activities"                                               "94.0%"  "1.2%" "4.8%"  22053 241 22066 241
"Professional, Scientific And Technical Activities"                    "96.6%"  "1.1%" "2.4%"  22053 241 22066 241
"Administrative And Support Service Activities"                        "91.0%"  "0.3%" "8.7%"  22053 241 22066 241
"Education"                                                            "89.3%"  "0.3%" "10.4%" 22053 241 22066 241
"Human Health And Social Work Activities"                              "94.7%"  "0.8%" "4.5%"  22053 241 22066 241
"Arts, Entertainment And Recreation"                                   "29.9%"  "0.3%" "69.8%" 22053 241 22066 241
"All Industries"                                                       "84.1%"  "0.4%" "15.5%" 22053 241 22066 241
end
format %td date1
format %tq qdate1
format %td date2
format %tq qdate2


                        Thanks for your advice waiting for your comments

                        Comment

                        • #13
                          Code:
                          * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter Inds07m)
10493040101 3 -9
10493040101 4 -9
10493040101 5 -9
10493040101 6 -9
10493040101 7 -9
10694020101 4 -9
10694020101 5 -9
10694020101 6 -9
10694020101 7 -9
10694020101 8 -9
10694020102 4 -9
10694020102 5 -9
10694020102 6 -9
10694020102 7 -9
10694020102 8 -9
10792030101 2 -9
10792030101 3 -9
10792030101 4 -9
10792030101 5 -9
10792030101 6 -9
10793010101 3 15
10793010101 4 15
10793010101 5 15
10793010101 6 15
10793010101 7 15
10794010101 4  9
10794010101 5  9
10794010101 6  9
10794010101 7  9
10794010101 8  9
10794010102 4  9
10794010102 5  9
10794010102 6  9
10794010102 7  9
10794010102 8  9
10993020101 3 16
10993020101 4 16
10993020101 5 16
10993020101 6 16
10993020101 7 -9
10993020102 3 15
10993020102 4 15
10993020102 5 15
10993020102 6 15
10993020102 7 15
11091010101 1 16
11091010101 2 16
11091010101 3 16
11091010101 4 16
11091010101 5 16
11093030101 3 -9
11093030101 4 -9
11093030101 5 -9
11093030101 6 -9
11093030101 7 -9
11094010101 4 15
11094010101 5 -9
11094010101 6 -9
11094010101 7 -9
11094010101 8 -9
11094010102 4 17
11094010102 5 17
11094010102 6 17
11094010102 7 17
11094010102 8 17
11291020101 1 -9
11291020101 2 -9
11291020101 3 -9
11291020101 4 -9
11291020101 5 -9
11292020101 2  7
11292020101 3  7
11292020101 4  7
11292020101 5  7
11292020101 6  7
11294030101 4 15
11294030101 5 15
11294030101 6 15
11294030101 7 15
11294030101 8 15
11294030102 4 -9
11294030102 5 -9
11294030102 6 -9
11294030102 7 -9
11294030102 8 -9
20191020101 1 16
20191020101 2 16
20191020101 3 16
20191020101 4 16
20191020101 5 16
20191020102 1  7
20191020102 2  7
20191020102 3  7
20191020102 4  7
20191020102 5  7
20191040101 1 19
20191040101 2 19
20191040101 3 19
20191040101 4 19
20191040101 5 19
end
label values quarter quarter
label def quarter 1 "Jan-Mar 2019", modify
label def quarter 2 "April-June 2019", modify
label def quarter 3 "July-Sep 2019", modify
label def quarter 4 "Oct-Des 2019", modify
label def quarter 5 "Jan-Mar 2020", modify
label def quarter 6 "April-June 2020", modify
label def quarter 7 "July-Sep 2020", modify
label def quarter 8 "Oct-Des 2020", modify
label values Inds07m Inds07m5
label def Inds07m5 -9 "Does not apply", modify
label def Inds07m5 7 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles", modify
label def Inds07m5 9 "Accommodation And Food Service Activities", modify
label def Inds07m5 15 "Public admin and defence", modify
label def Inds07m5 16 "Education", modify
label def Inds07m5 17 "Human Health And Social Work Activities", modify
label def Inds07m5 19 "Other service activities", modify
tempfile panel
save `panel'

* Example generated by -dataex-. For more info, type help dataex
* Example generated by -dataex-. For more info, type help dataex
clear
input str68 industry str6 continuingtotrade str4 haspermanentlyceasedtrading str5 hastemporarilyclosedortemporaril float(date1 qdate1 date2 qdate2)
"Manufacturing"                                                        "77.2%"  "0.2%" "22.7%" 21997 240 22010 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "92.0%"  "0.0%" "8.0%"  21997 240 22010 241
"Construction"                                                         "70.9%"  "0.0%" "29.1%" 21997 240 22010 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "72.8%"  "0.2%" "27.0%" 21997 240 22010 241
"Accommodation And Food Service Activities"                            "18.4%"  "0.4%" "81.2%" 21997 240 22010 241
"Transportation And Storage"                                           "92.3%"  "0.0%" "7.7%"  21997 240 22010 241
"Information And Communication"                                        "94.4%"  "0.5%" "5.1%"  21997 240 22010 241
"Professional, Scientific And Technical Activities"                    "96.2%"  "0.1%" "3.7%"  21997 240 22010 241
"Administrative And Support Service Activities"                        "89.7%"  "0.7%" "9.6%"  21997 240 22010 241
"Education"                                                            "85.8%"  "0.3%" "13.8%" 21997 240 22010 241
"Human Health And Social Work Activities"                              "95.6%"  "0.9%" "3.5%"  21997 240 22010 241
"Arts, Entertainment And Recreation"                                   "16.7%"  "1.1%" "82.2%" 21997 240 22010 241
"All Industries"                                                       "75.4%"  "0.3%" "24.3%" 21997 240 22010 241
"Manufacturing"                                                        "79.0%"  "0.4%" "20.6%" 22011 241 22024 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "90.0%"  "0.0%" "10.0%" 22011 241 22024 241
"Construction"                                                         "73.2%"  "0.7%" "26.1%" 22011 241 22024 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "75.2%"  "0.5%" "24.3%" 22011 241 22024 241
"Accommodation And Food Service Activities"                            "18.2%"  "1.2%" "80.6%" 22011 241 22024 241
"Transportation And Storage"                                           "91.5%"  "0.0%" "8.5%"  22011 241 22024 241
"Information And Communication"                                        "95.0%"  "0.5%" "4.5%"  22011 241 22024 241
"Professional, Scientific And Technical Activities"                    "96.7%"  "0.3%" "3.0%"  22011 241 22024 241
"Administrative And Support Service Activities"                        "91.5%"  "0.4%" "8.1%"  22011 241 22024 241
"Education"                                                            "86.8%"  "0.6%" "12.6%" 22011 241 22024 241
"Human Health And Social Work Activities"                              "93.8%"  "1.4%" "4.9%"  22011 241 22024 241
"Arts, Entertainment And Recreation"                                   "20.5%"  "0.0%" "79.5%" 22011 241 22024 241
"All Industries"                                                       "76.7%"  "0.5%" "22.8%" 22011 241 22024 241
"Manufacturing"                                                        "85.9%"  "0.2%" "13.9%" 22025 241 22038 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "97.1%"  "0.0%" "2.9%"  22025 241 22038 241
"Construction"                                                         "78.1%"  "0.3%" "21.6%" 22025 241 22038 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "79.9%"  "0.1%" "20.0%" 22025 241 22038 241
"Transportation And Storage"                                           "95.0%"  "0.0%" "5.0%"  22025 241 22038 241
"Accommodation And Food Service Activities"                            "21.9%"  "0.0%" "78.1%" 22025 241 22038 241
"Information And Communication"                                        "95.0%"  "0.5%" "4.5%"  22025 241 22038 241
"Real Estate Activities"                                               "93.0%"  "1.4%" "5.6%"  22025 241 22038 241
"Professional, Scientific And Technical Activities"                    "95.5%"  "1.1%" "3.4%"  22025 241 22038 241
"Administrative And Support Service Activities"                        "92.7%"  "0.2%" "7.1%"  22025 241 22038 241
"Education"                                                            "89.2%"  "1.0%" "9.8%"  22025 241 22038 241
"Human Health And Social Work Activities"                              "95.4%"  "0.8%" "3.8%"  22025 241 22038 241
"Arts, Entertainment And Recreation"                                   "19.6%"  "0.3%" "80.1%" 22025 241 22038 241
"All Industries"                                                       "79.3%"  "0.4%" "20.3%" 22025 241 22038 241
"Manufacturing"                                                        "92.3%"  "0.3%" "7.4%"  22039 241 22052 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "95.5%"  "0.0%" "4.5%"  22039 241 22052 241
"Construction"                                                         "80.4%"  "0.5%" "19.1%" 22039 241 22052 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "85.0%"  "0.2%" "14.8%" 22039 241 22052 241
"Transportation And Storage"                                           "94.5%"  "0.0%" "5.5%"  22039 241 22052 241
"Accommodation And Food Service Activities"                            "25.4%"  "0.5%" "74.1%" 22039 241 22052 241
"Information And Communication"                                        "93.8%"  "0.4%" "5.8%"  22039 241 22052 241
"Real Estate Activities"                                               "95.7%"  "1.4%" "2.9%"  22039 241 22052 241
"Professional, Scientific And Technical Activities"                    "97.1%"  "0.3%" "2.6%"  22039 241 22052 241
"Administrative And Support Service Activities"                        "91.1%"  "0.3%" "8.6%"  22039 241 22052 241
"Education"                                                            "90.3%"  "0.3%" "9.4%"  22039 241 22052 241
"Human Health And Social Work Activities"                              "95.9%"  "0.8%" "3.3%"  22039 241 22052 241
"Arts, Entertainment And Recreation"                                   "25.0%"  "0.4%" "74.6%" 22039 241 22052 241
"All Industries"                                                       "82.0%"  "0.3%" "17.7%" 22039 241 22052 241
"Manufacturing"                                                        "95.1%"  "0.1%" "4.8%"  22053 241 22066 241
"Water Supply, Sewerage, Waste Management And Remediation Activities"  "100.0%" "0.0%" "0.0%"  22053 241 22066 241
"Construction"                                                         "82.6%"  "0.4%" "17.0%" 22053 241 22066 241
"Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "88.2%"  "0.3%" "11.5%" 22053 241 22066 241
"Transportation And Storage"                                           "95.7%"  "0.3%" "4.0%"  22053 241 22066 241
"Accommodation And Food Service Activities"                            "34.4%"  "0.3%" "65.3%" 22053 241 22066 241
"Information And Communication"                                        "94.5%"  "0.7%" "4.8%"  22053 241 22066 241
"Real Estate Activities"                                               "94.0%"  "1.2%" "4.8%"  22053 241 22066 241
"Professional, Scientific And Technical Activities"                    "96.6%"  "1.1%" "2.4%"  22053 241 22066 241
"Administrative And Support Service Activities"                        "91.0%"  "0.3%" "8.7%"  22053 241 22066 241
"Education"                                                            "89.3%"  "0.3%" "10.4%" 22053 241 22066 241
"Human Health And Social Work Activities"                              "94.7%"  "0.8%" "4.5%"  22053 241 22066 241
"Arts, Entertainment And Recreation"                                   "29.9%"  "0.3%" "69.8%" 22053 241 22066 241
"All Industries"                                                       "84.1%"  "0.4%" "15.5%" 22053 241 22066 241
end
format %td date1
format %tq qdate1
format %td date2
format %tq qdate2
tempfile trade
save `trade'



//  PREPARE PANEL DATA SET FOR MERGER
use `panel'
gen qdate = tq(2018q4) + quarter
format qdate %tq
decode Inds07m, gen(industry)
drop Inds07m
format PERSID %15.0f
isid PERSID qdate, sort
tempfile cleaned_treat
save `cleaned_treat'


//  NOW JOIN EACH OBSERVATION IN TRADE DATA SET WITH EVERY PANEL OBSERVATION
//  THAT AGREES ON INDUSTRY AND HAS DATE FALLING WITHIN THE RANGE OF DATES
use `trade', clear
rangejoin qdate qdate1 qdate2 using `cleaned_treat', by(industry)
keep if !missing(PERSID) // DROP UNMATCHED OBSERVATIONS

//  NOW MERGE THIS BACK TO THE PANEL DATA SET
merge m:1 PERSID qdate using `cleaned_treat', keep(match using) nogenerate
                          Yes, the reason that the labels were being truncated on the left is the command -replace industry = substr(industry, 4, .)- that was in the earlier code. That command was there because the original labels used in the panel data set began with a single letter followed by two spaces, like ""G Wholesale, retail, repair of vehicles". When you nicely changed the label, you omitted those, and so that -replace- command was now chopping off the first four letters of the actual name of the industry. Removing that command fixes this. The above code, when run with your example data, produces matches appropriately. There are still some that are unmatched because there are industries in the panel data that do not occur in the trade data ("Does not Apply", "Other service activities", and "Public admin and defence"), and other cases where the value of qdate in the panel data doesn't match any of the date ranges in the trade data (2019q1 through 2019q4, and 2020q3 and 2020q4. In other words, the above code gives all of the appropriate matches.

                          Comment

                          • #14
                            Originally posted by Clyde Schechter View Post



                            Yes, the reason that the labels were being truncated on the left is the command -replace industry = substr(industry, 4, .)- that was in the earlier code. That command was there because the original labels used in the panel data set began with a single letter followed by two spaces, like ""G Wholesale, retail, repair of vehicles". When you nicely changed the label, you omitted those, and so that -replace- command was now chopping off the first four letters of the actual name of the industry. Removing that command fixes this. The above code, when run with your example data, produces matches appropriately. There are still some that are unmatched because there are industries in the panel data that do not occur in the trade data ("Does not Apply", "Other service activities", and "Public admin and defence"), and other cases where the value of qdate in the panel data doesn't match any of the date ranges in the trade data (2019q1 through 2019q4, and 2020q3 and 2020q4. In other words, the above code gives all of the appropriate matches.

                            Many Thanks, Clyde, for your effort. as you said, now that gives some unmatches, but that makes sense.

                            However, Just a last thing to make sure everything is run correctly
                            my panel data is around 75000 observations, and after I merged the two datasets, I got around 94000 observations. Is that make sense after the merge? Or is there something wrong? I think that should be the same as the number of observations in panel data.

                            Note that when I ran this command
                            Code:
                            angejoin qdate qdate1 qdate2 using `cleaned_treat', by(industry)
keep if !missing(PERSID)
                            I got around 3200 observations in tread data, which I think that rise the number of observation


                            Comment

                            • #15
                              The reason the number of observations increased is that in the trade data there can be multiple observations for the same industry that match with a single observation from the original panel data. To show just one example (there are many, even in the example data):

                              Code:
                                       PERSID    qdate           quarter                                    industry   contin~e   hasper~g   hastem~l  
    10794010101   2020q2   April-June 2020   Accommodation And Food Service Activities      34.4%       0.3%      65.3%  
    10794010101   2020q2   April-June 2020   Accommodation And Food Service Activities      25.4%       0.5%      74.1%  
    10794010101   2020q2   April-June 2020   Accommodation And Food Service Activities      18.2%       1.2%      80.6%  
    10794010101   2020q2   April-June 2020   Accommodation And Food Service Activities      18.4%       0.4%      81.2%  
    10794010101   2020q2   April-June 2020   Accommodation And Food Service Activities      21.9%       0.0%      78.1%
                              In the panel data, there is just one observation with PERSID = 10794010101 and qdate = 2020q2. But the trade data has 5 different entries for its industry, Accommodation And Food Service Activities, for quarter April-June 2020. And all of these get picked up in the end result.

                              If this isn't supposed to happen, then it means that something is wrong with your trade data.

                              Comment

                              Previous 1 2 Next
                              Working...
                              X