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
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
So I used your suggestion and Nick's suggestion in this thread to create a weekly variable
And this is the trade data
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
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
Originally posted by Clyde Schechter
View Post
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
Code:
* 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
Originally posted by Clyde Schechter
View Post
Originally posted by Nick Cox
View Post
And this is the trade data
Code:
* 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
Comment