Creating fictional end-of-month dates if only year is available

[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 from strings into a stata date format 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.