Lagging daily data by exactly one month

[Gene Park]

Hello. I am working with daily time series data, and I want to create a lag that is exactly 1 month earlier. I did figure out a way to do so, but it's pretty inelegant - see below. Is there a better way to do this? I'm trying to this over a decent number of variables so I'm looking for a more efficient method.

The code below, which I found on the internet, basically creates a new lagged date that is exactly one month earlier. It then replaces the old date with the new lagged data. One disadvantage is that I have to do this for each lag, save into a new stata database file, and then merge with the original file.

Any help would be most appreciated!

Best,
Gene Park

clear
use xrate_daily.dta
gen monthlyDate=mofd(day) // day = is the specific day of a month in a specific year, e.g. Jan 1, 2017
gen oneMonthEarlierTemp=dofm(mofd(day)-1)
format oneMonthEarlierTemp %td
gen oneMonthEarlier=mdy(month(oneMonthEarlierTemp), day(day), year(oneMonthEarlierTemp))
format oneMonthEarlier %td
egen numInvalid=total(oneMonthEarlier==.)
local i 1 // number of days to subtract from invalid dates
while (numInvalid>0) {
replace oneMonthEarlier=mdy(month(oneMonthEarlierTemp),day (day)-`i',year(oneMonthEarlierTemp)) if oneMonthEarlier==.
local i=`i'+1
drop numInvalid
egen numInvalid = total(oneMonthEarlier==.)
}
drop oneMonthEarlierTemp numInvalid
format oneMonthEarlier %td

gen xrate_d_1m = xrate_d // xrate_d = daily exchange rate
keep oneMonthEarlier xrate_d_1m
rename oneMonthEarlier day
save xrate_daily.dta, replace // 1 month lag

[Phil Bromiley]

You didn't get a quick answer. You'll increase your chance of a useful response by following the FAQ on asking questions - provide Stata code in code delimiters, Stata output, and sample data using dataex. Also, try to simplify what you post to the minimum necessary to generate the issue.

I don't see an elegant solution but others on the list are likely to one if you pose the question correctly. I wonder if you can't do something by sorting a couple of different ways.

[Gene Park]

Thank you, Phil.

[William Lisowski]

Here is some code that may do what is required, without loops. Tested on exactly three carefully-chosen dates in the absence of sample data.

clear
set obs 3
generate today = .
replace today = daily("29mar2017","DMY") in 1
replace today = daily("29mar2016","DMY") in 2
replace today = daily("15jan2017","DMY") in 3
format today %td
generate prev_mon = dofm(mofd(today)-1)
format prev_mon %td
generate prev_end = mdy(month(today),1,year(today))-1
format prev_end %td
generate prev_day = min(day(today),day(prev_end))
generate monthago = mdy(month(prev_mon),prev_day,year(prev_mon))
format monthago %td
list

. list

     +----------------------------------------------------------+
     |     today    prev_mon    prev_end   prev_day    monthago |
     |----------------------------------------------------------|
  1. | 29mar2017   01feb2017   28feb2017         28   28feb2017 |
  2. | 29mar2016   01feb2016   29feb2016         29   29feb2016 |
  3. | 15jan2017   01dec2016   31dec2016         15   15dec2016 |
     +----------------------------------------------------------+

.

And for future reference, do let me echo Phil's recommendation that you review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. The more you help others understand your problem, the more likely others are to be able to help you solve your problem. With sample data, presented using the dataex command as discussed in the FAQ, I'm sure this would have elicited an answer shortly after you posted it.

[Robert Picard]

Here's a slightly different take on the date arithmetics to get there. To avoid having to do merge gymnastics to align the rates one month ago, you can use rangestat (from SSC) to get the rate. I used (min) but (max) or (mean) would also work as there is only one rate per day.

clear
set seed 23123
set obs 100
gen day = mdy(1,1,2000) + _n
format %td day
gen xrate_d = runiform()

* the date on the first of the month - 1 is the last day of the previous month
gen oneMago = mdy(month(day), 1, year(day)) - 1

* adjust if the day is greater than the last day of the previous month
replace oneMago = cond(day(day) > day(oneMago), ///
    mdy(month(oneMago), day(oneMago), year(oneMago)), ///
    mdy(month(oneMago), day(day), year(oneMago)))
format %td oneMago

* rangestat is from SSC, to install, type: ssc install rangestat
rangestat (min) xrate1ma=xrate_d, interval(day oneMago oneMago)