delay

[Steve Bereznai]

Good morning!

I have 2 files (observations = 43 617). In the first file are countries with a lot of information (in the example I will not write down them, just the necessary variables). In the second file is the unemployment information about the countries.

I want to merge these file, BUT unemployment rates are expected to have a delayed impact, affecting one’s job situation after some time, I want to assign respondents with the quarterly unemployment rate that prevailed in their respective countries three months before the date (month/year).

For example: Belgium 2004. April. (1. File) necessary Belgium 2004. Q1 -------> 4 (April) - 3 = 1 (January - Q1)

I show an example:

The first file:

Country	Year	Month
Austria	2004	January
Austria	2004	June
Belgium	2004	April
Belgium	2004	May
Germany	2004	May
Germany	2011	May
Germany	2013	May
France	2011	September
France	2013	December

The second file (it contains unemployment rate of country):

Country	Time	Value
Austria	2003 - Q4	1.12
Austria	2004 - Q1	1.14
Belgium	2004 - Q1	1.23
France	2011 - Q2	1.56
Germany	2004 - Q1	1.33
Germany	2011 - Q1	1.39
Germany	2013 - Q1	1.30
France	2013 - Q3	1.51

• January, February, and March (Q1)
• April, May, and June (Q2)
• July, August, and September (Q3)
• October, November, and December (Q4)

I solved:



Code:
gen quarter= "" replace quarter = "Q1" if inlist(Month, "January", "February", "March") replace quarter = "Q2" if inlist(Month, "April", "May", "June") and so on.


I can then create the Time variable that I have in my second data set in my first data set by

Code:
gen Time = Year + " - " + quarter
The variable Year is string.

After that I merged my data sets with

Code:
merge 1:1 Country Time using name_of_second_data_set.dta

Result:
The merge was succesful, but expected to have a delayed impact of unemployment rates is missing?

Somebody know, how van I apply the delay impact?

Thanks

[Andrew Musau]

Generate a quarter variable in the first dataset with a three month lag from the current month and merge or joinby. Here is a start. Also refer to FAQ Advice #12 and the request to use dataex to provide data examples.

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 country int year str9 month
"Austria" 2004 "January"  
"Austria" 2004 "June"     
"Belgium" 2004 "April"    
"Belgium" 2004 "May"      
"Germany" 2004 "May"      
"Germany" 2011 "May"      
"Germany" 2013 "May"      
"France"  2011 "September"
"France"  2013 "December" 
end

gen qtr=qofd(dofm(mofd(date(month+" 01 "+string(year), "MDY")) -3))
format qtr %tq

The -dofm()- function undoes what the -mofd()- function did, and it looks like a long mess.

help datetime

to help disentangle the expression.

Res.:

 l, sep(0)

     +-------------------------------------+
     | country   year       month      qtr |
     |-------------------------------------|
  1. | Austria   2004     January   2003q4 |
  2. | Austria   2004        June   2004q1 |
  3. | Belgium   2004       April   2004q1 |
  4. | Belgium   2004         May   2004q1 |
  5. | Germany   2004         May   2004q1 |
  6. | Germany   2011         May   2011q1 |
  7. | Germany   2013         May   2013q1 |
  8. |  France   2011   September   2011q2 |
  9. |  France   2013    December   2013q3 |
     +-------------------------------------+

[Steve Bereznai]

Hi
Thanks for your fast answer

I run it:
gen qtr=qofd(dofm(mofd(date(month+" 01 "+string(year), "MDY")) -3)) Result: type mismatch
Why?

[Andrew Musau]

I do not know. Maybe year is a string variable in your case whereas it is numeric in my example. Read the comment on dataex in #2.

[Steve Bereznai]

I do not know. Maybe year is a string variable in your case whereas it is numeric in my example. Read the comment on dataex in #2.

Year is int (if need I can convert to string)
Month is byte

Which type do I need?

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 country int year byte month
"Austria" 2004 1 
"Austria" 2004 6     
"Belgium" 2004 4    
"Belgium" 2004 5      
"Germany" 2004 5      
"Germany" 2011 5      
"Germany" 2013 5      
"France"  2011 9
"France"  2013 12 
end

gen qtr=qofd(dofm(mofd(mdy(month, 1, year)) -3))
format qtr %tq

Res.:

. l, sep(0)

     +---------------------------------+
     | country   year   month      qtr |
     |---------------------------------|
  1. | Austria   2004       1   2003q4 |
  2. | Austria   2004       6   2004q1 |
  3. | Belgium   2004       4   2004q1 |
  4. | Belgium   2004       5   2004q1 |
  5. | Germany   2004       5   2004q1 |
  6. | Germany   2011       5   2011q1 |
  7. | Germany   2013       5   2013q1 |
  8. |  France   2011       9   2011q2 |
  9. |  France   2013      12   2013q3 |
     +---------------------------------+

.

[Steve Bereznai]

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 country int year byte month
"Austria" 2004 1
"Austria" 2004 6
"Belgium" 2004 4
"Belgium" 2004 5
"Germany" 2004 5
"Germany" 2011 5
"Germany" 2013 5
"France" 2011 9
"France" 2013 12
end

gen qtr=qofd(dofm(mofd(mdy(month, 1, year)) -3))
format qtr %tq

Res.:

. l, sep(0)

+---------------------------------+
| country year month qtr |
|---------------------------------|
1. | Austria 2004 1 2003q4 |
2. | Austria 2004 6 2004q1 |
3. | Belgium 2004 4 2004q1 |
4. | Belgium 2004 5 2004q1 |
5. | Germany 2004 5 2004q1 |
6. | Germany 2011 5 2011q1 |
7. | Germany 2013 5 2013q1 |
8. | France 2011 9 2011q2 |
9. | France 2013 12 2013q3 |
+---------------------------------+

.

You are awesome!! Thanks it works! Just I have the last question:

I have problem with the Merge:

qtr and time are different:

qtr -> 2003q4 (float)
time -> 2003 - Q4 (str) (time comes from the second file)

Have can I converted them?

Thank you

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 country str9 time float value
"Austria" "2003 - Q4" 1.12
"Austria" "2004 - Q1" 1.14
"Belgium" "2004 - Q1" 1.23
"France"  "2011 - Q2" 1.56
"Germany" "2004 - Q1" 1.33
"Germany" "2011 - Q1" 1.39
"Germany" "2013 - Q1"  1.3
"France"  "2013 - Q3" 1.51
end

gen qtr= yq(real(substr(time, 1, 4)), real(substr(time, -1, 1)))
format qtr %tq

Res.:

. l, sep(0)

     +--------------------------------------+
     | country        time   value      qtr |
     |--------------------------------------|
  1. | Austria   2003 - Q4    1.12   2003q4 |
  2. | Austria   2004 - Q1    1.14   2004q1 |
  3. | Belgium   2004 - Q1    1.23   2004q1 |
  4. |  France   2011 - Q2    1.56   2011q2 |
  5. | Germany   2004 - Q1    1.33   2004q1 |
  6. | Germany   2011 - Q1    1.39   2011q1 |
  7. | Germany   2013 - Q1     1.3   2013q1 |
  8. |  France   2013 - Q3    1.51   2013q3 |
     +--------------------------------------+

[Steve Bereznai]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 country str9 time float value
"Austria" "2003 - Q4" 1.12
"Austria" "2004 - Q1" 1.14
"Belgium" "2004 - Q1" 1.23
"France" "2011 - Q2" 1.56
"Germany" "2004 - Q1" 1.33
"Germany" "2011 - Q1" 1.39
"Germany" "2013 - Q1" 1.3
"France" "2013 - Q3" 1.51
end

gen qtr= yq(real(substr(time, 1, 4)), real(substr(time, -1, 1)))
format qtr %tq

Res.:

. l, sep(0)

+--------------------------------------+
| country time value qtr |
|--------------------------------------|
1. | Austria 2003 - Q4 1.12 2003q4 |
2. | Austria 2004 - Q1 1.14 2004q1 |
3. | Belgium 2004 - Q1 1.23 2004q1 |
4. | France 2011 - Q2 1.56 2011q2 |
5. | Germany 2004 - Q1 1.33 2004q1 |
6. | Germany 2011 - Q1 1.39 2011q1 |
7. | Germany 2013 - Q1 1.3 2013q1 |
8. | France 2013 - Q3 1.51 2013q3 |
+--------------------------------------+

Thank you!!!!!! You are awesome!!
I am grateful!
Great job it is working


Have a nice day
Best Regards
Bereznai