Announcement

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

    Replace value

    Dear all

    This penal data has variable lockdowns related to group time ( quarter& week ) and industry. The variable lockdown just has value in some weeks only and other weeks coded as "0." for example, individual "9100501010102 " in the July-Sep 2020 5 "83" working in Education industry "7" takes the value of lockdown "3.5", whereas, the individual in the time. July-Sep 2020 6 "84" take the value of lockdown "0"

    What I want is to replace the value "0" with 3.5." just this for one individual, and I have more than 60000 individuals in my data. This means that I want to replace all the values of lockdown "0" with values in the week after in all my data ( I mean, I need all individuals in week "84" to have the same lockdown value in week "83" if they work in the same industry and so on so forth for all individuals and industries )

    I try to use this command, but it takes a lot of time to replace just one week of time in my data
    Code:
    replace lockdwon = 8.0 if wdate ==64 & industry1 ==22
replace lockdwon = 27.0 if wdate ==64 & industry1 ==23
replace lockdwon = 81.2 if wdate ==64 & industry1 ==1
replace lockdwon = 7.7 if wdate ==64 & industry1 ==21
replace lockdwon = 5.1 if wdate ==64 & industry1 ==13
replace lockdwon = 3.7 if wdate ==64 & industry1 ==18
replace lockdwon = 9.6 if wdate ==64 & industry1 ==2
replace lockdwon = 13.8 if wdate ==64 & industry1 ==7
replace lockdwon = 3.5 if wdate ==64 & industry1 ==12
replace lockdwon = 82.2 if wdate ==64 & industry1 ==4
    I have more than 25 weeks, and it takes time to do it one by one. Is there any perfect way to do this in Stata?

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID float wdate long industry1 double lockdwon
1770203010102  80  7    0
7990493010102  43 12    0
4230191480101  53 14    0
2330794020101  72 12    0
4231191850101  50 14    0
2620693030101  84 23    0
4230101720102  53 12    0
1140293020101  54 18    0
 150294010101  41 23    0
2780204070101 132  7    0
2350994050101  48 12    0
 181392010102  65 14 22.7
 120901020101 100  7    0
 660803070101  86 23    0
1321002020101 101 14  6.3
1560102030101  79  5  4.1
7580502010101  83 21  2.2
9380304050101 146  2    0
1410402010103  95 14  8.3
2220491040101  43 14    0
 120402020101  69  1 78.1
2291304170101 104  7    0
 210501010101  83 23  1.8
9100501010102  83  7  3.5
4230701400101 111 23 29.4
 710991040102  61 12    0
1030702020102 111  5 17.4
2450803040101  86  5    0
2000901010102  74 14    0
9141191020102  50 12    0
2240704070101  98  2    0
1540704050102 150  5    0
2031002040101 114 12    0
 771392050102  78  7    0
1430502030101  83  5  2.4
2181203020101  90  2    0
 490191010102  40 18    0
 810604080102 123  1 37.9
1630194020101  92 12    0
2470794010101  85 14    0
7990493010102  56 12    0
 710601020102  58  7    0
1690594030101  57  7    0
3040692020102  58 14    0
1250404040101 147 14  3.3
 381293030101  90 18    0
 671392070101  52 23    0
9061392010102  78 14    0
1051303040102 117  7 34.8
2591092030101  75 18  7.5
4230801600102  86 23    0
1320992020101  74 12    0
2040202030102  80 12    0
1560901020101  61 13    0
4240404390103 134 12    0
2050801020101 112 12    0
1560294030102  80  5    0
4230401960101  95  4 24.2
2171301030102  78 12    0
7890503020102 135  4  6.6
9170791050102  46 18    0
 320294020101  41  7    0
 490904040101 100 23    0
4230894730101  86 12    0
1130294020101  80 13    0
2390492030101  56  7    0
1030601030102  58 13    0
1420403040101  82 14    0
1920502030101  96  7    0
2181002020103  88  5    0
 150104020101 144 23    0
4230292750101  41 18    0
1260801020101  86 18    0
4231104240101 128 13    0
2120594020101  83  5  2.4
 110404010102 108 12    0
3081103030101 102  7    0
1320494020101  56 14    0
4230503860102  83 14    0
 750904060101 113 13   15
 740803020102 112 18    0
 371003030102  88 14    0
4230301190101  55 21    0
1910403050103 108 14    0
1591392040102  78  7    0
2950902030102 113 12 19.9
9060793030101  85 12  2.1
2830104070102 131  7  6.5
3000894010101  86  7    0
9020392010101  42 12    0
 440394010102  81 18  4.8
1351303010102 104  7    0
 551194030101  63 12    0
 561103090102  89 13 25.3
1560294030102  41  5    0
4230692240101  58 23    0
1640503020102  83  5  2.4
2820603080102 110  5    0
 390401030101  82 14    0
1121103020102  89 23    6
end
label values wdate wdate
label def wdate 40 "Oct-Des 2019 1", modify
label def wdate 41 "Oct-Des 2019 2", modify
label def wdate 42 "Oct-Des 2019 3", modify
label def wdate 43 "Oct-Des 2019 4", modify
label def wdate 46 "Oct-Des 2019 7", modify
label def wdate 48 "Oct-Des 2019 9", modify
label def wdate 50 "Oct-Des 2019 11", modify
label def wdate 52 "Oct-Des 2019 13", modify
label def wdate 53 "Jan-Mar 2020 1", modify
label def wdate 54 "Jan-Mar 2020 2", modify
label def wdate 55 "Jan-Mar 2020 3", modify
label def wdate 56 "Jan-Mar 2020 4", modify
label def wdate 57 "Jan-Mar 2020 5", modify
label def wdate 58 "Jan-Mar 2020 6", modify
label def wdate 61 "Jan-Mar 2020 9", modify
label def wdate 63 "Jan-Mar 2020 11", modify
label def wdate 65 "Jan-Mar 2020 13", modify
label def wdate 69 "Apr-June 2020 4", modify
label def wdate 72 "Apr-June 2020 7", modify
label def wdate 74 "Apr-June 2020 9", modify
label def wdate 75 "Apr-June 2020 10", modify
label def wdate 78 "Apr-June 2020 13", modify
label def wdate 79 "July-Sep 2020 1", modify
label def wdate 80 "July-Sep 2020 2", modify
label def wdate 81 "July-Sep 2020 3", modify
label def wdate 82 "July-Sep 2020 4", modify
label def wdate 83 "July-Sep 2020 5", modify
label def wdate 84 "July-Sep 2020 6", modify
label def wdate 85 "July-Sep 2020 7", modify
label def wdate 86 "July-Sep 2020 8", modify
label def wdate 88 "July-Sep 2020 10", modify
label def wdate 89 "July-Sep 2020 11", modify
label def wdate 90 "July-Sep 2020 12", modify
label def wdate 92 "Oct-Des 2020 1", modify
label def wdate 95 "Oct-Des 2020 4", modify
label def wdate 96 "Oct-Des 2020 5", modify
label def wdate 98 "Oct-Des 2020 7", modify
label def wdate 100 "Oct-Des 2020 9", modify
label def wdate 101 "Oct-Des 2020 10", modify
label def wdate 102 "Oct-Des 2020 11", modify
label def wdate 104 "Oct-Des 2020 13", modify
label def wdate 108 "Jan-Mar 2021 4", modify
label def wdate 110 "Jan-Mar 2021 6", modify
label def wdate 111 "Jan-Mar 2021 7", modify
label def wdate 112 "Jan-Mar 2021 8", modify
label def wdate 113 "Jan-Mar 2021 9", modify
label def wdate 114 "Jan-Mar 2021 10", modify
label def wdate 117 "Jan-Mar 2021 13", modify
label def wdate 123 "Apr-June 2021 6", modify
label def wdate 128 "Apr-June 2021 11", modify
label def wdate 131 "July-Sep 2021 1", modify
label def wdate 132 "July-Sep 2021 2", modify
label def wdate 134 "July-Sep 2021 4", modify
label def wdate 135 "July-Sep 2021 5", modify
label def wdate 144 "Oct-Des 2021 1", modify
label def wdate 146 "Oct-Des 2021 3", modify
label def wdate 147 "Oct-Des 2021 4", modify
label def wdate 150 "Oct-Des 2021 7", modify
label values industry1 industry
label def industry 1 "Accommodation And Food Service Activities", modify
label def industry 2 "Administrative And Support Service Activities", modify
label def industry 4 "Arts, Entertainment And Recreation", modify
label def industry 5 "Construction", modify
label def industry 7 "Education", modify
label def industry 12 "Human Health And Social Work Activities", modify
label def industry 13 "Information And Communication", modify
label def industry 14 "Manufacturing", modify
label def industry 18 "Professional, Scientific And Technical Activities", modify
label def industry 21 "Transportation And Storage", modify
label def industry 23 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles", modify


    Thanks
    Tags: None
  • #2
    Dalia:
    I would start off with counting the number of different groups and related frequencies included in tìyour dataset (that seems to have many panels with one obsrvation only):
    Code:
    . egen wanted=group( wdate industry1)
    Kind regards,
    Carlo
    (Stata 19.0)

    Comment

    • #3
      I would create a new data set containing just three variables: industry1, wdate, and lockdwon. I would populate that dataset with the corresponding values of the changes you want to make. Let's call this data set corrections.dta. (I don't know where you are getting these corrections from--perhaps you already have them in a data set somewhere, or a spreadsheet that you could import to Stata. Worst case, you have to type it all in by hand--it's still less typing than all those -replace- commands.) Now you run:

      Code:
      use your_original_dataset, clear
merge m:1 industry1 wdate using corrections, update replace
      And voila!
      • 1 like

      Comment

      • #4
        Originally posted by Carlo Lazzaro View Post
        Dalia:
        I would start off with counting the number of different groups and related frequencies included in tìyour dataset (that seems to have many panels with one obsrvation only):
        Code:
        . egen wanted=group( wdate industry1)
        Dear Carlo

        Thanks for your reply Yes your right and I already have this variable in my data:

        [CODE]
        Code:
        * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte quarter float(wdate wanted) long industry1 double lockdwon
  60194040102 4 40 3107 1 0
 330191030102 4 40 3107 1 0
4230194400101 4 40 3107 1 0
 760192020103 4 40 3107 1 0
9240194020102 4 40 3107 1 0
1510192020101 4 40 3107 1 0
9110191060102 4 40 3107 1 0
 950194040101 4 40 3107 1 0
2110192040103 4 40 3107 1 0
3020194010103 4 40 3107 1 0
1310194010101 4 40 3107 1 0
 660193010101 4 40 3107 1 0
1730191030101 4 40 3107 1 0
1170192040102 4 40 3107 1 0
 580193010101 4 40 3107 1 0
4230194030103 4 40 3107 1 0
 100191010103 4 40 3107 1 0
 510194010103 4 40 3107 1 0
4230194790102 4 40 3107 1 0
 880194030102 4 40 3107 1 0
1540193010101 4 40 3107 1 0
1070192010101 4 40 3107 1 0
 950194040102 4 40 3107 1 0
1650191020104 4 40 3107 1 0
1410192010102 4 40 3107 1 0
3020193040104 4 40 3107 1 0
 790292040101 4 41 3108 1 0
4230292570102 4 41 3108 1 0
2770293030103 4 41 3108 1 0
 120294030102 4 41 3108 1 0
3020294010102 4 41 3108 1 0
3010291020102 4 41 3108 1 0
2330293020102 4 41 3108 1 0
  70292030101 4 41 3108 1 0
2820292050101 4 41 3108 1 0
2330293020101 4 41 3108 1 0
4230294450101 4 41 3108 1 0
  70292030102 4 41 3108 1 0
7920293010101 4 41 3108 1 0
 130292040101 4 41 3108 1 0
4230294820101 4 41 3108 1 0
  20294040101 4 41 3108 1 0
1350292010101 4 41 3108 1 0
9300292030101 4 41 3108 1 0
3120293040102 4 41 3108 1 0
4230294440102 4 41 3108 1 0
9180294060101 4 41 3108 1 0
1560294020101 4 41 3108 1 0
3120293040101 4 41 3108 1 0
 130292040102 4 41 3108 1 0
4230292730102 4 41 3108 1 0
2770291010102 4 41 3108 1 0
1740291020103 4 41 3108 1 0
2050293020101 4 41 3108 1 0
2730292040104 4 41 3108 1 0
1270293010101 4 41 3108 1 0
 160291010101 4 41 3108 1 0
3020293010103 4 41 3108 1 0
1050293020103 4 41 3108 1 0
1020292020103 4 41 3108 1 0
  30293040102 4 41 3108 1 0
 600292010103 4 41 3108 1 0
4230291500103 4 41 3108 1 0
7530292010101 4 41 3108 1 0
9400392040102 4 42 3109 1 0
1930392010101 4 42 3109 1 0
1800392040101 4 42 3109 1 0
1610392010101 4 42 3109 1 0
4230392670105 4 42 3109 1 0
1520393030101 4 42 3109 1 0
3050394010101 4 42 3109 1 0
1800391040101 4 42 3109 1 0
4230392950102 4 42 3109 1 0
1110393040101 4 42 3109 1 0
 630393010102 4 42 3109 1 0
2930391010102 4 42 3109 1 0
4230394840103 4 42 3109 1 0
2820391030102 4 42 3109 1 0
7720391020103 4 42 3109 1 0
1930392010102 4 42 3109 1 0
 340493010102 4 43 3110 1 0
 700494040101 4 43 3110 1 0
 620492030101 4 43 3110 1 0
4230491240103 4 43 3110 1 0
 470494040101 4 43 3110 1 0
 340492020101 4 43 3110 1 0
 910492010102 4 43 3110 1 0
 340492020102 4 43 3110 1 0
1530491020101 4 43 3110 1 0
1240494010101 4 43 3110 1 0
 480494030102 4 43 3110 1 0
4230491240102 4 43 3110 1 0
2070493040103 4 43 3110 1 0
4230491620101 4 43 3110 1 0
1580493030104 4 43 3110 1 0
 260493020101 4 43 3110 1 0
 180492020103 4 43 3110 1 0
 460492030104 4 43 3110 1 0
2160593040101 4 44 3111 1 0
2950593040102 4 44 3111 1 0
end
format %tw wanted
label values quarter quarter
label def quarter 4 "Oct-Des 2019", modify
label values wdate wdate
label def wdate 40 "Oct-Des 2019 1", modify
label def wdate 41 "Oct-Des 2019 2", modify
label def wdate 42 "Oct-Des 2019 3", modify
label def wdate 43 "Oct-Des 2019 4", modify
label def wdate 44 "Oct-Des 2019 5", modify
label values industry1 industry
label def industry 1 "Accommodation And Food Service Activities", modify
        But how this can help me to ?

        Comment

        • #5
          Originally posted by Clyde Schechter View Post
          I would create a new data set containing just three variables: industry1, wdate, and lockdwon. I would populate that dataset with the corresponding values of the changes you want to make. Let's call this data set corrections.dta. (I don't know where you are getting these corrections from--perhaps you already have them in a data set somewhere, or a spreadsheet that you could import to Stata. Worst case, you have to type it all in by hand--it's still less typing than all those -replace- commands.) Now you run:

          Code:
          use your_original_dataset, clear
merge m:1 industry1 wdate using corrections, update replace
          And voila!
          Thank you Clyde for your reply

          In fact, the data is actually with me from the beginning, but in a two-week format ( fortnight ) and not a week format, so I just wanted to convert it to be on a weekly basis only. This is because when I combined the two-week data with the panel data, it gave me "zero" values for the weeks that do not correspond to the weeks of the panel data.

          Here is the original data( trade) :
          As you can see, it gives me data every two weeks with one value for the "trade" variable. For example, the manufacturing industry gives me a value of 22.7% for the period 23/03/2020 to 05/04/2020 (two weeks). In other words, the trade value for these two weeks was of the same value. Therefore, I only want to convert it so that the value is for each week in this data so that it is compatible with the panel data on a weekly basis.

          What do you see from your point of view? Can we modify the data above (the first data in the question), or should we modify the data (Trade) attached here so that it is in the form of a week and then merge it with the plate data?


          Code:
          * Example generated by -dataex-. For more info, type help dataex
clear
input str24 date str68 industry str5 hastemporarilyclosedortemporaril float(period_start period_finish)
"23/03/2020 to 05/04/2020" "Manufacturing"                                                        "22.7%" 21997 22010
"23/03/2020 to 05/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "8.0%"  21997 22010
"23/03/2020 to 05/04/2020" "Construction"                                                         "29.1%" 21997 22010
"23/03/2020 to 05/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "27.0%" 21997 22010
"23/03/2020 to 05/04/2020" "Accommodation And Food Service Activities"                            "81.2%" 21997 22010
"23/03/2020 to 05/04/2020" "Transportation And Storage"                                           "7.7%"  21997 22010
"23/03/2020 to 05/04/2020" "Information And Communication"                                        "5.1%"  21997 22010
"23/03/2020 to 05/04/2020" "Professional, Scientific And Technical Activities"                    "3.7%"  21997 22010
"23/03/2020 to 05/04/2020" "Administrative And Support Service Activities"                        "9.6%"  21997 22010
"23/03/2020 to 05/04/2020" "Education"                                                            "13.8%" 21997 22010
"23/03/2020 to 05/04/2020" "Human Health And Social Work Activities"                              "3.5%"  21997 22010
"23/03/2020 to 05/04/2020" "Arts, Entertainment And Recreation"                                   "82.2%" 21997 22010
"23/03/2020 to 05/04/2020" "All Industries"                                                       "24.3%" 21997 22010
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        "20.6%" 22011 22024
"06/04/2020 to 19/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "10.0%" 22011 22024
"06/04/2020 to 19/04/2020" "Construction"                                                         "26.1%" 22011 22024
"06/04/2020 to 19/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "24.3%" 22011 22024
"06/04/2020 to 19/04/2020" "Accommodation And Food Service Activities"                            "80.6%" 22011 22024
"06/04/2020 to 19/04/2020" "Transportation And Storage"                                           "8.5%"  22011 22024
"06/04/2020 to 19/04/2020" "Information And Communication"                                        "4.5%"  22011 22024
"06/04/2020 to 19/04/2020" "Professional, Scientific And Technical Activities"                    "3.0%"  22011 22024
"06/04/2020 to 19/04/2020" "Administrative And Support Service Activities"                        "8.1%"  22011 22024
"06/04/2020 to 19/04/2020" "Education"                                                            "12.6%" 22011 22024
"06/04/2020 to 19/04/2020" "Human Health And Social Work Activities"                              "4.9%"  22011 22024
"06/04/2020 to 19/04/2020" "Arts, Entertainment And Recreation"                                   "79.5%" 22011 22024
"06/04/2020 to 19/04/2020" "All Industries"                                                       "22.8%" 22011 22024
"20/04/2020 to 03/05/2020" "Manufacturing"                                                        "13.9%" 22025 22038
"20/04/2020 to 03/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "2.9%"  22025 22038
"20/04/2020 to 03/05/2020" "Construction"                                                         "21.6%" 22025 22038
"20/04/2020 to 03/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "20.0%" 22025 22038
"20/04/2020 to 03/05/2020" "Transportation And Storage"                                           "5.0%"  22025 22038
"20/04/2020 to 03/05/2020" "Accommodation And Food Service Activities"                            "78.1%" 22025 22038
"20/04/2020 to 03/05/2020" "Information And Communication"                                        "4.5%"  22025 22038
"20/04/2020 to 03/05/2020" "Real Estate Activities"                                               "5.6%"  22025 22038
"20/04/2020 to 03/05/2020" "Professional, Scientific And Technical Activities"                    "3.4%"  22025 22038
"20/04/2020 to 03/05/2020" "Administrative And Support Service Activities"                        "7.1%"  22025 22038
"20/04/2020 to 03/05/2020" "Education"                                                            "9.8%"  22025 22038
"20/04/2020 to 03/05/2020" "Human Health And Social Work Activities"                              "3.8%"  22025 22038
"20/04/2020 to 03/05/2020" "Arts, Entertainment And Recreation"                                   "80.1%" 22025 22038
"20/04/2020 to 03/05/2020" "All Industries"                                                       "20.3%" 22025 22038
"04/05/2020 to 17/05/2020" "Manufacturing"                                                        "7.4%"  22039 22052
"04/05/2020 to 17/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "4.5%"  22039 22052
"04/05/2020 to 17/05/2020" "Construction"                                                         "19.1%" 22039 22052
"04/05/2020 to 17/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "14.8%" 22039 22052
"04/05/2020 to 17/05/2020" "Transportation And Storage"                                           "5.5%"  22039 22052
"04/05/2020 to 17/05/2020" "Accommodation And Food Service Activities"                            "74.1%" 22039 22052
"04/05/2020 to 17/05/2020" "Information And Communication"                                        "5.8%"  22039 22052
"04/05/2020 to 17/05/2020" "Real Estate Activities"                                               "2.9%"  22039 22052
"04/05/2020 to 17/05/2020" "Professional, Scientific And Technical Activities"                    "2.6%"  22039 22052
"04/05/2020 to 17/05/2020" "Administrative And Support Service Activities"                        "8.6%"  22039 22052
"04/05/2020 to 17/05/2020" "Education"                                                            "9.4%"  22039 22052
"04/05/2020 to 17/05/2020" "Human Health And Social Work Activities"                              "3.3%"  22039 22052
"04/05/2020 to 17/05/2020" "Arts, Entertainment And Recreation"                                   "74.6%" 22039 22052
"04/05/2020 to 17/05/2020" "All Industries"                                                       "17.7%" 22039 22052
"18/05/2020 to 31/05/2020" "Manufacturing"                                                        "4.8%"  22053 22066
"18/05/2020 to 31/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "0.0%"  22053 22066
"18/05/2020 to 31/05/2020" "Construction"                                                         "17.0%" 22053 22066
"18/05/2020 to 31/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "11.5%" 22053 22066
"18/05/2020 to 31/05/2020" "Transportation And Storage"                                           "4.0%"  22053 22066
"18/05/2020 to 31/05/2020" "Accommodation And Food Service Activities"                            "65.3%" 22053 22066
"18/05/2020 to 31/05/2020" "Information And Communication"                                        "4.8%"  22053 22066
"18/05/2020 to 31/05/2020" "Real Estate Activities"                                               "4.8%"  22053 22066
"18/05/2020 to 31/05/2020" "Professional, Scientific And Technical Activities"                    "2.4%"  22053 22066
"18/05/2020 to 31/05/2020" "Administrative And Support Service Activities"                        "8.7%"  22053 22066
"18/05/2020 to 31/05/2020" "Education"                                                            "10.4%" 22053 22066
"18/05/2020 to 31/05/2020" "Human Health And Social Work Activities"                              "4.5%"  22053 22066
"18/05/2020 to 31/05/2020" "Arts, Entertainment And Recreation"                                   "69.8%" 22053 22066
"18/05/2020 to 31/05/2020" "All Industries"                                                       "15.5%" 22053 22066
"01/06/2020 to 14/06/2020" "Manufacturing"                                                        "2.6%"  22067 22080
"01/06/2020 to 14/06/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "1.5%"  22067 22080
"01/06/2020 to 14/06/2020" "Construction"                                                         "12.0%" 22067 22080
"01/06/2020 to 14/06/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "7.0%"  22067 22080
"01/06/2020 to 14/06/2020" "Transportation And Storage"                                           "5.5%"  22067 22080
"01/06/2020 to 14/06/2020" "Accommodation And Food Service Activities"                            "52.7%" 22067 22080
"01/06/2020 to 14/06/2020" "Information And Communication"                                        "10.5%" 22067 22080
"01/06/2020 to 14/06/2020" "Real Estate Activities"                                               "2.8%"  22067 22080
"01/06/2020 to 14/06/2020" "Professional, Scientific And Technical Activities"                    "7.5%"  22067 22080
"01/06/2020 to 14/06/2020" "Administrative And Support Service Activities"                        "11.8%" 22067 22080
"01/06/2020 to 14/06/2020" "Education"                                                            "10.5%" 22067 22080
"01/06/2020 to 14/06/2020" "Human Health And Social Work Activities"                              "4.1%"  22067 22080
"01/06/2020 to 14/06/2020" "Arts, Entertainment And Recreation"                                   "58.6%" 22067 22080
"01/06/2020 to 14/06/2020" "All Industries"                                                       "13.5%" 22067 22080
"15/06/2020 to 28/06/2020" "Manufacturing"                                                        "0.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "0.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Construction"                                                         "5.5%"  22081 22094
"15/06/2020 to 28/06/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "4.4%"  22081 22094
"15/06/2020 to 28/06/2020" "Transportation And Storage"                                           "6.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Accommodation And Food Service Activities"                            "48.0%" 22081 22094
"15/06/2020 to 28/06/2020" "Information And Communication"                                        "8.2%"  22081 22094
"15/06/2020 to 28/06/2020" "Real Estate Activities"                                               "2.7%"  22081 22094
"15/06/2020 to 28/06/2020" "Professional, Scientific And Technical Activities"                    "4.7%"  22081 22094
"15/06/2020 to 28/06/2020" "Administrative And Support Service Activities"                        "8.2%"  22081 22094
"15/06/2020 to 28/06/2020" "Education"                                                            "8.4%"  22081 22094
"15/06/2020 to 28/06/2020" "Human Health And Social Work Activities"                              "0.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Arts, Entertainment And Recreation"                                   "60.4%" 22081 22094
"15/06/2020 to 28/06/2020" "All Industries"                                                       "10.6%" 22081 22094
"29/06/2020 to 12/07/2020" "Manufacturing"                                                        "0.0%"  22095 22108
"29/06/2020 to 12/07/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "0.0%"  22095 22108
"29/06/2020 to 12/07/2020" "Construction"                                                         "4.1%"  22095 22108
"29/06/2020 to 12/07/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "2.3%"  22095 22108
end
format %td period_start
format %td period_finish

          Comment

          • #6
            I don't know what you mean by "plate" data.

            But I can't reconcile what you are saying in #5 with what you said in #1. If those -replace- statements were producing correct results, then the approach that I suggested in #3 will do exactly the same thing, just with less effort and lower risk of introducing errors through mis-typing the commands.

            If things don't match up properly on -merge, update replace-, you will find exactly the same mismatches using the approach in #1. And in that case, it means that your updating information source is incomplete. That's not a Stata problem; it's a data problem.
            • 1 like

            Comment

            • #7
              Originally posted by Clyde Schechter View Post
              I don't know what you mean by "plate" data.

              But I can't reconcile what you are saying in #5 with what you said in #1. If those -replace- statements were producing correct results, then the approach that I suggested in #3 will do exactly the same thing, just with less effort and lower risk of introducing errors through mis-typing the commands.

              If things don't match up properly on -merge, update replace-, you will find exactly the same mismatches using the approach in #1. And in that case, it means that your updating information source is incomplete. That's not a Stata problem; it's a data problem.
              Thanks for your reply, sorry might be the previous is not much clear. I'll explain in more datils what I want :

              I have two datasets :

              1- Panel data :

              Code:
              * Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte(quarter WEEK) float wdate str68 industry float wanted
10101010101  5 1  53 "Does not apply"                                                       3120
10101010101  6 1  66 "Public admin and defence"                                             3133
10101010101  7 1  79 "Does not apply"                                                       3146
10101010101  8 1  92 "Other service activities"                                             3159
10101010101  9 1 105 "Information And Communication"                                        3172
10101010102  5 1  53 "Does not apply"                                                       3120
10101010102  6 1  66 "Does not apply"                                                       3133
10101010102  7 1  79 "Does not apply"                                                       3146
10101010102  8 1  92 "Does not apply"                                                       3159
10101010102  9 1 105 "Does not apply"                                                       3172
10102020102  6 1  66 "Education"                                                            3133
10102020102  7 1  79 "Education"                                                            3146
10102020102  8 1  92 "Education"                                                            3159
10102020102  9 1 105 "Education"                                                            3172
10102020102 10 1 118 "Education"                                                            3184
10104030101  8 1  92 "Human Health And Social Work Activities"                              3159
10104030101  9 1 105 "Human Health And Social Work Activities"                              3172
10104030101 10 1 118 "Human Health And Social Work Activities"                              3184
10104030101 11 1 131 "Human Health And Social Work Activities"                              3197
10104030101 12 1 144 "Human Health And Social Work Activities"                              3211
10104030102  8 1  92 "Human Health And Social Work Activities"                              3159
10104030102  9 1 105 "Human Health And Social Work Activities"                              3172
10104030102 10 1 118 "Human Health And Social Work Activities"                              3184
10104030102 11 1 131 "Human Health And Social Work Activities"                              3197
10104030102 12 1 144 "Human Health And Social Work Activities"                              3211
10203030101  7 2  80 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3147
10203030101  8 2  93 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3160
10203030101  9 2 106 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3173
10203030101 10 2 119 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3185
10203030101 11 2 132 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3198
10303030101  7 3  81 "Professional, Scientific And Technical Activities"                    3148
10303030101  8 3  94 "Professional, Scientific And Technical Activities"                    3161
10303030101  9 3 107 "Professional, Scientific And Technical Activities"                    3174
10303030101 10 3 120 "Professional, Scientific And Technical Activities"                    3186
10303030101 11 3 133 "Professional, Scientific And Technical Activities"                    3199
10304050102  8 3  94 "Manufacturing"                                                        3161
10304050102  9 3 107 "Does not apply"                                                       3174
10304050102 10 3 120 "Does not apply"                                                       3186
10304050102 11 3 133 "Does not apply"                                                       3199
10304050102 12 3 146 "Does not apply"                                                       3213
10493040101  3 4  30 "Does not apply"                                                       3096
10493040101  4 4  43 "Does not apply"                                                       3110
10493040101  5 4  56 "Does not apply"                                                       3123
10493040101  6 4  69 "Does not apply"                                                       3136
10493040101  7 4  82 "Does not apply"                                                       3149
10501030101  5 5  57 "Professional, Scientific And Technical Activities"                    3124
10501030101  6 5  70 "Professional, Scientific And Technical Activities"                    3137
10501030101  7 5  83 "Professional, Scientific And Technical Activities"                    3150
10501030101  8 5  96 "Professional, Scientific And Technical Activities"                    3163
10501030101  9 5 109 "Professional, Scientific And Technical Activities"                    3176
10504040101  8 5  96 "Does not apply"                                                       3163
10504040101  9 5 109 "Does not apply"                                                       3176
10504040101 10 5 122 "Does not apply"                                                       3188
10504040101 11 5 135 "Does not apply"                                                       3201
10504040101 12 5 148 "Does not apply"                                                       3215
10504040102  8 5  96 "Does not apply"                                                       3163
10504040102  9 5 109 "Does not apply"                                                       3176
10504040102 10 5 122 "Does not apply"                                                       3188
10504040102 11 5 135 "Does not apply"                                                       3201
10504040102 12 5 148 "Does not apply"                                                       3215
10602010101  6 6  71 "Does not apply"                                                       3138
10602010101  7 6  84 "Does not apply"                                                       3151
10602010101  8 6  97 "Does not apply"                                                       3164
10602010101  9 6 110 "Does not apply"                                                       3177
10602010101 10 6 123 "Does not apply"                                                       3189
10602020101  6 6  71 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3138
10602020101  7 6  84 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3151
10602020101  8 6  97 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3164
10602020101  9 6 110 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3177
10602020101 10 6 123 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3189
10602030102  6 6  71 "Does not apply"                                                       3138
10602030102  7 6  84 "Does not apply"                                                       3151
10602030102  8 6  97 "Does not apply"                                                       3164
10602030102  9 6 110 "Does not apply"                                                       3177
10602030102 10 6 123 "Does not apply"                                                       3189
10603070101  7 6  84 "Real estate activit ies"                                              3151
10603070101  8 6  97 "Real estate activit ies"                                              3164
10603070101  9 6 110 "Real estate activit ies"                                              3177
10603070101 10 6 123 "Does not apply"                                                       3189
10603070101 11 6 136 "Real estate activit ies"                                              3202
10604060102  8 6  97 "Human Health And Social Work Activities"                              3164
10604060102  9 6 110 "Human Health And Social Work Activities"                              3177
10604060102 10 6 123 "Human Health And Social Work Activities"                              3189
10604060102 11 6 136 "Human Health And Social Work Activities"                              3202
10604060102 12 6 149 "Human Health And Social Work Activities"                              3216
10604070101  8 6  97 "Does not apply"                                                       3164
10604070101  9 6 110 "Does not apply"                                                       3177
10604070101 10 6 123 "Does not apply"                                                       3189
10604070101 11 6 136 "Accommodation And Food Service Activities"                            3202
10604070101 12 6 149 "Accommodation And Food Service Activities"                            3216
10604070103  8 6  97 "Accommodation And Food Service Activities"                            3164
10604070103  9 6 110 "Accommodation And Food Service Activities"                            3177
10604070103 10 6 123 "Accommodation And Food Service Activities"                            3189
10604070103 11 6 136 "Accommodation And Food Service Activities"                            3202
10604070103 12 6 149 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 3216
10694020101  4 6  45 "Does not apply"                                                       3112
10694020101  5 6  58 "Does not apply"                                                       3125
10694020101  6 6  71 "Does not apply"                                                       3138
10694020101  7 6  84 "Does not apply"                                                       3151
10694020101  8 6  97 "Does not apply"                                                       3164
end
format %tw wanted
label values quarter quarter
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 "Apr-June 2020", modify
label def quarter 7 "July-Sep 2020", modify
label def quarter 8 "Oct-Des 2020", modify
label def quarter 9 "Jan-Mar 2021", modify
label def quarter 10 "Apr-June 2021", modify
label def quarter 11 "July-Sep 2021", modify
label def quarter 12 "Oct-Des 2021", modify
label values WEEK WEEK
label values wdate wdate
label def wdate 30 "July-Sep 2019 4", modify
label def wdate 43 "Oct-Des 2019 4", modify
label def wdate 45 "Oct-Des 2019 6", modify
label def wdate 53 "Jan-Mar 2020 1", modify
label def wdate 56 "Jan-Mar 2020 4", modify
label def wdate 57 "Jan-Mar 2020 5", modify
label def wdate 58 "Jan-Mar 2020 6", modify
label def wdate 66 "Apr-June 2020 1", modify
label def wdate 69 "Apr-June 2020 4", modify
label def wdate 70 "Apr-June 2020 5", modify
label def wdate 71 "Apr-June 2020 6", modify
label def wdate 79 "July-Sep 2020 1", modify
label def wdate 80 "July-Sep 2020 2", modify
label def wdate 81 "July-Sep 2020 3", modify
label def wdate 82 "July-Sep 2020 4", modify
label def wdate 83 "July-Sep 2020 5", modify
label def wdate 84 "July-Sep 2020 6", modify
label def wdate 92 "Oct-Des 2020 1", modify
label def wdate 93 "Oct-Des 2020 2", modify
label def wdate 94 "Oct-Des 2020 3", modify
label def wdate 96 "Oct-Des 2020 5", modify
label def wdate 97 "Oct-Des 2020 6", modify
label def wdate 105 "Jan-Mar 2021 1", modify
label def wdate 106 "Jan-Mar 2021 2", modify
label def wdate 107 "Jan-Mar 2021 3", modify
label def wdate 109 "Jan-Mar 2021 5", modify
label def wdate 110 "Jan-Mar 2021 6", modify
label def wdate 118 "Apr-June 2021 1", modify
label def wdate 119 "Apr-June 2021 2", modify
label def wdate 120 "Apr-June 2021 3", modify
label def wdate 122 "Apr-June 2021 5", modify
label def wdate 123 "Apr-June 2021 6", modify
label def wdate 131 "July-Sep 2021 1", modify
label def wdate 132 "July-Sep 2021 2", modify
label def wdate 133 "July-Sep 2021 3", modify
label def wdate 135 "July-Sep 2021 5", modify
label def wdate 136 "July-Sep 2021 6", modify
label def wdate 144 "Oct-Des 2021 1", modify
label def wdate 146 "Oct-Des 2021 3", modify
label def wdate 148 "Oct-Des 2021 5", modify
label def wdate 149 "Oct-Des 2021 6", modify



              2- Trade data :
              Code:
              * Example generated by -dataex-. For more info, type help dataex
clear
input str24 date str68 industry str5 hastemporarilyclosedortemporaril float(period_start period_finish)
"23/03/2020 to 05/04/2020" "Manufacturing"                                                        "22.7%" 21997 22010
"23/03/2020 to 05/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "8.0%"  21997 22010
"23/03/2020 to 05/04/2020" "Construction"                                                         "29.1%" 21997 22010
"23/03/2020 to 05/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "27.0%" 21997 22010
"23/03/2020 to 05/04/2020" "Accommodation And Food Service Activities"                            "81.2%" 21997 22010
"23/03/2020 to 05/04/2020" "Transportation And Storage"                                           "7.7%"  21997 22010
"23/03/2020 to 05/04/2020" "Information And Communication"                                        "5.1%"  21997 22010
"23/03/2020 to 05/04/2020" "Professional, Scientific And Technical Activities"                    "3.7%"  21997 22010
"23/03/2020 to 05/04/2020" "Administrative And Support Service Activities"                        "9.6%"  21997 22010
"23/03/2020 to 05/04/2020" "Education"                                                            "13.8%" 21997 22010
"23/03/2020 to 05/04/2020" "Human Health And Social Work Activities"                              "3.5%"  21997 22010
"23/03/2020 to 05/04/2020" "Arts, Entertainment And Recreation"                                   "82.2%" 21997 22010
"23/03/2020 to 05/04/2020" "All Industries"                                                       "24.3%" 21997 22010
"06/04/2020 to 19/04/2020" "Manufacturing"                                                        "20.6%" 22011 22024
"06/04/2020 to 19/04/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "10.0%" 22011 22024
"06/04/2020 to 19/04/2020" "Construction"                                                         "26.1%" 22011 22024
"06/04/2020 to 19/04/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "24.3%" 22011 22024
"06/04/2020 to 19/04/2020" "Accommodation And Food Service Activities"                            "80.6%" 22011 22024
"06/04/2020 to 19/04/2020" "Transportation And Storage"                                           "8.5%"  22011 22024
"06/04/2020 to 19/04/2020" "Information And Communication"                                        "4.5%"  22011 22024
"06/04/2020 to 19/04/2020" "Professional, Scientific And Technical Activities"                    "3.0%"  22011 22024
"06/04/2020 to 19/04/2020" "Administrative And Support Service Activities"                        "8.1%"  22011 22024
"06/04/2020 to 19/04/2020" "Education"                                                            "12.6%" 22011 22024
"06/04/2020 to 19/04/2020" "Human Health And Social Work Activities"                              "4.9%"  22011 22024
"06/04/2020 to 19/04/2020" "Arts, Entertainment And Recreation"                                   "79.5%" 22011 22024
"06/04/2020 to 19/04/2020" "All Industries"                                                       "22.8%" 22011 22024
"20/04/2020 to 03/05/2020" "Manufacturing"                                                        "13.9%" 22025 22038
"20/04/2020 to 03/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "2.9%"  22025 22038
"20/04/2020 to 03/05/2020" "Construction"                                                         "21.6%" 22025 22038
"20/04/2020 to 03/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "20.0%" 22025 22038
"20/04/2020 to 03/05/2020" "Transportation And Storage"                                           "5.0%"  22025 22038
"20/04/2020 to 03/05/2020" "Accommodation And Food Service Activities"                            "78.1%" 22025 22038
"20/04/2020 to 03/05/2020" "Information And Communication"                                        "4.5%"  22025 22038
"20/04/2020 to 03/05/2020" "Real Estate Activities"                                               "5.6%"  22025 22038
"20/04/2020 to 03/05/2020" "Professional, Scientific And Technical Activities"                    "3.4%"  22025 22038
"20/04/2020 to 03/05/2020" "Administrative And Support Service Activities"                        "7.1%"  22025 22038
"20/04/2020 to 03/05/2020" "Education"                                                            "9.8%"  22025 22038
"20/04/2020 to 03/05/2020" "Human Health And Social Work Activities"                              "3.8%"  22025 22038
"20/04/2020 to 03/05/2020" "Arts, Entertainment And Recreation"                                   "80.1%" 22025 22038
"20/04/2020 to 03/05/2020" "All Industries"                                                       "20.3%" 22025 22038
"04/05/2020 to 17/05/2020" "Manufacturing"                                                        "7.4%"  22039 22052
"04/05/2020 to 17/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "4.5%"  22039 22052
"04/05/2020 to 17/05/2020" "Construction"                                                         "19.1%" 22039 22052
"04/05/2020 to 17/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "14.8%" 22039 22052
"04/05/2020 to 17/05/2020" "Transportation And Storage"                                           "5.5%"  22039 22052
"04/05/2020 to 17/05/2020" "Accommodation And Food Service Activities"                            "74.1%" 22039 22052
"04/05/2020 to 17/05/2020" "Information And Communication"                                        "5.8%"  22039 22052
"04/05/2020 to 17/05/2020" "Real Estate Activities"                                               "2.9%"  22039 22052
"04/05/2020 to 17/05/2020" "Professional, Scientific And Technical Activities"                    "2.6%"  22039 22052
"04/05/2020 to 17/05/2020" "Administrative And Support Service Activities"                        "8.6%"  22039 22052
"04/05/2020 to 17/05/2020" "Education"                                                            "9.4%"  22039 22052
"04/05/2020 to 17/05/2020" "Human Health And Social Work Activities"                              "3.3%"  22039 22052
"04/05/2020 to 17/05/2020" "Arts, Entertainment And Recreation"                                   "74.6%" 22039 22052
"04/05/2020 to 17/05/2020" "All Industries"                                                       "17.7%" 22039 22052
"18/05/2020 to 31/05/2020" "Manufacturing"                                                        "4.8%"  22053 22066
"18/05/2020 to 31/05/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "0.0%"  22053 22066
"18/05/2020 to 31/05/2020" "Construction"                                                         "17.0%" 22053 22066
"18/05/2020 to 31/05/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "11.5%" 22053 22066
"18/05/2020 to 31/05/2020" "Transportation And Storage"                                           "4.0%"  22053 22066
"18/05/2020 to 31/05/2020" "Accommodation And Food Service Activities"                            "65.3%" 22053 22066
"18/05/2020 to 31/05/2020" "Information And Communication"                                        "4.8%"  22053 22066
"18/05/2020 to 31/05/2020" "Real Estate Activities"                                               "4.8%"  22053 22066
"18/05/2020 to 31/05/2020" "Professional, Scientific And Technical Activities"                    "2.4%"  22053 22066
"18/05/2020 to 31/05/2020" "Administrative And Support Service Activities"                        "8.7%"  22053 22066
"18/05/2020 to 31/05/2020" "Education"                                                            "10.4%" 22053 22066
"18/05/2020 to 31/05/2020" "Human Health And Social Work Activities"                              "4.5%"  22053 22066
"18/05/2020 to 31/05/2020" "Arts, Entertainment And Recreation"                                   "69.8%" 22053 22066
"18/05/2020 to 31/05/2020" "All Industries"                                                       "15.5%" 22053 22066
"01/06/2020 to 14/06/2020" "Manufacturing"                                                        "2.6%"  22067 22080
"01/06/2020 to 14/06/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "1.5%"  22067 22080
"01/06/2020 to 14/06/2020" "Construction"                                                         "12.0%" 22067 22080
"01/06/2020 to 14/06/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "7.0%"  22067 22080
"01/06/2020 to 14/06/2020" "Transportation And Storage"                                           "5.5%"  22067 22080
"01/06/2020 to 14/06/2020" "Accommodation And Food Service Activities"                            "52.7%" 22067 22080
"01/06/2020 to 14/06/2020" "Information And Communication"                                        "10.5%" 22067 22080
"01/06/2020 to 14/06/2020" "Real Estate Activities"                                               "2.8%"  22067 22080
"01/06/2020 to 14/06/2020" "Professional, Scientific And Technical Activities"                    "7.5%"  22067 22080
"01/06/2020 to 14/06/2020" "Administrative And Support Service Activities"                        "11.8%" 22067 22080
"01/06/2020 to 14/06/2020" "Education"                                                            "10.5%" 22067 22080
"01/06/2020 to 14/06/2020" "Human Health And Social Work Activities"                              "4.1%"  22067 22080
"01/06/2020 to 14/06/2020" "Arts, Entertainment And Recreation"                                   "58.6%" 22067 22080
"01/06/2020 to 14/06/2020" "All Industries"                                                       "13.5%" 22067 22080
"15/06/2020 to 28/06/2020" "Manufacturing"                                                        "0.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "0.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Construction"                                                         "5.5%"  22081 22094
"15/06/2020 to 28/06/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "4.4%"  22081 22094
"15/06/2020 to 28/06/2020" "Transportation And Storage"                                           "6.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Accommodation And Food Service Activities"                            "48.0%" 22081 22094
"15/06/2020 to 28/06/2020" "Information And Communication"                                        "8.2%"  22081 22094
"15/06/2020 to 28/06/2020" "Real Estate Activities"                                               "2.7%"  22081 22094
"15/06/2020 to 28/06/2020" "Professional, Scientific And Technical Activities"                    "4.7%"  22081 22094
"15/06/2020 to 28/06/2020" "Administrative And Support Service Activities"                        "8.2%"  22081 22094
"15/06/2020 to 28/06/2020" "Education"                                                            "8.4%"  22081 22094
"15/06/2020 to 28/06/2020" "Human Health And Social Work Activities"                              "0.0%"  22081 22094
"15/06/2020 to 28/06/2020" "Arts, Entertainment And Recreation"                                   "60.4%" 22081 22094
"15/06/2020 to 28/06/2020" "All Industries"                                                       "10.6%" 22081 22094
"29/06/2020 to 12/07/2020" "Manufacturing"                                                        "0.0%"  22095 22108
"29/06/2020 to 12/07/2020" "Water Supply, Sewerage, Waste Management And Remediation Activities"  "0.0%"  22095 22108
"29/06/2020 to 12/07/2020" "Construction"                                                         "4.1%"  22095 22108
"29/06/2020 to 12/07/2020" "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" "2.3%"  22095 22108
end
format %td period_start
format %td period_finish

              What I want is to Merge these datasets. The panel data has variable industry, and the Trade has the same variable, so I want to merge based on the industry and weekly basis. However, the problem is that the panel data is in a quarterly format, and the Trade data is in a fortnightly ( two weeks ) format. Therefore, what I want now is to change the format of the Trade data and separate the time. As you see, the Trade data is given value based on two weeks. How do we convert this data to weekly? The mechanism to convert to weekly is that I assume a week's value (23/03/2023 to 29/03/2020) is the same as the value of a week (30/03/2020 to 05/04/2020). How to do this in Stata?



              After preparing the Trade data, I want to merge it with Panel data. Could you help me with this also?
              Also, do we have to convert the Panel data to a weekly format? Or what is the best way to merge these two data?

              I hope this is clear!

              Comment

              • #8
                Unless further down the line you have some dataset with weekly data that you plan to join with these results, it makes no sense at all to expand this data into weekly data by duplicating observations. There is no conceivable statistical rationale for doing that. The data are quarterly in one set, and in two-week intervals in the other. You have two sensible choices: aggregate the trade data up to quarterly and merge them, or merge the panel data pairing each quarterly observations with all of the two-week periods that lie within the quarters (by industry).

                But there is a problem doing even that. You have a number of observations in the trade data where the two-week period in question overlaps two different quarters. Here's a listing of them:

                Code:
                     +----------------------------------------------------------------------------------------------+
     | period_~t   period_~h                                                               industry |
     |----------------------------------------------------------------------------------------------|
  1. | 23mar2020   05apr2020                              Accommodation And Food Service Activities |
  2. | 23mar2020   05apr2020                          Administrative And Support Service Activities |
  3. | 23mar2020   05apr2020                                                         All Industries |
  4. | 23mar2020   05apr2020                                     Arts, Entertainment And Recreation |
  5. | 23mar2020   05apr2020                                                           Construction |
     |----------------------------------------------------------------------------------------------|
  6. | 23mar2020   05apr2020                                                              Education |
  7. | 23mar2020   05apr2020                                Human Health And Social Work Activities |
  8. | 23mar2020   05apr2020                                          Information And Communication |
  9. | 23mar2020   05apr2020                                                          Manufacturing |
 10. | 23mar2020   05apr2020                      Professional, Scientific And Technical Activities |
     |----------------------------------------------------------------------------------------------|
 11. | 23mar2020   05apr2020                                             Transportation And Storage |
 12. | 23mar2020   05apr2020    Water Supply, Sewerage, Waste Management And Remediation Activities |
 13. | 23mar2020   05apr2020   Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles |
 97. | 29jun2020   12jul2020                                                           Construction |
 98. | 29jun2020   12jul2020                                                          Manufacturing |
     |----------------------------------------------------------------------------------------------|
 99. | 29jun2020   12jul2020    Water Supply, Sewerage, Waste Management And Remediation Activities |
100. | 29jun2020   12jul2020   Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles |
     +----------------------------------------------------------------------------------------------+
                So you need to decide how you want to handle those: which quarter do you match them to?

                Comment

                • #9
                  Thanks, Clyde, for this valuable explanation:

                  I would go with the first one, which is to aggregate the trade data up to quarterly and merge them with panel data.

                  In terms of overlapping, if we have a number of observations in the trade data where the two-week period in question overlaps two different quarters, starting at the end of the quarter, we would be moving to the next quarter.

                  This means observation from 23 Mar 2020 to 05 Apr 2020 must be linked to the quarter April - June 2020, and observation on 29 June 2020 to 12 July 2020 must be linked to the July - Sep 2020, and so on so forth

                  Note that My trade data sample starts from 23 Mar 2020 to 26 Des 2021. Therefore, I assume we will get overlapping in the whole sample.


                  Comment

                  • #10
                    All right, it is a bit difficult to understand these data sets. But I think I have it now. In the panel data, each combination of PERSID and industry gets a single observation each quarter. In the trade data, it is industry and 2-week period that uniquely identify observations. The end result should be a data set where each PESID industry quarter observation in the panel data is paired up with all of the trade data observations for the same industry and quarter, and then aggregated up to produce an estimate of the quarterly values of the hastemporarilyclosed variable. I will assume that you want that aggregation to be done by averaging them--that seems to make sense for a variable that represents the precentage of the industry that has closed down. Since the two-week periods are not all exactly 14 days, I will take a duration-weighted average.

                    Code:
                    clear*

use panel_data
// CREATE STATA INTERNAL FORMAT QUARTERLY DATE
// FROM VARIABLE QUARTER, WHICH COUNTS UP FROM
// 2019Q1 = 1
gen qdate = tq(2018q4) + quarter
format qdate %tq
tempfile panel
save `panel'

use trade_data, clear
// CREATE A QUARTERLY DATE BASED ON THE QUARTER IN WHICH
// THE TWO WEEK PERIOD ENDS
gen qdate = qofd(period_finish)
format qdate %tq
joinby industry qdate using `panel'
destring hastemporarilyclosed, replace ignore("%")

gen wt = period_finish-period_start

isid PERSID industry qdate period_start

collapse (mean) hastemporarilyclosed [iweight = wt], by(PERSID industry qdate)
                    Note: In the example data, most of the observations go unmatched because the example data sets don't overlap very much. Presumably with the full data sets this problem will be much reduced, perhaps non-existent.

                    Comment

                    Previous Next
                    Working...
                    X