How to find the range of dates

BB Kim

Join Date: Feb 2023
Posts: 31

#16

04 Mar 2023, 14:43

I want to get the variable of the number of "Past" recession work experience

co_start_date is lifetime career start date, co_end_date is lifetime career end date (CEO 's total career year in his lifetime from co_start_date to co_end_date ; this career is from different firms)

YEAR GVKEY EXECID co_start_date co_end_date
2000 5680 04970 01jan1997 01jan2006
2001 5681 04970 01jan1997 01jan2006
2002 5680 04970 01jan1997 10may2006
2003 24668 04970 01jan1997 10may2006
2004 10920 04970 01jan1997 10may2006
2005 184748 04970 01jan1997 10may2006
2006 8479 04970 01jan1997 10may2006

YEAR GVKEY EXECID co_start_date co_end_date
2002 4598 00016 01mar1955 01oct1994
2003 157415 00016 01mar1955 01oct1994
2004 10920 00016 01mar1955 01oct1994

From example above, CEO id of EXECID 04970 worked in her whole life since 01jan1997 to 10may2006 from 5 different companies (GVKEY)
CEO id of 04970 worked in her whole life since 01mar1955 to 01oct1994 from 3 different companies (GVKEY)

I first want to change career end date (co_end_date) at each YEAR as below.
For ceo id EXECID 04970, I change the co_end_date as the first date of Current calendar year since CEO have worked till the current Year, his lifetime career end date is beyond the current year)

YEAR GVKEY EXECID co_start_date co_end_date
2000 5680 04970 01jan1997 01jan2000
2001 5681 04970 01jan1997 01jan2001
2002 5683 04970 01jan1997 01jan2002
2003 24668 04970 01jan1997 01jan2003
2004 10920 04970 01jan1997 01jan2004
2005 184748 04970 01jan1997 01jan2005
2006 8479 04970 01jan1997 10may2006

For ceo id EXECID 00016, since co_end_date is before YEAR (CEO's career already ended before current YEAR), I do not change and leave as it is.

YEAR GVKEY EXECID co_start_date co_end_date
2002 4598 00016 01mar1955 01oct1994
2003 157415 00016 01mar1955 01oct1994
2004 10920 00016 01mar1955 01oct1994

I want the value of n_recessions_worked of 04970

the value of n_recessions_worked in 2000 is 1 (recession range of 12 before 2000)
the value of n_recessions_worked in 2001 is 1 (recession range of 12 before 2001; current year is 2001 thus does not count the starting year of recession 13;
Do NOT count if starting of recession range year is equal current year since i'm looking at "PAST" recession experience )
the value of n_recessions_worked in 2002 is 2 (recession range of 12 and recession 13 before 2002)
...
the value of n_recessions_worked in 2007 is 2
the value of n_recessions_worked in 2008 is 3 (recession range of 12, 13, 14 before 2008)
...
...
the value of n_recessions_worked in 2011 is 3

(since
local recession12 July 1990 March 1991
local recession13 March 2001 November 2001
local recession14 December 2007 June 2009)

could you please revise previous code to get the value of n_recessions_work?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
My code was:

gen end_date=dofy(YEAR)

format %td end_date

replace co_end_date= end_date if co_end_date >end_date

% I replaced co_end_date if it is later than current year.

From your code, I wanted to replace end_date with co_end_date

foreach v of varlist co_start_date co_end_date {
gen _`v' = daily(`v', "DMY"), after(`v')
assert missing(`v') == missing(_`v')
drop `v'
rename _`v' `v'
format `v' %td
}

But this shows "type mismatch " and

gen n_co_recessions_worked = 0
forvalues i = 1/15 {

replace n_co_recessions_worked = n_co_recessions_worked + 1 if min(co_end_date, `recession_end`i'') >= max(co_start_date, `recession_start`i'')
}

forvalues loop, it says "if not found" error
So what is the problem?

. dataex YEAR GVKEY EXECID co_start_date co_end_date, count(300)

----------------------- copy starting from the next line -----------------------

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input double YEAR str6(GVKEY EXECID) float(co_start_date co_end_date)
2002 "119417" "00008"  3408 16067
2003 "6307"   "00008"  3408 16067
2002 "4598"   "00016" -1767 12692
2003 "157415" "00016" -1767 12692
2004 "10920"  "00016" -1767 12692
2002 "10498"  "00026" 11504 14701
2002 "148294" "00042"   366 21305
2003 "24607"  "00042"   366 21305
2008 "3662"   "00042"   366 21305
2009 "7435"   "00042"   366 21305
2002 "24607"  "00043"  3804 15096
2002 "11456"  "00062" -1095 15521
2003 "147885" "00062" -1095 15521
2004 "15444"  "00062" -1095 15521
2005 "4598"   "00062" -1095 15521
2002 "7139"   "00066"  6118 15796
2003 "10920"  "00066"  6118 15796
2004 "25777"  "00066"  6118 15796
2002 "25313"  "00074"  8248 15584
2006 "62374"  "00075" 17015 17166
2002 "28930"  "00116" 12585 16560
2003 "11259"  "00116" 12585 16560
2004 "5680"   "00116" 12585 16560
2005 "2721"   "00116" 12585 16560
2002 "64902"  "00117"  4169 15796
2003 "3138"   "00117"  4169 15796
2004 "11649"  "00117"  4169 15796
2005 "179741" "00117"  4169 15796
2006 "3362"   "00117"  4169 15796
2007 "30170"  "00117"  4169 15796
2008 "10005"  "00117"  4169 15796
2009 "10920"  "00117"  4169 15796
2010 "5680"   "00117"  4169 15796
2011 "112168" "00117"  4169 15796
2002 "3813"   "00125"  1827 23011
2003 "2393"   "00125"  1827 23011
2004 "10920"  "00125"  1827 23011
2005 "5680"   "00125"  1827 23011
2006 "10983"  "00125"  1827 23011
2007 "17239"  "00125"  1827 23011
2008 "133367" "00125"  1827 23011
2009 "11264"  "00125"  1827 23011
2010 "10443"  "00125"  1827 23011
2011 "24610"  "00125"  1827 23011
2002 "10920"  "00134"  9375 18333
2003 "2710"   "00134"  9375 18333
2004 "65702"  "00134"  9375 18333
2005 "241216" "00134"  9375 18333
2006 "5680"   "00134"  9375 18333
2007 "3144"   "00134"  9375 18333
2008 "5680"   "00134"  9375 18333
2002 "2193"   "00172"  4018 16908
2003 "16603"  "00172"  4018 16908
2005 "6307"   "00172"  4018 16908
2006 "10795"  "00172"  4018 16908
2007 "1722"   "00172"  4018 16908
2008 "6349"   "00172"  4018 16908
2002 "15444"  "00182"  9191 16161
2003 "8215"   "00182"  9191 16161
2002 "10405"  "00210" -1095 12996
2003 "5680"   "00210" -1095 12996
2004 "148256" "00210" -1095 12996
2005 "10920"  "00210" -1095 12996
2002 "1677"   "00229"  1277 17348
2003 "175689" "00229"  1277 17348
2004 "6829"   "00229"  1277 17348
2005 "24607"  "00229"  1277 17348
2006 "4598"   "00229"  1277 17348
2007 "11456"  "00229"  1277 17348
2002 "11456"  "00260"   731 13264
2003 "5680"   "00260"   731 13264
2004 "160163" "00260"   731 13264
2005 "64389"  "00260"   731 13264
2002 "6649"   "00281"  8887 23011
2003 "8214"   "00281"  8887 23011
2004 "10920"  "00281"  8887 23011
2005 "10920"  "00281"  8887 23011
2006 "62374"  "00281"  8887 23011
2007 "10920"  "00281"  8887 23011
2008 "3505"   "00281"  8887 23011
2002 "146500" "00296"  9313 20910
2003 "5071"   "00296"  9313 20910
2004 "6829"   "00296"  9313 20910
2005 "6829"   "00296"  9313 20910
2006 "4213"   "00296"  9313 20910
2007 "10920"  "00296"  9313 20910
2008 "8717"   "00296"  9313 20910
2009 "10816"  "00296"  9313 20910
2010 "6104"   "00296"  9313 20910
2011 "3138"   "00296"  9313 20910
2002 "24931"  "00297"  1827 15655
2002 "24607"  "00325" 10409 16801
2003 "8463"   "00325" 10409 16801
2004 "4598"   "00325" 10409 16801
2005 "150481" "00325" 10409 16801
2002 "62865"  "00339" 12419 15721
2003 "7486"   "00339" 12419 15721
2002 "5071"   "00347" 14835 16427
2003 "5680"   "00347" 14835 16427
2004 "8215"   "00347" 14835 16427
2007 "5680"   "00393"  2677 16040
2008 "121553" "00393"  2677 16040
2009 "20338"  "00393"  2677 16040
2010 "139804" "00393"  2677 16040
2011 "3662"   "00393"  2677 16040
2010 "8358"   "00407" 18506 19738
2011 "2176"   "00407" 18506 19738
2002 "10920"  "00418"  -579 16299
2003 "28742"  "00418"  -579 16299
2004 "6649"   "00418"  -579 16299
2008 "3416"   "00419"  9466 23011
2009 "2251"   "00419"  9466 23011
2010 "5680"   "00419"  9466 23011
2002 "11259"  "00456" 11535 23011
2003 "5680"   "00456" 11535 23011
2004 "24283"  "00456" 11535 23011
2005 "5680"   "00456" 11535 23011
2006 "8463"   "00456" 11535 23011
2002 "10540"  "00458"  9587 14976
2003 "100609" "00458"  9587 14976
2004 "31673"  "00458"  9587 14976
2005 "27980"  "00458"  9587 14976
2004 "11259"  "00472" 16390 17501
2004 "6034"   "00472" 11323 16387
2005 "161075" "00472" 16390 17501
2006 "16603"  "00472" 16390 17501
2007 "112168" "00472" 16390 17501
2002 "9647"   "00476" 13865 17036
2003 "14477"  "00476" 13865 17036
2004 "6207"   "00476" 13865 17036
2005 "179437" "00476" 13865 17036
2006 "5680"   "00476" 13865 17036
2002 "25627"  "00498"  3104 14019
2003 "11649"  "00498"  3104 14019
2004 "10920"  "00510"  9862 23011
2002 "1397"   "00516"   731  7671
2002 "5691"   "00517"  2435 15614
2003 "5680"   "00517"  2435 15614
2002 "4598"   "00518"  3653 15461
2003 "4390"   "00518"  3653 15461
2004 "11649"  "00518"  3653 15461
2005 "6829"   "00518"  3653 15461
2006 "24610"  "00518"  3653 15461
2007 "63892"  "00518"  3653 15461
2008 "10540"  "00518"  3653 15461
2009 "10005"  "00518"  3653 15461
2010 "5680"   "00518"  3653 15461
2002 "22177"  "00532"  5479 15764
2002 "13992"  "00537"  7487 16223
2003 "29008"  "00537"  7487 16223
2004 "161075" "00537"  7487 16223
2002 "13634"  "00588" -1461 19083
2003 "2193"   "00588" -1461 19083
2004 "13992"  "00588" -1461 19083
2005 "5047"   "00588" -1461 19083
2006 "14477"  "00588" -1461 19083
2007 "6502"   "00588" -1461 19083
2008 "65613"  "00588" -1461 19083
2009 "5125"   "00588" -1461 19083
2010 "12053"  "00588" -1461 19083
2011 "14477"  "00588" -1461 19083
2002 "117961" "00593"  -730 17348
2003 "4598"   "00593"  -730 17348
2004 "1382"   "00593"  -730 17348
2005 "11636"  "00593"  -730 17348
2006 "6307"   "00593"  -730 17348
2007 "11259"  "00593"  -730 17348
2008 "29634"  "00593"  -730 17348
2009 "6104"   "00593"  -730 17348
2006 "64275"  "00643" 15492 21221
2007 "6829"   "00643" 15492 21221
2008 "8358"   "00643" 15492 21221
2009 "119417" "00643" 15492 21221
2010 "8479"   "00643" 15492 21221
2011 "5125"   "00643" 15492 21221
2002 "64839"  "00649"  1643 16437
2003 "2811"   "00649"  1643 16437
2004 "5680"   "00649"  1643 16437
2005 "8479"   "00649"  1643 16437
2006 "11649"  "00649"  1643 16437
2007 "110685" "00649"  1643 16437
2008 "5047"   "00649"  1643 16437
2009 "164652" "00649"  1643 16437
2010 "5680"   "00649"  1643 16437
2002 "10618"  "00685"  3288 16433
2003 "7199"   "00685"  3288 16433
2004 "22140"  "00685"  3288 16433
2002 "119417" "00695"  5479 15979
2003 "119417" "00695"  5479 15979
2008 "5680"   "00702"  8401 23011
2009 "3835"   "00702"  8401 23011
2010 "7291"   "00702"  8401 23011
2011 "10000"  "00702"  8401 23011
2002 "1045"   "00721" 11657 15431
2002 "11259"  "00722" 10958 16070
2003 "5680"   "00722" 10958 16070
2002 "3138"   "00745"  6879 15823
2003 "5680"   "00745"  6879 15823
2003 "10793"  "00788" 11323 12053
2004 "5047"   "00788" 11323 12053
2005 "3580"   "00788" 11323 12053
2006 "6774"   "00788" 11323 12053
2007 "7921"   "00791"  9313 16618
2008 "119417" "00791"  9313 16618
2008 "25389"  "00816" 11596 15186
2010 "1300"   "00816" 11596 15186
2011 "5680"   "00816" 11596 15186
2002 "24607"  "00826" 11962 19492
2003 "5680"   "00826" 11962 19492
2004 "10540"  "00826" 11962 19492
2005 "138466" "00826" 11962 19492
2006 "65613"  "00826" 11962 19492
2007 "1618"   "00826" 11962 19492
2008 "6829"   "00826" 11962 19492
2009 "4036"   "00826" 11962 19492
2010 "112168" "00826" 11962 19492
2011 "20029"  "00826" 11962 19492
2002 "3144"   "00836"     0 15737
2003 "2193"   "00836"     0 15737
2002 "5047"   "00837"   731 22436
2004 "2193"   "00837"   731 22436
2005 "6774"   "00837"   731 22436
2006 "5680"   "00837"   731 22436
2007 "2606"   "00837"   731 22436
2002 "15289"  "00849"   731 16468
2003 "5680"   "00849"   731 16468
2004 "1382"   "00849"   731 16468
2005 "11304"  "00849"   731 16468
2006 "11124"  "00849"   731 16468
2007 "7085"   "00849"   731 16468
2008 "8479"   "00849"   731 16468
2009 "10581"  "00849"   731 16468
2010 "14027"  "00849"   731 16468
2011 "1356"   "00849"   731 16468
2002 "4598"   "00864" 11323 23011
2002 "1743"   "00866"  -730 13984
2010 "177697" "00874" 18232 18506
2002 "62967"  "00885"  9497 15372
2003 "22140"  "00885"  9497 15372
2002 "4611"   "00896"  6241 16437
2003 "11259"  "00896"  6241 16437
2004 "11259"  "00896"  6241 16437
2002 "2193"   "00899"  8067 16892
2003 "23252"  "00899"  8067 16892
2004 "11443"  "00899"  8067 16892
2005 "6829"   "00899"  8067 16892
2006 "17239"  "00899"  8067 16892
2007 "16477"  "00899"  8067 16892
2008 "113490" "00899"  8067 16892
2009 "6242"   "00899"  8067 16892
2002 "6649"   "00902" 13727 15492
2003 "14477"  "00902" 13727 15492
2004 "11259"  "00902" 13727 15492
2005 "3138"   "00902" 13727 15492
2002 "4598"   "00919"  9609 17185
2003 "157355" "00919"  9609 17185
2004 "11649"  "00919"  9609 17185
2005 "3158"   "00919"  9609 17185
2006 "3851"   "00919"  9609 17185
2007 "7085"   "00919"  9609 17185
2007 "10405"  "00957"  -306 14153
2008 "119417" "00957"  -306 14153
2009 "148257" "00957"  -306 14153
2010 "6829"   "00957"  -306 14153
2002 "11259"  "00981"  2404 15705
2003 "6774"   "00981"  2404 15705
2002 "12756"  "00996" 13515 16800
2003 "157355" "00996" 13515 16800
2004 "10405"  "00996" 13515 16800
2005 "11259"  "00996" 13515 16800
2002 "3813"   "01007"   731 16437
2003 "119417" "01007"   731 16437
2004 "14049"  "01007"   731 16437
2005 "13712"  "01007"   731 16437
2006 "1045"   "01007"   731 16437
2007 "7921"   "01007"   731 16437
2008 "177827" "01007"   731 16437
2009 "148394" "01007"   731 16437
2010 "3138"   "01007"   731 16437
2011 "1382"   "01007"   731 16437
2002 "10920"  "01028" 14976 15918
2003 "24931"  "01028" 14976 15918
2002 "6829"   "01039"  3104 16252
2003 "30671"  "01039"  3104 16252
2004 "119316" "01039"  3104 16252
2004 "23577"  "01078" 14426 23011
2005 "17436"  "01078" 14426 23011
2006 "4213"   "01078" 14426 23011
2007 "12379"  "01078" 14426 23011
2008 "29830"  "01078" 14426 23011
2009 "105089" "01078" 14426 23011
2010 "14477"  "01078" 14426 23011
2002 "3144"   "01081" 10652 23011
2003 "2193"   "01081" 10652 23011
2004 "22140"  "01081" 10652 23011
2002 "6552"   "01101"  1096 16815
2003 "11649"  "01101"  1096 16815
2004 "5878"   "01101"  1096 16815
2005 "4598"   "01101"  1096 16815
2002 "10793"  "01108" 12965 14245
end
format %td co_start_date
format %td co_end_date

------------------ copy up to and including the previous line ------------------

Listed 300 out of 44487 observations

Last edited by BB Kim; 04 Mar 2023, 15:35.

Comment

Clyde Schechter

Join Date: Apr 2014
Posts: 30357

#17

04 Mar 2023, 16:53

After spending over an hour on this, I'm just going to show you what I have so far, and leave it to you to figure out how to fix it. The code below is, I believe, a correct implementation of the words you use to describe what you want. But the results it produces are not even remotely close to the results you say you want. Moreover, try though I may, I can't figure out what it is you actually are doing to arrive at those results. They just seem to me to be completely bizarre and I cannot make any sense of them.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte recession_num float(recession_start recession_end)
 1 -11110 -9802
 2  -8280 -7884
 3  -5447 -5205
 4  -4078 -3744
 5  -2375 -2071
 6   -883  -640
 7     91   397
 8   3622  3957
 9   5053  5538
10   7305  7487
11   7852  8340
12  11139 11382
13  15035 15280
14  17501 18049
15  21946 22006
end
format %td recession_start
format %td recession_end

tempfile recessions
save `recessions'

* Example generated by -dataex-. For more info, type help dataex
clear
input int YEAR long GVKEY str5 EXECID float(co_start_date co_end_date)
2000   5680 "04970" 13515 14610
2001   5681 "04970" 13515 14976
2002   5683 "04970" 13515 15341
2003  24668 "04970" 13515 15706
2004  10920 "04970" 13515 16071
2005 184748 "04970" 13515 16437
2006   8479 "04970" 13515 16931
2002   4598 "00016" -1767 12692
2003 157415 "00016" -1767 12692
2004  10920 "00016" -1767 12692
end
format %td co_start_date
format %td co_end_date


isid GVKEY EXECID YEAR
//  REPLACE THE CO_END_DATE BY START OF CURRENT YEAR
//  UNLESS CO_END_DATE IS EARLIER, OR IS IN THE CURRENT YEAR
clonevar new_co_end_date = co_end_date
replace new_co_end_date = mdy(1, 1, YEAR)  if YEAR < year(new_co_end_date)

//  MATCH UP THESE CAREER RANGES WITH RECESSIONS
cross using `recessions'
isid EXECID GVKEY YEAR recession_num, sort
//  IDENTIFY ALL RECESSIONS THAT OVERLAP THE CAREER TO DATE
by EXECID GVKEY YEAR: gen byte overlap = min(new_co_end_date, recession_end) ///
    >= max(co_start_date, recession_start)
//  IN EACH YEAR, COUNT THOSE OVERLAPS THAT STARTED BEFORE THE CURRENT YEAR
by EXECID GVKEY YEAR: egen n_prior_recessions ///
    = total(overlap & year(recession_start) < YEAR)  
by EXECID GVKEY YEAR: keep if _n == 1
drop recession_num recession_start recession_end

which produces the following results:

Code:

    YEAR    GVKEY   EXECID   co_start_date   co_end_date   new_co_end_date   overlap   n_prior_recess~s  
    2002     4598    00016       01mar1955     01oct1994         01oct1994         0                  7  
    2004    10920    00016       01mar1955     01oct1994         01oct1994         0                  7  
    2003   157415    00016       01mar1955     01oct1994         01oct1994         0                  7  
    2000     5680    04970       01jan1997     01jan2000         01jan2000         0                  0  
    2001     5681    04970       01jan1997     01jan2001         01jan2001         0                  0  
    2002     5683    04970       01jan1997     01jan2002         01jan2002         0                  1  
    2006     8479    04970       01jan1997     10may2006         10may2006         0                  1  
    2004    10920    04970       01jan1997     01jan2004         01jan2004         0                  1  
    2003    24668    04970       01jan1997     01jan2003         01jan2003         0                  1  
    2005   184748    04970       01jan1997     01jan2005         01jan2005         0                  1

Announcement

Comment

Comment