st: Re: Generating a new date variable from another date variable

["Joseph Coveney" <stajc2@gmail.com>]

Pinaki Mitra wrote:

I have a data variable where each observation is different dates of
2006 to 2012. I need to summarize this data by weekend (each Saturday
of each week). I attempted to generate a new date variable (weekend)
and collapse the data by weekend. But I am having difficulty in
generating the weekend variable from the actual date variable.
For example,
DailyDate(original variable) WeekendDate (Want to generate)
01/01/2006 01/07/2006
01/03/2006 01/07/2006
01/04/2006 01/07/2006
01/05/2006 01/07/2006
01/06/2006 01/07/2006
I tried:
[snip]
It does not work. Would anyone please help? Thank you.

--------------------------------------------------------------------------------

I would recommend taking Nick's suggestion of -dow()- and running with it.
Something like that below should work.  The first part is just recreating your
dataset excerpt.  Look at the section the begins with "Begin here".  I don't
know whether your target dataset has all the days of the year or not, and so I
recommend creating a separate calendar dataset containing the Saturday dates
that you need, and then merging your target dataset with the separate calendar
dataset.

Joseph Coveney

. clear *

. set more off

. 
. input str20 DailyDate

                DailyDate
  1. "01/01/2006"
  2. "01/03/2006"
  3. "01/04/2006"
  4. "01/05/2006"
  5. "01/06/2006"
  6. end

. generate int holder = date(DailyDate, "MDY")

. drop DailyDate

. rename holder DailyDate

. tempfile Pinaki_Mitras_dataset

. quietly save `Pinaki_Mitras_dataset'

. drop _all

. 
. *
. * Begin here
. *
. quietly set obs `=date("2012-12-31", "YMD") - ///
>     date("2006-01-01", "YMD") + 10'

. generate int DailyDate = date("2005-12-31", "YMD") + _n

. generate int week = sum(dow(DailyDate) == 0)

. bysort week (DailyDate): generate int WeekendDate = DailyDate[_N]

. quietly drop if DailyDate > date("2012-12-31", "YMD")

. 
. format *Date %tdCCYY-NN-DD

. list in 1/10, noobs abbreviate(20) sepby(week)

  +---------------------------------+
  |  DailyDate   week   WeekendDate |
  |---------------------------------|
  | 2006-01-01      1    2006-01-07 |
  | 2006-01-02      1    2006-01-07 |
  | 2006-01-03      1    2006-01-07 |
  | 2006-01-04      1    2006-01-07 |
  | 2006-01-05      1    2006-01-07 |
  | 2006-01-06      1    2006-01-07 |
  | 2006-01-07      1    2006-01-07 |
  |---------------------------------|
  | 2006-01-08      2    2006-01-14 |
  | 2006-01-09      2    2006-01-14 |
  | 2006-01-10      2    2006-01-14 |
  +---------------------------------+

. list in -10/l, noobs abbreviate(20) sepby(week)

  +---------------------------------+
  |  DailyDate   week   WeekendDate |
  |---------------------------------|
  | 2012-12-22    364    2012-12-22 |
  |---------------------------------|
  | 2012-12-23    365    2012-12-29 |
  | 2012-12-24    365    2012-12-29 |
  | 2012-12-25    365    2012-12-29 |
  | 2012-12-26    365    2012-12-29 |
  | 2012-12-27    365    2012-12-29 |
  | 2012-12-28    365    2012-12-29 |
  | 2012-12-29    365    2012-12-29 |
  |---------------------------------|
  | 2012-12-30    366    2013-01-05 |
  | 2012-12-31    366    2013-01-05 |
  +---------------------------------+

. drop week

. 
. merge 1:m DailyDate using `Pinaki_Mitras_dataset', ///
>    assert(match master) keep(match) nogenerate noreport

. 
. list, noobs abbreviate(20)

  +--------------------------+
  |  DailyDate   WeekendDate |
  |--------------------------|
  | 2006-01-01    2006-01-07 |
  | 2006-01-03    2006-01-07 |
  | 2006-01-04    2006-01-07 |
  | 2006-01-05    2006-01-07 |
  | 2006-01-06    2006-01-07 |
  +--------------------------+

. 
. exit

end of do-file


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/