Creating fictional end-of-month dates if only year or month&year is available (irregular dates)

[Victoria Gierok]

Dear Statalist,

I am using Stata Version 14.
I have a dataset of Royal Navy captains which includes start and end dates of their service. In some cases I have the precise dates (day, month year). However, in other cases I only have the year (or month and year) in which they started and left the service.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 Ship byte Rate str10(Date_start Date_end) str18 Name str23 Rank str20 Role float(datestart dateend)
"London"       1 "1670"       "1670"       "John Tinker"        "Captain"                 "Commanding Officer"   -105919 -105919
"London"       1 "1673"       "1673"       "William Holden"     "Captain"                 "Commanding Officer"   -104823 -104823
"London"       1 "07/1673"    "1673"       "Thomas Elliott"     "Captain"                 "Commanding Officer"   -104642 -104823
"London"       1 "1692"       "1692"       "Matthew Aylmer"     "Captain"                 "Commanding Officer"    -97884  -97884
"London"       1 "06/03/1694" "22/12/1695" "Stafford Fairborne" "Captain"                 "Commanding Officer"    -97089  -96433
"London"       1 "24/12/1695" "11/06/1696" "Thomas Jennings"    "Captain"                 "Commanding Officer"    -96431  -96261
"London"       1 "12/06/1696" "01/05/1699" "John Munden"        "Captain"                 "Commanding Officer"    -96260  -95207
"London"       1 "1693"       "12/07/1693" "John Ashby"         "Admiral of the Blue"     "Flag Officer"          -97518  -97326
"London"       1 "1670"       "1670"       "John Flawes"        "First Lieutenant"        "Commissioned Officer" -105919 -105919
"London"       1 "1672"       "1672"       "John Robinson"      "First Lieutenant"        "Commissioned Officer" -105189 -105189
"London"       1 "1672"       "1673"       "George Rooke"       "First Lieutenant"        "Commissioned Officer" -105189 -104823
"London"       1 "07/05/1695" "03/03/1695" "Nathaniel Symonds"  "First Lieutenant"        "Commissioned Officer"  -96662  -96727
"London"       1 "26/12/1695" "24/01/1696" "Hercules Mitchell"  "First Lieutenant"        "Commissioned Officer"  -96429  -96400
"London"       1 "09/08/1696" "10/05/1699" "Richard Canning"    "First Lieutenant"        "Commissioned Officer"  -96202  -95198
"Saint Andrew" 1 "18/10/1670" "06/12/1670" "John Tinker"        "Captain"                 "Commanding Officer"   -105629 -105580
"Saint Andrew" 1 "16/01/1671" "08/04/1672" "George Ayscue"      "Admiral"                 "Commanding Officer"   -105539 -105091
"Saint Andrew" 1 "08/04/1672" "26/03/1673" "John Kempthorne"    "Rear-Admiral of the Red" "Commanding Officer"   -105091 -104739
"Saint Andrew" 1 "27/03/1673" "18/10/1673" "John Archer"        "Captain"                 "Commanding Officer"   -104738 -104533
"Saint Andrew" 1 "1690"       "1691"       "Robert Dorrell"     "Captain"                 "Commanding Officer"    -98614  -98249
"Saint Andrew" 1 "1691"       "1691"       "John Berkeley"      "Rear-Admiral"            "Commanding Officer"    -98249  -98249
"Saint Andrew" 1 "1692"       "1692"       "George Churchill"   "Captain"                 "Commanding Officer"    -97884  -97884
"Saint Andrew" 1 "1693"       "03/12/1693" "John Clements"      "Captain"                 "Commanding Officer"    -97518  -97182
"Saint Andrew" 1 "24/12/1695" "05/01/1695" "Edward Whitaker"    "Captain"                 "Commanding Officer"    -96431  -96784
"Saint Andrew" 1 "06/01/1695" "1697"       "Wolfran Cornewall"  "Captain"                 "Commanding Officer"    -96783  -96057
"Saint Andrew" 1 "27/03/1673" "18/10/1673" "John Kempthorne"    "Rear-Admiral of the Red" "Flag Officer"         -104738 -104533
"Saint Andrew" 1 "30/12/1695" "1696"       "Matthew Aylmer"     "Rear-Admiral of the Red" "Flag Officer"          -96425  -96423
"Saint Andrew" 1 "1670"       "1672"       "Peter Edwards"      "First Lieutenant"        "Commissioned Officer" -105919 -105189
"Saint Andrew" 1 "1671"       "1671"       "John Wood"          "First Lieutenant"        "Commissioned Officer" -105554 -105554
"Saint Andrew" 1 "1672"       "1672"       "Thomas Harwood"     "First Lieutenant"        "Commissioned Officer" -105189 -105189
"Saint Andrew" 1 "1692"       "1692"       "Thomas Hardy"       "First Lieutenant"        "Commissioned Officer"  -97884  -97884
"Saint Andrew" 1 "26/12/1695" "05/01/1695" "Robert Masterton"   "First Lieutenant"        "Commissioned Officer"  -96429  -96784
"Saint Andrew" 1 "06/01/1695" "01/05/1696" "John Cowe"          "First Lieutenant"        "Commissioned Officer"  -96783  -96302
end
format %tdDD.NN.CCYY datestart
format %tdDD.NN.CCYY dateend

I have already transformed the original date variables (Date_start and Date_end) from strings into a stata date format (new variables are called datestart and dateend, final columns in the shown dataset). I did this by using the following code:

gen datestart1 = date(Date_start, "DMY")
format datestart1 %tdDD.NN.CCYY
gen datestart2 = date(Date_start, "Y") if datestart==.
format datestart2 %tdDD.NN.CCYY
gen datestart3 = date(Date_start, "MY") if datestart2==.
format datestart3 %tdDD.NN.CCYY

gen datestart = datestart1
replace datestart = datestart2 if datestart==.
replace datestart = datestart3 if datestart==.
format datestart %tdDD.NN.CCYY

drop datestart1 datestart2 datestart3

For the start dates, stata simply assumes the day to be January 1 if only the year is available or the first day of the given month if only month and year are available, which is perfectly fine.
However, for the end dates it does the same, which is not what I want. For the end dates I would like stata to take the last day of that year (i.e. December 31), so that I get the fictional presumption of a full year of service if both start and end date are the same year.

I checked different statalist threads on date variables, but I couldn’t find anything that would answer this particular problem. If there is, please let me know. Otherwise, I’d be very grateful for any advice.

Disclaimer: This is my first post, I tried to follow all the advice on “Advice on posting to Statalist”, I’m thankful for any comments on how to improve my posting style. I used dataex to show my dataset, however, it seems to display the date formats in the "original" stata way (just numbers), whereas in my browse window they are shown as 01.01.1670 - which is nicer to read and understand.

Best,

VG

[Nick Cox]

You did a great job on your first post.

31 December on or after any daily date in the same year is just obtained by feeding 31 as day and 12 as month and the year in question to mdy().

. di %td mdy(5, 29, 2018)
29may2018

. di %td  mdy(12, 31, year(mdy(5, 29, 2018)))
31dec2018

so you want

gen wanted = mdy(12, 31, year(dateend))
format wanted %td

[Victoria Gierok]

Thank you very much for this very quick answer. It worked nicely.