[Dalia Toame]

Hello Clyde
Thanks for your effort with me, so I'm making some changes to my data to include more time range. Also, I would like to merge data weekly instead of quarterly because I found difficulties with getting reliable results.

I have read the codebook of my panel datasets, so the week's definition is what you said in this thread

Here I am applying the definition of the week that Stata uses. Specifically, week 1 of any given year begins on January 1, regardless of what day of the week that falls on.

As this below in first quarter of 2020:
WEEK 1 : 1 Jan to 7 Jan 2020
WEEK 2 : 8 Jan to 14 Jan 2020
WEEK 3 : 15 Jan to 21 Jan 2020
WEEK 4 : 22 Jan to 28 Jan 2020
WEEK 4 : 29 Jan to 4 Feb 2020
WEEK 5 : 5 4 Feb to 11 Feb 2020 ............ so on and so forth in all quarters

In this case, I think we mapped it to 13 weeks per quarter.

Could you help me merge the panel datasets and Trade data?

The panel data

* Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte WEEK str68 industry float(qdate wanted) byte quarter float wdate
10493040101  4 "Does not apply"                                                       238 3096 3  30
10493040101  4 "Does not apply"                                                       239 3110 4  43
10493040101  4 "Does not apply"                                                       240 3123 5  56
10493040101  4 "Does not apply"                                                       241 3136 6  69
10493040101  4 "Does not apply"                                                       242 3149 7  82
10694020101  6 "Does not apply"                                                       239 3112 4  45
10694020101  6 "Does not apply"                                                       240 3125 5  58
10694020101  6 "Does not apply"                                                       241 3138 6  71
10694020101  6 "Does not apply"                                                       242 3151 7  84
10694020101  6 "Does not apply"                                                       243 3164 8  97
10694020102  6 "Does not apply"                                                       239 3112 4  45
10694020102  6 "Does not apply"                                                       240 3125 5  58
10694020102  6 "Does not apply"                                                       241 3138 6  71
10694020102  6 "Does not apply"                                                       242 3151 7  84
10694020102  6 "Does not apply"                                                       243 3164 8  97
10792030101  7 "Does not apply"                                                       237 3086 2  20
10792030101  7 "Does not apply"                                                       238 3099 3  33
10792030101  7 "Does not apply"                                                       239 3113 4  46
10792030101  7 "Does not apply"                                                       240 3126 5  59
10792030101  7 "Does not apply"                                                       241 3139 6  72
10793010101  7 "Public admin and defence"                                             238 3099 3  33
10793010101  7 "Public admin and defence"                                             239 3113 4  46
10793010101  7 "Public admin and defence"                                             240 3126 5  59
10793010101  7 "Public admin and defence"                                             241 3139 6  72
10793010101  7 "Public admin and defence"                                             242 3152 7  85
10794010101  7 "Accommodation And Food Service Activities"                            239 3113 4  46
10794010101  7 "Accommodation And Food Service Activities"                            240 3126 5  59
10794010101  7 "Accommodation And Food Service Activities"                            241 3139 6  72
10794010101  7 "Accommodation And Food Service Activities"                            242 3152 7  85
10794010101  7 "Accommodation And Food Service Activities"                            243 3165 8  98
10794010102  7 "Accommodation And Food Service Activities"                            239 3113 4  46
10794010102  7 "Accommodation And Food Service Activities"                            240 3126 5  59
10794010102  7 "Accommodation And Food Service Activities"                            241 3139 6  72
10794010102  7 "Accommodation And Food Service Activities"                            242 3152 7  85
10794010102  7 "Accommodation And Food Service Activities"                            243 3165 8  98
10993020101  9 "Education"                                                            238 3101 3  35
10993020101  9 "Education"                                                            239 3115 4  48
10993020101  9 "Education"                                                            240 3128 5  61
10993020101  9 "Education"                                                            241 3141 6  74
10993020101  9 "Does not apply"                                                       242 3154 7  87
10993020102  9 "Public admin and defence"                                             238 3101 3  35
10993020102  9 "Public admin and defence"                                             239 3115 4  48
10993020102  9 "Public admin and defence"                                             240 3128 5  61
10993020102  9 "Public admin and defence"                                             241 3141 6  74
10993020102  9 "Public admin and defence"                                             242 3154 7  87
11091010101 10 "Education"                                                            236 3077 1  10
11091010101 10 "Education"                                                            237 3089 2  23
11091010101 10 "Education"                                                            238 3102 3  36
11091010101 10 "Education"                                                            239 3116 4  49
11091010101 10 "Education"                                                            240 3129 5  62
11093030101 10 "Does not apply"                                                       238 3102 3  36
11093030101 10 "Does not apply"                                                       239 3116 4  49
11093030101 10 "Does not apply"                                                       240 3129 5  62
11093030101 10 "Does not apply"                                                       241 3142 6  75
11093030101 10 "Does not apply"                                                       242 3155 7  88
11094010101 10 "Public admin and defence"                                             239 3116 4  49
11094010101 10 "Does not apply"                                                       240 3129 5  62
11094010101 10 "Does not apply"                                                       241 3142 6  75
11094010101 10 "Does not apply"                                                       242 3155 7  88
11094010101 10 "Does not apply"                                                       243 3168 8 101
11094010102 10 "Human Health And Social Work Activities"                              239 3116 4  49
11094010102 10 "Human Health And Social Work Activities"                              240 3129 5  62
11094010102 10 "Human Health And Social Work Activities"                              241 3142 6  75
11094010102 10 "Human Health And Social Work Activities"                              242 3155 7  88
11094010102 10 "Human Health And Social Work Activities"                              243 3168 8 101
11291020101 12 "Does not apply"                                                       236 3079 1  12
11291020101 12 "Does not apply"                                                       237 3091 2  25
11291020101 12 "Does not apply"                                                       238 3104 3  38
11291020101 12 "Does not apply"                                                       239 3118 4  51
11291020101 12 "Does not apply"                                                       240 3131 5  64
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237 3091 2  25
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238 3104 3  38
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239 3118 4  51
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240 3131 5  64
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 241 3144 6  77
11294030101 12 "Public admin and defence"                                             239 3118 4  51
11294030101 12 "Public admin and defence"                                             240 3131 5  64
11294030101 12 "Public admin and defence"                                             241 3144 6  77
11294030101 12 "Public admin and defence"                                             242 3157 7  90
11294030101 12 "Public admin and defence"                                             243 3170 8 103
11294030102 12 "Does not apply"                                                       239 3118 4  51
11294030102 12 "Does not apply"                                                       240 3131 5  64
11294030102 12 "Does not apply"                                                       241 3144 6  77
11294030102 12 "Does not apply"                                                       242 3157 7  90
11294030102 12 "Does not apply"                                                       243 3170 8 103
20191020101  1 "Education"                                                            236 3068 1   1
20191020101  1 "Education"                                                            237 3080 2  14
20191020101  1 "Education"                                                            238 3093 3  27
20191020101  1 "Education"                                                            239 3107 4  40
20191020101  1 "Education"                                                            240 3120 5  53
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 236 3068 1   1
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237 3080 2  14
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238 3093 3  27
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239 3107 4  40
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240 3120 5  53
20191040101  1 "Other service activities"                                             236 3068 1   1
20191040101  1 "Other service activities"                                             237 3080 2  14
20191040101  1 "Other service activities"                                             238 3093 3  27
20191040101  1 "Other service activities"                                             239 3107 4  40
20191040101  1 "Other service activities"                                             240 3120 5  53
end
format %tq qdate
format %tw wanted
label values WEEK WEEK
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 wdate wdate
label def wdate 1 "Jan-Mar 2019 1", modify
label def wdate 10 "Jan-Mar 2019 10", modify
label def wdate 12 "Jan-Mar 2019 12", modify
label def wdate 14 "April-June 2019 1", modify
label def wdate 20 "April-June 2019 7", modify
label def wdate 23 "April-June 2019 10", modify
label def wdate 25 "April-June 2019 12", modify
label def wdate 27 "July-Sep 2019 1", modify
label def wdate 30 "July-Sep 2019 4", modify
label def wdate 33 "July-Sep 2019 7", modify
label def wdate 35 "July-Sep 2019 9", modify
label def wdate 36 "July-Sep 2019 10", modify
label def wdate 38 "July-Sep 2019 12", modify
label def wdate 40 "Oct-Des 2019 1", modify
label def wdate 43 "Oct-Des 2019 4", modify
label def wdate 45 "Oct-Des 2019 6", modify
label def wdate 46 "Oct-Des 2019 7", modify
label def wdate 48 "Oct-Des 2019 9", modify
label def wdate 49 "Oct-Des 2019 10", modify
label def wdate 51 "Oct-Des 2019 12", modify
label def wdate 53 "Jan-Mar 2020 1", modify
label def wdate 56 "Jan-Mar 2020 4", modify
label def wdate 58 "Jan-Mar 2020 6", modify
label def wdate 59 "Jan-Mar 2020 7", modify
label def wdate 61 "Jan-Mar 2020 9", modify
label def wdate 62 "Jan-Mar 2020 10", modify
label def wdate 64 "Jan-Mar 2020 12", modify
label def wdate 69 "April-June 2020 4", modify
label def wdate 71 "April-June 2020 6", modify
label def wdate 72 "April-June 2020 7", modify
label def wdate 74 "April-June 2020 9", modify
label def wdate 75 "April-June 2020 10", modify
label def wdate 77 "April-June 2020 12", modify
label def wdate 82 "July-Sep 2020 4", modify
label def wdate 84 "July-Sep 2020 6", modify
label def wdate 85 "July-Sep 2020 7", modify
label def wdate 87 "July-Sep 2020 9", modify
label def wdate 88 "July-Sep 2020 10", modify
label def wdate 90 "July-Sep 2020 12", modify
label def wdate 97 "Oct-Des 2020 6", modify
label def wdate 98 "Oct-Des 2020 7", modify
label def wdate 101 "Oct-Des 2020 10", modify
label def wdate 103 "Oct-Des 2020 12", modify

So I used your suggestion and Nick's suggestion in this thread to create a weekly variable

This might work, depending on how weeks are defined. I believe I have, in an earlier thread of yours, explained that there are different definitions of week in use, and these different definitions would lead to different results. Here I am applying the definition of week that Stata uses. Specifically week 1 of any given year begins on January 1, regardless of what day of the week that falls on. A new week begins every 7 days thereafter, except that at the end of the year, the 52nd week will be longer than 7 days and include however many additional days are needed to reach through December 31 (this number will vary depending on whether it is a leap year). In particular, there is no abbreviated 53rd week.

Added: Notice that under this definition of week, some weeks will overlap two quarters. For example, the last day of 2019Q1, 31 Mar 2019, and the first day of 2019Q2, 1 Apr 2019, are both in the 13th week of year 2019. Because the duration of a year is never a multiple of 7 days, and that of a quarter is only sometimes so, anomalies like this arise in any attempt to frame larger units of time into weeks. That is, although the different ways weeks can be defined will give rise to different anomalies, it is mathematically inevitable that any definition of week leads to some kind of anomaly.

gen int qdate = quarter + tq(2018q4)
format qdate %tq
gen wanted = WEEK + wofd(dofq(qdate)) - 1
format wanted %tw

Somehow the definition you're using implies that 365 or 366 days in a year are always mapped to 13 weeks per quarter.

That should allow you to define your own numbering as a composite of quarter and week.

If this definition is as strict as implied, you can and should keep clear of Stata's own weekly functions and formats.

egen wdate = group(quarter week), label 

.

And this is the trade data

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

I think we get overlaps in the date of trade data with the week in panel data. So what is the best way to merge these datasets based on week time? Is it possible for panel data to create a variable for two weeks, so this gives us an easy way to merge the trade data because the trade data has a time variable representing two weeks?

Thanks for your time with me

[Dalia Toame]

Hello Clyde
Thanks for your effort with me, so I'm making some changes to my data to include more time range. Also, I would like to merge data weekly instead of quarterly because I found difficulties with getting reliable results.

I have read the codebook of my panel datasets, so the week's definition is what you said in this thread

Here I am applying the definition of the week that Stata uses. Specifically, week 1 of any given year begins on January 1, regardless of what day of the week that falls on.

As this below in first quarter of 2020:
WEEK 1 : 1 Jan to 7 Jan 2020
WEEK 2 : 8 Jan to 14 Jan 2020
WEEK 3 : 15 Jan to 21 Jan 2020
WEEK 4 : 22 Jan to 28 Jan 2020
WEEK 4 : 29 Jan to 4 Feb 2020
WEEK 5 : 5 4 Feb to 11 Feb 2020 ............ so on and so forth in all quarters

In this case, I think we mapped it to 13 weeks per quarter.

Could you help me merge the panel datasets and Trade data?

The panel data

* Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte WEEK str68 industry float(qdate wanted) byte quarter float wdate
10493040101  4 "Does not apply"                                                       238 3096 3  30
10493040101  4 "Does not apply"                                                       239 3110 4  43
10493040101  4 "Does not apply"                                                       240 3123 5  56
10493040101  4 "Does not apply"                                                       241 3136 6  69
10493040101  4 "Does not apply"                                                       242 3149 7  82
10694020101  6 "Does not apply"                                                       239 3112 4  45
10694020101  6 "Does not apply"                                                       240 3125 5  58
10694020101  6 "Does not apply"                                                       241 3138 6  71
10694020101  6 "Does not apply"                                                       242 3151 7  84
10694020101  6 "Does not apply"                                                       243 3164 8  97
10694020102  6 "Does not apply"                                                       239 3112 4  45
10694020102  6 "Does not apply"                                                       240 3125 5  58
10694020102  6 "Does not apply"                                                       241 3138 6  71
10694020102  6 "Does not apply"                                                       242 3151 7  84
10694020102  6 "Does not apply"                                                       243 3164 8  97
10792030101  7 "Does not apply"                                                       237 3086 2  20
10792030101  7 "Does not apply"                                                       238 3099 3  33
10792030101  7 "Does not apply"                                                       239 3113 4  46
10792030101  7 "Does not apply"                                                       240 3126 5  59
10792030101  7 "Does not apply"                                                       241 3139 6  72
10793010101  7 "Public admin and defence"                                             238 3099 3  33
10793010101  7 "Public admin and defence"                                             239 3113 4  46
10793010101  7 "Public admin and defence"                                             240 3126 5  59
10793010101  7 "Public admin and defence"                                             241 3139 6  72
10793010101  7 "Public admin and defence"                                             242 3152 7  85
10794010101  7 "Accommodation And Food Service Activities"                            239 3113 4  46
10794010101  7 "Accommodation And Food Service Activities"                            240 3126 5  59
10794010101  7 "Accommodation And Food Service Activities"                            241 3139 6  72
10794010101  7 "Accommodation And Food Service Activities"                            242 3152 7  85
10794010101  7 "Accommodation And Food Service Activities"                            243 3165 8  98
10794010102  7 "Accommodation And Food Service Activities"                            239 3113 4  46
10794010102  7 "Accommodation And Food Service Activities"                            240 3126 5  59
10794010102  7 "Accommodation And Food Service Activities"                            241 3139 6  72
10794010102  7 "Accommodation And Food Service Activities"                            242 3152 7  85
10794010102  7 "Accommodation And Food Service Activities"                            243 3165 8  98
10993020101  9 "Education"                                                            238 3101 3  35
10993020101  9 "Education"                                                            239 3115 4  48
10993020101  9 "Education"                                                            240 3128 5  61
10993020101  9 "Education"                                                            241 3141 6  74
10993020101  9 "Does not apply"                                                       242 3154 7  87
10993020102  9 "Public admin and defence"                                             238 3101 3  35
10993020102  9 "Public admin and defence"                                             239 3115 4  48
10993020102  9 "Public admin and defence"                                             240 3128 5  61
10993020102  9 "Public admin and defence"                                             241 3141 6  74
10993020102  9 "Public admin and defence"                                             242 3154 7  87
11091010101 10 "Education"                                                            236 3077 1  10
11091010101 10 "Education"                                                            237 3089 2  23
11091010101 10 "Education"                                                            238 3102 3  36
11091010101 10 "Education"                                                            239 3116 4  49
11091010101 10 "Education"                                                            240 3129 5  62
11093030101 10 "Does not apply"                                                       238 3102 3  36
11093030101 10 "Does not apply"                                                       239 3116 4  49
11093030101 10 "Does not apply"                                                       240 3129 5  62
11093030101 10 "Does not apply"                                                       241 3142 6  75
11093030101 10 "Does not apply"                                                       242 3155 7  88
11094010101 10 "Public admin and defence"                                             239 3116 4  49
11094010101 10 "Does not apply"                                                       240 3129 5  62
11094010101 10 "Does not apply"                                                       241 3142 6  75
11094010101 10 "Does not apply"                                                       242 3155 7  88
11094010101 10 "Does not apply"                                                       243 3168 8 101
11094010102 10 "Human Health And Social Work Activities"                              239 3116 4  49
11094010102 10 "Human Health And Social Work Activities"                              240 3129 5  62
11094010102 10 "Human Health And Social Work Activities"                              241 3142 6  75
11094010102 10 "Human Health And Social Work Activities"                              242 3155 7  88
11094010102 10 "Human Health And Social Work Activities"                              243 3168 8 101
11291020101 12 "Does not apply"                                                       236 3079 1  12
11291020101 12 "Does not apply"                                                       237 3091 2  25
11291020101 12 "Does not apply"                                                       238 3104 3  38
11291020101 12 "Does not apply"                                                       239 3118 4  51
11291020101 12 "Does not apply"                                                       240 3131 5  64
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237 3091 2  25
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238 3104 3  38
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239 3118 4  51
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240 3131 5  64
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 241 3144 6  77
11294030101 12 "Public admin and defence"                                             239 3118 4  51
11294030101 12 "Public admin and defence"                                             240 3131 5  64
11294030101 12 "Public admin and defence"                                             241 3144 6  77
11294030101 12 "Public admin and defence"                                             242 3157 7  90
11294030101 12 "Public admin and defence"                                             243 3170 8 103
11294030102 12 "Does not apply"                                                       239 3118 4  51
11294030102 12 "Does not apply"                                                       240 3131 5  64
11294030102 12 "Does not apply"                                                       241 3144 6  77
11294030102 12 "Does not apply"                                                       242 3157 7  90
11294030102 12 "Does not apply"                                                       243 3170 8 103
20191020101  1 "Education"                                                            236 3068 1   1
20191020101  1 "Education"                                                            237 3080 2  14
20191020101  1 "Education"                                                            238 3093 3  27
20191020101  1 "Education"                                                            239 3107 4  40
20191020101  1 "Education"                                                            240 3120 5  53
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 236 3068 1   1
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237 3080 2  14
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238 3093 3  27
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239 3107 4  40
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240 3120 5  53
20191040101  1 "Other service activities"                                             236 3068 1   1
20191040101  1 "Other service activities"                                             237 3080 2  14
20191040101  1 "Other service activities"                                             238 3093 3  27
20191040101  1 "Other service activities"                                             239 3107 4  40
20191040101  1 "Other service activities"                                             240 3120 5  53
end
format %tq qdate
format %tw wanted
label values WEEK WEEK
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 wdate wdate
label def wdate 1 "Jan-Mar 2019 1", modify
label def wdate 10 "Jan-Mar 2019 10", modify
label def wdate 12 "Jan-Mar 2019 12", modify
label def wdate 14 "April-June 2019 1", modify
label def wdate 20 "April-June 2019 7", modify
label def wdate 23 "April-June 2019 10", modify
label def wdate 25 "April-June 2019 12", modify
label def wdate 27 "July-Sep 2019 1", modify
label def wdate 30 "July-Sep 2019 4", modify
label def wdate 33 "July-Sep 2019 7", modify
label def wdate 35 "July-Sep 2019 9", modify
label def wdate 36 "July-Sep 2019 10", modify
label def wdate 38 "July-Sep 2019 12", modify
label def wdate 40 "Oct-Des 2019 1", modify
label def wdate 43 "Oct-Des 2019 4", modify
label def wdate 45 "Oct-Des 2019 6", modify
label def wdate 46 "Oct-Des 2019 7", modify
label def wdate 48 "Oct-Des 2019 9", modify
label def wdate 49 "Oct-Des 2019 10", modify
label def wdate 51 "Oct-Des 2019 12", modify
label def wdate 53 "Jan-Mar 2020 1", modify
label def wdate 56 "Jan-Mar 2020 4", modify
label def wdate 58 "Jan-Mar 2020 6", modify
label def wdate 59 "Jan-Mar 2020 7", modify
label def wdate 61 "Jan-Mar 2020 9", modify
label def wdate 62 "Jan-Mar 2020 10", modify
label def wdate 64 "Jan-Mar 2020 12", modify
label def wdate 69 "April-June 2020 4", modify
label def wdate 71 "April-June 2020 6", modify
label def wdate 72 "April-June 2020 7", modify
label def wdate 74 "April-June 2020 9", modify
label def wdate 75 "April-June 2020 10", modify
label def wdate 77 "April-June 2020 12", modify
label def wdate 82 "July-Sep 2020 4", modify
label def wdate 84 "July-Sep 2020 6", modify
label def wdate 85 "July-Sep 2020 7", modify
label def wdate 87 "July-Sep 2020 9", modify
label def wdate 88 "July-Sep 2020 10", modify
label def wdate 90 "July-Sep 2020 12", modify
label def wdate 97 "Oct-Des 2020 6", modify
label def wdate 98 "Oct-Des 2020 7", modify
label def wdate 101 "Oct-Des 2020 10", modify
label def wdate 103 "Oct-Des 2020 12", modify

So I used your suggestion and Nick's suggestion in this thread to create a weekly variable

This might work, depending on how weeks are defined. I believe I have, in an earlier thread of yours, explained that there are different definitions of week in use, and these different definitions would lead to different results. Here I am applying the definition of week that Stata uses. Specifically week 1 of any given year begins on January 1, regardless of what day of the week that falls on. A new week begins every 7 days thereafter, except that at the end of the year, the 52nd week will be longer than 7 days and include however many additional days are needed to reach through December 31 (this number will vary depending on whether it is a leap year). In particular, there is no abbreviated 53rd week.

Added: Notice that under this definition of week, some weeks will overlap two quarters. For example, the last day of 2019Q1, 31 Mar 2019, and the first day of 2019Q2, 1 Apr 2019, are both in the 13th week of year 2019. Because the duration of a year is never a multiple of 7 days, and that of a quarter is only sometimes so, anomalies like this arise in any attempt to frame larger units of time into weeks. That is, although the different ways weeks can be defined will give rise to different anomalies, it is mathematically inevitable that any definition of week leads to some kind of anomaly.

gen int qdate = quarter + tq(2018q4)
format qdate %tq
gen wanted = WEEK + wofd(dofq(qdate)) - 1
format wanted %tw

Somehow the definition you're using implies that 365 or 366 days in a year are always mapped to 13 weeks per quarter.

That should allow you to define your own numbering as a composite of quarter and week.

If this definition is as strict as implied, you can and should keep clear of Stata's own weekly functions and formats.

egen wdate = group(quarter week), label 

.

And this is the trade data

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

I think we get overlaps in the date of trade data with the week in panel data. So what is the best way to merge these datasets based on week time? Is it possible for panel data to create a variable for two weeks, so this gives us an easy way to merge the trade data because the trade data has a time variable representing two weeks?

Thanks for your time with me

[Clyde Schechter]

Weeks are trouble. Two-week units will be no better. They are not commensurate with years or quarters or half-years. There are a number of different ways of defining them. I have asked you about this before, and you still have not indicated how the weeks in your study data set are defined. Perhaps you just don't know. Fair enough, but in that case it is simply not possible to proceed to match weeks with ranges of daily dates when we don't know how to decide which dates belong to which weeks. So until that is answered, there really isn't anything more to say about this.

[Dalia Toame]

Weeks are trouble. Two-week units will be no better. They are not commensurate with years or quarters or half-years. There are a number of different ways of defining them. I have asked you about this before, and you still have not indicated how the weeks in your study data set are defined. Perhaps you just don't know. Fair enough, but in that case it is simply not possible to proceed to match weeks with ranges of daily dates when we don't know how to decide which dates belong to which weeks. So until that is answered, there really isn't anything more to say about this.

Thank you, Clyde for your reply

I've read all the documents that came with the data, and they are mentioned below:



First quarter in 2020
The first week of the quarter is Week 1, which starts on Wednesday, January 1, 2020, and ends on Tuesday, January 7, 2020. The last week of the quarter is Week 13, which starts on Wednesday, March 25, 2019, and ends on Tuesday, March 31, 2020

Second quarter in 2020
The first week of the quarter is Week 1, which starts on Wednesday, April 1, 2020, and ends on Tuesday, April 7, 2020. The last week of the quarter is Week 13, which starts on Wednesday, June 24, 2020, and ends on Tuesday, June 30, 2020.

And we apply the same way for all subsequent quarters.

The 13-week reference period is based on a rolling reference period, which means that each reference week is the same week in the quarter, regardless of the year. For example, the first reference week of the first quarter is always the first week of January, and the first reference week of the second quarter is always the first week of April.


Is that the answer to the WEEK definition?

[Clyde Schechter]

Almost. The unspecified part of your definition is how to handle the end of the quarter. The two examples you give 2020Q1 and 2020Q2 are the easy cases because both of those happen to be 91 days long, and hence, exactly 13 weeks (where a week is 7 consecutive days). But Q3 and Q4 of any year are 92 days long each, so there is a day left over at the end. The question is whether that last day would show up in your data set as week 14 of that quarter (a 1-day rump "week") or whether "week" 13 of that quarter is extended to 8 days length. There is a related problem that occurs in Q1 of any non-leap year: the first quarter will be only 90 days long. So the final "week" would be only 6 days long.

My assumption will be that the 13th "week" of any quarter always ends with the final day of the quarter, even if that means that that "week" has 6 or 8 days.

There is another issue where I am not sure what you want. In the final result you want to pair up the observations in the two data sets where the week of the quarter matches up with the range of dates in the other data set's variable named date (which is actually a pair of dates defining a range). What I do not recall (if you did say it earlier in the thread, I apologize for not remembering and not being able to find it in review), is whether a match up is when the "week" is entirely contained in the date range, or whether a match up is when the "week" overlaps with the date range. I am assuming the latter.

If either assumption is not correct, post back and I can modify the code accordingly.

* Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte WEEK str68 industry float qdate
10493040101  4 "Does not apply"                                                       238
10493040101  4 "Does not apply"                                                       239
10493040101  4 "Does not apply"                                                       240
10493040101  4 "Does not apply"                                                       241
10493040101  4 "Does not apply"                                                       242
10694020101  6 "Does not apply"                                                       239
10694020101  6 "Does not apply"                                                       240
10694020101  6 "Does not apply"                                                       241
10694020101  6 "Does not apply"                                                       242
10694020101  6 "Does not apply"                                                       243
10694020102  6 "Does not apply"                                                       239
10694020102  6 "Does not apply"                                                       240
10694020102  6 "Does not apply"                                                       241
10694020102  6 "Does not apply"                                                       242
10694020102  6 "Does not apply"                                                       243
10792030101  7 "Does not apply"                                                       237
10792030101  7 "Does not apply"                                                       238
10792030101  7 "Does not apply"                                                       239
10792030101  7 "Does not apply"                                                       240
10792030101  7 "Does not apply"                                                       241
10793010101  7 "Public admin and defence"                                             238
10793010101  7 "Public admin and defence"                                             239
10793010101  7 "Public admin and defence"                                             240
10793010101  7 "Public admin and defence"                                             241
10793010101  7 "Public admin and defence"                                             242
10794010101  7 "Accommodation And Food Service Activities"                            239
10794010101  7 "Accommodation And Food Service Activities"                            240
10794010101  7 "Accommodation And Food Service Activities"                            241
10794010101  7 "Accommodation And Food Service Activities"                            242
10794010101  7 "Accommodation And Food Service Activities"                            243
10794010102  7 "Accommodation And Food Service Activities"                            239
10794010102  7 "Accommodation And Food Service Activities"                            240
10794010102  7 "Accommodation And Food Service Activities"                            241
10794010102  7 "Accommodation And Food Service Activities"                            242
10794010102  7 "Accommodation And Food Service Activities"                            243
10993020101  9 "Education"                                                            238
10993020101  9 "Education"                                                            239
10993020101  9 "Education"                                                            240
10993020101  9 "Education"                                                            241
10993020101  9 "Does not apply"                                                       242
10993020102  9 "Public admin and defence"                                             238
10993020102  9 "Public admin and defence"                                             239
10993020102  9 "Public admin and defence"                                             240
10993020102  9 "Public admin and defence"                                             241
10993020102  9 "Public admin and defence"                                             242
11091010101 10 "Education"                                                            236
11091010101 10 "Education"                                                            237
11091010101 10 "Education"                                                            238
11091010101 10 "Education"                                                            239
11091010101 10 "Education"                                                            240
11093030101 10 "Does not apply"                                                       238
11093030101 10 "Does not apply"                                                       239
11093030101 10 "Does not apply"                                                       240
11093030101 10 "Does not apply"                                                       241
11093030101 10 "Does not apply"                                                       242
11094010101 10 "Public admin and defence"                                             239
11094010101 10 "Does not apply"                                                       240
11094010101 10 "Does not apply"                                                       241
11094010101 10 "Does not apply"                                                       242
11094010101 10 "Does not apply"                                                       243
11094010102 10 "Human Health And Social Work Activities"                              239
11094010102 10 "Human Health And Social Work Activities"                              240
11094010102 10 "Human Health And Social Work Activities"                              241
11094010102 10 "Human Health And Social Work Activities"                              242
11094010102 10 "Human Health And Social Work Activities"                              243
11291020101 12 "Does not apply"                                                       236
11291020101 12 "Does not apply"                                                       237
11291020101 12 "Does not apply"                                                       238
11291020101 12 "Does not apply"                                                       239
11291020101 12 "Does not apply"                                                       240
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 241
11294030101 12 "Public admin and defence"                                             239
11294030101 12 "Public admin and defence"                                             240
11294030101 12 "Public admin and defence"                                             241
11294030101 12 "Public admin and defence"                                             242
11294030101 12 "Public admin and defence"                                             243
11294030102 12 "Does not apply"                                                       239
11294030102 12 "Does not apply"                                                       240
11294030102 12 "Does not apply"                                                       241
11294030102 12 "Does not apply"                                                       242
11294030102 12 "Does not apply"                                                       243
20191020101  1 "Education"                                                            236
20191020101  1 "Education"                                                            237
20191020101  1 "Education"                                                            238
20191020101  1 "Education"                                                            239
20191020101  1 "Education"                                                            240
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 236
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240
20191040101  1 "Other service activities"                                             236
20191040101  1 "Other service activities"                                             237
20191040101  1 "Other service activities"                                             238
20191040101  1 "Other service activities"                                             239
20191040101  1 "Other service activities"                                             240
end
format %tq qdate

tempfile dataset1
save `dataset1'

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

tempfile dataset2
save `dataset2'

//  IDENTIFY START AND FINISH DATES OF EACH "WEEK"
use `dataset1', clear
gen week_start = dofq(qdate) + (WEEK-1)*7
gen week_finish = ///
    cond(WEEK == 13, dofq(qdate+1)-1, week_start + 6)
format week_start week_finish %td
tempfile dataset1_modified
save `dataset1_modified'

//  EXTRACT START AND FINISH DATES OF EACH PERIOD
use `dataset2', clear
gen period_start = daily(substr(date, 1, 10), "DMY")
assert missing(period_start) == missing(date)
gen period_finish = daily(substr(date, 15, 10), "DMY")
assert missing(period_finish) == missing(date)
format period_start period_finish %td

//  MATCH ALL PAIRS OF OBSERVATIONS WHERE THE PERIOD AND THE "WEEK" OVERLAP
joinby industry using `dataset1_modified'
keep if min(week_finish, period_finish) >= max(week_start, period_start)

[Dalia Toame]

.

My assumption will be that the 13th "week" of any quarter always ends with the final day of the quarter, even if that means that that "week" has 6 or 8 days.

Yes, I assume that the quarter is always 13 weeks.

There is another issue where I am not sure what you want. In the final result, you want to pair up the observations in the two data sets where the week of the quarter matches up with the range of dates in the other data set's variable named date (which is actually a pair of dates defining a range). What I do not recall (if you did say it earlier in the thread, I apologize for not remembering and not being able to find it in review), is whether a match-up is when the "week" is entirely contained in the date range, or whether a match up is when the "week" overlaps with the date range. I am assuming the latter.

Here I go with the first assumption (different from your assumption). In the final result, I would like to get pair for all my observations where the WEKK of the quarter (panel data ) matches up with the range data(trade data). In other words, it matches up when the WEEK variable is entirely contained in the data range, so I need to get matches all weeks with data ranges.

Please note that I need to keep my observation pre-2020 still in my sample. When I used the above command to merge the data, it led to dropping all observations before 2020, so I would like to keep all the periods on ( Q1 2019 to Q4 2020)

Last time could you help with that?

Many thanks for your time

[Clyde Schechter]

It's just a few changes to the final two lines of the code:

* Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte WEEK str68 industry float qdate
10493040101  4 "Does not apply"                                                       238
10493040101  4 "Does not apply"                                                       239
10493040101  4 "Does not apply"                                                       240
10493040101  4 "Does not apply"                                                       241
10493040101  4 "Does not apply"                                                       242
10694020101  6 "Does not apply"                                                       239
10694020101  6 "Does not apply"                                                       240
10694020101  6 "Does not apply"                                                       241
10694020101  6 "Does not apply"                                                       242
10694020101  6 "Does not apply"                                                       243
10694020102  6 "Does not apply"                                                       239
10694020102  6 "Does not apply"                                                       240
10694020102  6 "Does not apply"                                                       241
10694020102  6 "Does not apply"                                                       242
10694020102  6 "Does not apply"                                                       243
10792030101  7 "Does not apply"                                                       237
10792030101  7 "Does not apply"                                                       238
10792030101  7 "Does not apply"                                                       239
10792030101  7 "Does not apply"                                                       240
10792030101  7 "Does not apply"                                                       241
10793010101  7 "Public admin and defence"                                             238
10793010101  7 "Public admin and defence"                                             239
10793010101  7 "Public admin and defence"                                             240
10793010101  7 "Public admin and defence"                                             241
10793010101  7 "Public admin and defence"                                             242
10794010101  7 "Accommodation And Food Service Activities"                            239
10794010101  7 "Accommodation And Food Service Activities"                            240
10794010101  7 "Accommodation And Food Service Activities"                            241
10794010101  7 "Accommodation And Food Service Activities"                            242
10794010101  7 "Accommodation And Food Service Activities"                            243
10794010102  7 "Accommodation And Food Service Activities"                            239
10794010102  7 "Accommodation And Food Service Activities"                            240
10794010102  7 "Accommodation And Food Service Activities"                            241
10794010102  7 "Accommodation And Food Service Activities"                            242
10794010102  7 "Accommodation And Food Service Activities"                            243
10993020101  9 "Education"                                                            238
10993020101  9 "Education"                                                            239
10993020101  9 "Education"                                                            240
10993020101  9 "Education"                                                            241
10993020101  9 "Does not apply"                                                       242
10993020102  9 "Public admin and defence"                                             238
10993020102  9 "Public admin and defence"                                             239
10993020102  9 "Public admin and defence"                                             240
10993020102  9 "Public admin and defence"                                             241
10993020102  9 "Public admin and defence"                                             242
11091010101 10 "Education"                                                            236
11091010101 10 "Education"                                                            237
11091010101 10 "Education"                                                            238
11091010101 10 "Education"                                                            239
11091010101 10 "Education"                                                            240
11093030101 10 "Does not apply"                                                       238
11093030101 10 "Does not apply"                                                       239
11093030101 10 "Does not apply"                                                       240
11093030101 10 "Does not apply"                                                       241
11093030101 10 "Does not apply"                                                       242
11094010101 10 "Public admin and defence"                                             239
11094010101 10 "Does not apply"                                                       240
11094010101 10 "Does not apply"                                                       241
11094010101 10 "Does not apply"                                                       242
11094010101 10 "Does not apply"                                                       243
11094010102 10 "Human Health And Social Work Activities"                              239
11094010102 10 "Human Health And Social Work Activities"                              240
11094010102 10 "Human Health And Social Work Activities"                              241
11094010102 10 "Human Health And Social Work Activities"                              242
11094010102 10 "Human Health And Social Work Activities"                              243
11291020101 12 "Does not apply"                                                       236
11291020101 12 "Does not apply"                                                       237
11291020101 12 "Does not apply"                                                       238
11291020101 12 "Does not apply"                                                       239
11291020101 12 "Does not apply"                                                       240
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 241
11294030101 12 "Public admin and defence"                                             239
11294030101 12 "Public admin and defence"                                             240
11294030101 12 "Public admin and defence"                                             241
11294030101 12 "Public admin and defence"                                             242
11294030101 12 "Public admin and defence"                                             243
11294030102 12 "Does not apply"                                                       239
11294030102 12 "Does not apply"                                                       240
11294030102 12 "Does not apply"                                                       241
11294030102 12 "Does not apply"                                                       242
11294030102 12 "Does not apply"                                                       243
20191020101  1 "Education"                                                            236
20191020101  1 "Education"                                                            237
20191020101  1 "Education"                                                            238
20191020101  1 "Education"                                                            239
20191020101  1 "Education"                                                            240
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 236
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240
20191040101  1 "Other service activities"                                             236
20191040101  1 "Other service activities"                                             237
20191040101  1 "Other service activities"                                             238
20191040101  1 "Other service activities"                                             239
20191040101  1 "Other service activities"                                             240
end
format %tq qdate

tempfile dataset1
save `dataset1'

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

tempfile dataset2
save `dataset2'

//  IDENTIFY START AND FINISH DATES OF EACH "WEEK"
use `dataset1', clear
gen week_start = dofq(qdate) + (WEEK-1)*7
gen week_finish = ///
    cond(WEEK == 13, dofq(qdate+1)-1, week_start + 6)
format week_start week_finish %td
tempfile dataset1_modified
save `dataset1_modified'

//  EXTRACT START AND FINISH DATES OF EACH PERIOD
use `dataset2', clear
gen period_start = daily(substr(date, 1, 10), "DMY")
assert missing(period_start) == missing(date)
gen period_finish = daily(substr(date, 15, 10), "DMY")
assert missing(period_finish) == missing(date)
format period_start period_finish %td

//  MATCH ALL PAIRS OF OBSERVATIONS WHERE THE "WEEK" IS COMPLETELY
//  CONTAINED WITHIN THE PERIOD
joinby industry using `dataset1_modified', unmatched(using)
keep if (week_finish <= period_finish & week_start >= period_start)  ///
    | _merge == 2

[Dalia Toame]

I appreciate your help with that. Now it is working. However, I notice something doesn't make sense to me. Might I don't have a clue in this matter.

When I ran the first line of this code, the datasets merged, which gave me around 745000 observations. Then when running the second line of code, it deleted around 745200 observations, and I left with only 46000.

// MATCH ALL PAIRS OF OBSERVATIONS WHERE THE "WEEK" IS COMPLETELY
// CONTAINED WITHIN THE PERIOD
joinby industry using `dataset1_modified', unmatched(using)
keep if (week_finish <= period_finish & week_start >= period_start) ///
| _merge == 2
[/code]

My panel data (dataset1) is around 70000 observations. I think this does not make sense because we expect to increase the number of observations when we merge two datasets. I don't know where the problem is. What do you think?


May I ask you to look at this and get your comments?

[Clyde Schechter]

Yes, you are right. Observations from the first data set that do not match to anything in the second were, in some instances being lost. The following code fixes that:

clear*
* Example generated by -dataex-. For more info, type help dataex
clear
input double PERSID byte WEEK str68 industry float qdate
10493040101  4 "Does not apply"                                                       238
10493040101  4 "Does not apply"                                                       239
10493040101  4 "Does not apply"                                                       240
10493040101  4 "Does not apply"                                                       241
10493040101  4 "Does not apply"                                                       242
10694020101  6 "Does not apply"                                                       239
10694020101  6 "Does not apply"                                                       240
10694020101  6 "Does not apply"                                                       241
10694020101  6 "Does not apply"                                                       242
10694020101  6 "Does not apply"                                                       243
10694020102  6 "Does not apply"                                                       239
10694020102  6 "Does not apply"                                                       240
10694020102  6 "Does not apply"                                                       241
10694020102  6 "Does not apply"                                                       242
10694020102  6 "Does not apply"                                                       243
10792030101  7 "Does not apply"                                                       237
10792030101  7 "Does not apply"                                                       238
10792030101  7 "Does not apply"                                                       239
10792030101  7 "Does not apply"                                                       240
10792030101  7 "Does not apply"                                                       241
10793010101  7 "Public admin and defence"                                             238
10793010101  7 "Public admin and defence"                                             239
10793010101  7 "Public admin and defence"                                             240
10793010101  7 "Public admin and defence"                                             241
10793010101  7 "Public admin and defence"                                             242
10794010101  7 "Accommodation And Food Service Activities"                            239
10794010101  7 "Accommodation And Food Service Activities"                            240
10794010101  7 "Accommodation And Food Service Activities"                            241
10794010101  7 "Accommodation And Food Service Activities"                            242
10794010101  7 "Accommodation And Food Service Activities"                            243
10794010102  7 "Accommodation And Food Service Activities"                            239
10794010102  7 "Accommodation And Food Service Activities"                            240
10794010102  7 "Accommodation And Food Service Activities"                            241
10794010102  7 "Accommodation And Food Service Activities"                            242
10794010102  7 "Accommodation And Food Service Activities"                            243
10993020101  9 "Education"                                                            238
10993020101  9 "Education"                                                            239
10993020101  9 "Education"                                                            240
10993020101  9 "Education"                                                            241
10993020101  9 "Does not apply"                                                       242
10993020102  9 "Public admin and defence"                                             238
10993020102  9 "Public admin and defence"                                             239
10993020102  9 "Public admin and defence"                                             240
10993020102  9 "Public admin and defence"                                             241
10993020102  9 "Public admin and defence"                                             242
11091010101 10 "Education"                                                            236
11091010101 10 "Education"                                                            237
11091010101 10 "Education"                                                            238
11091010101 10 "Education"                                                            239
11091010101 10 "Education"                                                            240
11093030101 10 "Does not apply"                                                       238
11093030101 10 "Does not apply"                                                       239
11093030101 10 "Does not apply"                                                       240
11093030101 10 "Does not apply"                                                       241
11093030101 10 "Does not apply"                                                       242
11094010101 10 "Public admin and defence"                                             239
11094010101 10 "Does not apply"                                                       240
11094010101 10 "Does not apply"                                                       241
11094010101 10 "Does not apply"                                                       242
11094010101 10 "Does not apply"                                                       243
11094010102 10 "Human Health And Social Work Activities"                              239
11094010102 10 "Human Health And Social Work Activities"                              240
11094010102 10 "Human Health And Social Work Activities"                              241
11094010102 10 "Human Health And Social Work Activities"                              242
11094010102 10 "Human Health And Social Work Activities"                              243
11291020101 12 "Does not apply"                                                       236
11291020101 12 "Does not apply"                                                       237
11291020101 12 "Does not apply"                                                       238
11291020101 12 "Does not apply"                                                       239
11291020101 12 "Does not apply"                                                       240
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240
11292020101 12 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 241
11294030101 12 "Public admin and defence"                                             239
11294030101 12 "Public admin and defence"                                             240
11294030101 12 "Public admin and defence"                                             241
11294030101 12 "Public admin and defence"                                             242
11294030101 12 "Public admin and defence"                                             243
11294030102 12 "Does not apply"                                                       239
11294030102 12 "Does not apply"                                                       240
11294030102 12 "Does not apply"                                                       241
11294030102 12 "Does not apply"                                                       242
11294030102 12 "Does not apply"                                                       243
20191020101  1 "Education"                                                            236
20191020101  1 "Education"                                                            237
20191020101  1 "Education"                                                            238
20191020101  1 "Education"                                                            239
20191020101  1 "Education"                                                            240
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 236
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 237
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 238
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 239
20191020102  1 "Wholesale And Retail Trade; Repair Of Motor Vehicles And Motorcycles" 240
20191040101  1 "Other service activities"                                             236
20191040101  1 "Other service activities"                                             237
20191040101  1 "Other service activities"                                             238
20191040101  1 "Other service activities"                                             239
20191040101  1 "Other service activities"                                             240
end
format %tq qdate

tempfile dataset1
save `dataset1'

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

tempfile dataset2
save `dataset2'

//  IDENTIFY START AND FINISH DATES OF EACH "WEEK"
use `dataset1', clear
gen week_start = dofq(qdate) + (WEEK-1)*7
gen week_finish = ///
    cond(WEEK == 13, dofq(qdate+1)-1, week_start + 6)
format week_start week_finish %td
tempfile dataset1_modified
gen `c(obs_t)' obs_no = _n
save `dataset1_modified'

//  EXTRACT START AND FINISH DATES OF EACH PERIOD
use `dataset2', clear
gen period_start = daily(substr(date, 1, 10), "DMY")
assert missing(period_start) == missing(date)
gen period_finish = daily(substr(date, 15, 10), "DMY")
assert missing(period_finish) == missing(date)
format period_start period_finish %td

//  MATCH ALL PAIRS OF OBSERVATIONS WHERE THE "WEEK" IS COMPLETELY
//  CONTAINED WITHIN THE PERIOD
joinby industry using `dataset1_modified',
drop if (week_finish > period_finish | week_start < period_start)
merge m:1 obs_no using `dataset1_modified', assert(match using) nogenerate

[Dalia Toame]

* 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.

Thank you for your previous valuable advice and cooperation. I have merged my two datasets and have reached the analysis stage and found that lead me to some error results because I have to re coded the “Trade data” to eliminate this error but before to go in depth let me to remind what I did last time:
I have quarterly panel data. I want to merge external data (Trade data) matching to a factor variable in the panel data (Inde07m).



The panel data 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 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.


The Trade data has 19 waves of how Covid-19 affected the industry in the labour market (each wave constitutes two weeks) containing divisions of the variable "Inde07m"


You helped me with advising this command to merge :

 //  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'  
 //  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 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

After merging, I faced the problem that the “Lockdown” variable contained zero values because the "trade data" is the closing percentage every two weeks. Therefore, when combined with the panel data on a quarterly basis, it gives us that there are approximately seven weeks in each quarter for which there is no lockdown info.

The following figure shows that I have a value of zero for each industry most of the weeks. It can't be zero because zero is a genuine value in the data (but here, it is not real). The reason is that there are weeks for which there is no data about lockdown. So, I need to decide what to do with the weeks in between. Therefore, I can assume the week between is the same value as the one before. I want to fill in the weeks that have no actual value so they have the same value as the week before.

In the end, the final form of the Trade date will be as follows:

The first week: 23/03/2020 to 29/03/2020 , manufacturing, Lockdown 22.7%
The second week:30/03/2020 to 05/04/2020, manufacturing, Lockdown 22.7 %

So, the value of the week is the same value as the first week.




I think I need to do a REPREPARE TRADE DATA SET FOR MERGER to fill the week we do not have information about the lockdown. Is there a way to solve this and how to do that ?


I really appreciate your advice and waiting for your help.

[Clyde Schechter]

It's been 7 months since I last saw this, and, honestly, I don't remember very much about it. I suspect that had today's post come within a few days of when the conversation last dropped, I probably would have understood it, maybe after a couple of readings. But I don't.

I think I can be more helpful to you if you write a post with a focused question about the data in the state you currently have it: how we got there probably won't matter. (And if it turns out it does matter, I can review the earlier posts in the thread with the current context in mind.) Please provide a -dataex- example of that current data, and a specific question about what you want to do with it, and I'll try to answer it.