Count the number using date ranges

[BB Kim]

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


LISTS of RECESSIONS:
local recession1 August 1929 March 1933
local recession2 May 1937 June 1938
local recession3 February 1945 October 1945
local recession4 November 1948 October 1949
local recession5 July 1953 May 1954
local recession6 August 1957 April 1958
local recession7 April 1960 February 1961
local recession8 December 1969 November 1970
local recession9 November 1973 March 1975
local recession10 January 1980 July 1980
local recession11 July 1981 November 1982
local recession12 July 1990 March 1991
local recession13 March 2001 November 2001
local recession14 December 2007 June 2009
local recession15 February 2020 April 2020

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

[Clyde Schechter]

Duplicate post. Asked, and partially answered, at https://www.statalist.org/forums/for...of-dates/page2.

[BB Kim]

If I use the code here (My code below), I could change the co_end_date to the first date of the calendar year satisfying the condition I want to apply, which is new variable c_end_date
However, when I run foreach loop at the below that you suggested, it shows like this

. foreach v of varlist c_start_date c_end_date {
2. gen _`v' = daily(`v', "DMY"), after(`v')
3. assert missing(`v') == missing(_`v')
4. drop `v'
5. rename _`v' `v'
6. format `v' %td
7. }
(40 missing values generated)
40 contradictions in 24,525 observations
assertion is false
r(9);




What can I do if I can run the code that you posted last time (shown below)?

Due to this error, forvalues loop shows error message of "if not found"

How can I modify your code to solve the error??




************** My code to change co_end_date **********
gen year_co_end_date = substr(co_end_date, 6, 9) /*last 4 digit year of co_end_date*/
destring year_co_end_date, replace


replace year_co_end_date =YEAR if YEAR < year_co_end_date

tostring(year_co_end_date), gen (finyear)


destring finyear, replace

gen _co_end_date = daily(co_end_date, "DMY"), after(co_end_date)
assert missing(_co_end_date) == missing(co_end_date)
format _co_end_date %td
drop co_end_date
rename _co_end_date co_end_date

replace co_end_date = mdy(month(co_end_date), day(co_end_date), finyear)
tostring(co_end_date), gen (c_end_date)
rename co_start_date c_start_date

*********************************

********** CODE that you posted last time *******************************************
foreach v of varlist c_start_date c_end_date {
gen _`v' = daily(`v', "DMY"), after(`v')
assert missing(`v') == missing(_`v')
drop `v'
rename _`v' `v'
format `v' %td
}

local recession1 August 1929 March 1933
local recession2 May 1937 June 1938
local recession3 February 1945 October 1945
local recession4 November 1948 October 1949
local recession5 July 1953 May 1954
local recession6 August 1957 April 1958
local recession7 April 1960 February 1961
local recession8 December 1969 November 1970
local recession9 November 1973 March 1975
local recession10 January 1980 July 1980
local recession11 July 1981 November 1982
local recession12 July 1990 March 1991
local recession13 March 2001 November 2001
local recession14 December 2007 June 2009
local recession15 February 2020 April 2020

// CLEAN UP THE RECESSION LOCALS AND MAKE THEM NUMERIC
forvalues i = 1/15 {
local m1: word 1 of `recession`i''
local y1: word 2 of `recession`i''
local m2: word 3 of `recession`i''
local y2: word 4 of `recession`i''
local recession_start`i' = dofm(monthly("`m1' `y1'", "MY"))
local recession_end`i' = dofm(monthly("`m2' `y2'", "MY"))
assert !missing(`recession_start`i'', `recession_end`i'')
}




// IDENTIFY FIRST RECESSION WORKED ****************
gen first_co_r_worked_start = .
gen first_co_r_worked_end = .
format first_co_r_worked* %td
gen n_co_recessions_worked = 0

forvalues i = 1/15 {
replace first_co_r_worked_start = `recession_start`i'' if min(co_end_date, `recession_end`i'') >= max(co_start_date, `recession_start`i'') & missing(first_co_r_worked_start)
replace first_co_r_worked_end = `recession_end`i'' if min(co_end_date, `recession_end`i'') >= max(co_start_date, `recession_start`i'') & missing(first_co_r_worked_end)
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'')
}

[Clyde Schechter]

The problem is not with the code, it is with your data. There are 40 values of one of the variables c_start_date or c_end_date which are not actually possible dates, or are dates but are not written in day-month-year format. You need to find and fix those data problems. To find them:

foreach v of varlist c_start_date c_end_date {
    list `v' if missing(daily(`v', "DMY")) & !missing(`v')
}

Then correct those dates and re-run the code from the top.

By the way, when you run code and Stata halts with an error message, as it did here, you should not go on and try to run the rest of the code. The error message is telling you that something is wrong. It may be a problem with the code or with the data in general, but either way, there is a good chance that the rest of the code will not run. And, worse, if the rest of the code does run, you will get incorrect results. Once you get an error message you need to fix the problem that caused it before attempting to continue.