Last dates by registry

[Luciana Pando]

Hello everyone:

How I can recognize in Stata the last date of a register. I mean, I have something like this by ID and I want to keep only the registers from the last date (that is why i have created the variable Date_1). How I can keep them by ID?

ID Date Date_1
33589911 2019-08-13 0813
33589911 2019-09-04 0904
40776365 2019-08-26 0826
40776365 2019-09-09 0909
.
.
.
.
.


Thank you,

Luciana

[Nick Cox]

Date looks like a daily date and Date_1 a time of day. If so, then

bysort ID (Date Date_1) : keep if _n == _N 

keeps the last. But, but, but: you didn't use dataex here (FAQ Advice #12), so this is a guess, not a certainty.

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input long id str10 date
33589911 "2019-08-13"
33589911 "2019-09-04"
40776365 "2019-08-26"
40776365 "2019-09-09"
end

gen sif_date = daily(date, "YMD")
assert !missing(sif_date)
format sif_date %td
drop date

by id (sif_date), sort: keep if _n == _N

Neither you date variable, a string, nor your derived variable Date_1,a numeric variable but lacking year information so it will fail when year boundaries are crossed by a single id, will get you where you need to go. In Stata, almost anything you might want to do with dates requires a Stata internal format date variable. So we start by creating one from your string variable. Then it's just a sort and a keep.

Stata internal format date variables are complicated, but if you are going to do this kind of work more than occasionally, you need to learn how touse them. Read -help datetime- and the corresponding sections of the PDF manuals (click on the blue links in the help file) that come installed with your Stata. It's a lot of material, more than you can hope to remember in its entirety, but if you absorb the concepts, in the future you will be able to solve problems like this one with simple recourse to the help files to remind you of the details.

Note: The above code will fail if there are missing value of Date or invalid values of Date. If you find invalid Dates, of course, fix them and then try again. If there are missing values of Date that cannot be filled in with correct dates, then post back and I can show you code that handles them properly.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.