Getting end of month date

[Farid Matuk]

In our office we have a large (over 5 trillion rows) monthly data set stored in Oracle. IT guys decided to store date each month including the last day of the month in a format YYYYMMDD in a variable called: PER_NCOD which is indexed.

We used ODBC feature in Stata for data extraction but looping over dates was a problem, because last day of month follows exact rules for every month but variable for February. The most compact solution found is copied below, but better alternatives are welcome.

forvalues y = 2010/2015 {
forvalues m = 1/12 {
drop _all
local last_day = year(mdy(`m',1,`y')-1)*10000+month(mdy(`m',1,`y')-1)*100+day(mdy(`m',1,`y')-1)
odbc load, exec("SELECT * FROM OUR_DATABASE WHERE PER_NCOD=`last_day'") dsn("ABC") user(XYZ) password(123)
save OUR_`last_day'
}
}

[Nick Cox]

The last day of the month is the day before the first day of the next month. I don't know anything about odbc but here are your days:

 
forval m = 1/72 { 
    local mdate = ym(2009, 12) + `m' 
    di %td dofm(`mdate' + 1) - 1 
}

31jan2010
28feb2010
31mar2010
30apr2010
31may2010
30jun2010
31jul2010
31aug2010
30sep2010
31oct2010
30nov2010
31dec2010
31jan2011
28feb2011
31mar2011
30apr2011
31may2011
30jun2011
31jul2011
31aug2011
30sep2011
31oct2011
30nov2011
31dec2011
31jan2012
29feb2012
31mar2012
30apr2012
31may2012
30jun2012
31jul2012
31aug2012
30sep2012
31oct2012
30nov2012
31dec2012
31jan2013
28feb2013
31mar2013
30apr2013
31may2013
30jun2013
31jul2013
31aug2013
30sep2013
31oct2013
30nov2013
31dec2013
31jan2014
28feb2014
31mar2014
30apr2014
31may2014
30jun2014
31jul2014
31aug2014
30sep2014
31oct2014
30nov2014
31dec2014
31jan2015
28feb2015
31mar2015
30apr2015
31may2015
30jun2015
31jul2015
31aug2015
30sep2015
31oct2015
30nov2015
31dec2015

[Farid Matuk]

Hi Nick, in order to understand better I have rewrote your code like this:

forval m = 1/72 {
local fecha = dofm(ym(2009, 12) + `m' + 1) - 1
display %td `fecha'
}

And my previous code like this:

forvalues y = 2010/2015 {
forvalues m = 1/12 {
local last_day = mdy(`m',1,`y')-1
display %td `last_day'
}
}

The differences are 1 loop plus 2 string instructions versus 2 loops and 1 string instruction. Do you know how to measure efficiency coding in Stata? Thanks

[Nick Cox]

help timer

[Farid Matuk]

It looks 2 loops over integers plus 1 string operation is faster than 1 loop over integers plus 2 string operations