Generate Cumulative time series dummy variables

[Jamie Wolstenholme]

Hi there,

I have a list of observations in a .csv including date fields for each observation under the headings "observation id", "date" (dd/mm/yy)

The dates range from 01/09/11 to 01/09/16 but I only want to use the mm/yy portion

Assuming that once the observation happens it is added to a fixed stock, I'm trying to generate dummy variables from these dates to provide a sort of cumulative measure.

As such, If an observation was recorded in 01/14 I would want to have dummy variables that displayed "0" for each month from the beginning of the period up to December 2013 and then "1" for each month from January 2014 to the end of the period (09/16)

Is there any way that this can be achieved in Stata?

In theory, I guess this is what it would need to end up looking like:

id	date	09/09	…	09/10	10/12	11/12	…	09/16
717308	20/10/2012	0	0	0	1	1	1	1
705526	20/10/2012	0	0	0	1	1	1	1
705210	20/10/2012	0	0	0	1	1	1	1
680739	20/10/2012	0	0	0	1	1	1	1
672080	20/10/2012	0	0	0	1	1	1	1
521029	21/10/2012	0	0	0	1	1	1	1
613404	21/10/2012	0	0	0	1	1	1	1
402700	21/10/2012	0	0	0	1	1	1	1
740867	22/10/2012	0	0	0	1	1	1	1
727655	22/10/2012	0	0	0	1	1	1	1
450370	22/11/2012	0	0	0	0	1	1	1
632672	22/11/2012	0	0	0	0	1	1	1
731022	22/11/2012	0	0	0	0	1	1	1
539042	22/11/2012	0	0	0	0	1	1	1

Thanks in advance

[Robert Picard]

Once the data is imported into Stata, this boils down to converting the date to a Stata monthly date (see help datetime) and then looping over each month in the data to create the indicators. Something like:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long id str10 date
717308 "20/10/2012"
705526 "20/10/2012"
705210 "20/10/2012"
680739 "20/10/2012"
672080 "20/10/2012"
521029 "21/10/2012"
613404 "21/10/2012"
402700 "21/10/2012"
740867 "22/10/2012"
727655 "22/10/2012"
450370 "22/11/2012"
632672 "22/11/2012"
731022 "22/11/2012"
539042 "22/11/2012"
end

* convert to numeric monthly date; see help datetime
gen mdate = mofd(daily(date,"DMY"))
format %tm mdate

* find the range of the monthly date
sum mdate

* loop over each month and create indicator variables
forvalues i=`r(min)'/`r(max)' {
    local vname = "m_" + string(`i', "%tm")
    gen byte `vname' = `i' >= mdate
}

[Jamie Wolstenholme]

Thank you so much, this is perfect