Keep the last observation by group

[Rochelle Zhang]

Dear all,
I am posting a few observation of my data below.
Ticker: firm unique id
Analys: financial analyst id
Value: earnings forecast issued by analyst
Anndats: the date forecast is issued
Fyear: forecast year

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 cusip long analys double value long anndats int fyear
"87482X10" 50789 1.14 20141107 2014 
"87482X10" 71182 .86 20140317 2014 
"87482X10" 71182 1.02 20140507 2014 
"87482X10" 71182 1.24 20140807 2014 
"87482X10" 71182 1.21 20141105 2014 
"87482X10" 72481 1.2 20141203 2014 
"87482X10" 79092 .8 20140310 2014 
"87482X10" 79092 .51 20140407 2014 
"87482X10" 79092 .58 20140508 2014 
"87482X10" 79092 .57 20140709 2014 
"87482X10" 79092 1.23 20140808 2014 
"87482X10" 79092 .65 20140815 2014 
"87482X10" 79092 1.2 20141106 2014 
"87482X10" 80474 .83 20140310 2014 
"87482X10" 80474 1.05 20140317 2014 
"87482X10" 80474 .56 20140407 2014 
"87482X10" 80474 .58 20140507 2014 
"87482X10" 80474 .59 20140603 2014 
"87482X10" 80474 1.09 20140807 2014 
"87482X10" 80474 1.22 20141105 2014 
"87482X10" 119962 .73 20140309 2014 
"87482X10" 119962 .5 20140409 2014 
"87482X10" 119962 .58 20140507 2014 
"87482X10" 119962 1.17 20140807 2014 
"87482X10" 119962 1.19 20141105 2014 
end

You shall see that some analysts issue multiple forecasts for a year, e.g. analys 79092 updates his/her forecasts 7 times in year 2014.

I want to generate a count of how many distinct analysts issuing forecast each firm per year, in above example, analys 79092 should be counted once, not 7 times, that is , the variable I want to generate, call it count, count = 6 for the firm in the posted data.

Hence, I try to use the following code to keep the last forecast per analyst firm year, but it does not delete 6 out of the 7 observations for analys 79092

sort ticker fyear analys anndats
by ticker fyear analys anndats, sort: keep if _n == _N
//(31,606 observations deleted)

* count # of analysts 
by ticker fyear , sort: gen analyno=_N
 // I got analyno=28  which counts same analyst multiple times.

Could you point out my error ?

thank you ,

Rochelle

[Clyde Schechter]

There is a little inconsistency between your data and your explanation: you refer to ticker in your explanation and code, but the data has cusip instead. Anyway, as far as I can see, you don't actually need to identify specifically the last forecast made by each analyst, since you just need to count each analyst exactly once. So any one observation for the analyst will do. So my approach would be:

by cusip fyear analys, sort:  gen byte counter = (_n == 1)
by cusip fyear: replace counter = sum(counter)
by cusip fyear: replace counter = counter[_N]

The first line sets counter = 1 in one and only one observation per analyst for a given firm and forecast year. The second line replaces that by a running total of those. And the last line replaces it with the final total.

[Rochelle Zhang]

Dear Clyde,

Thanks for your solution ! It works !

I apologize for the inconsistency. My raw data has both ticker and cusip as identifiers, I dropped several variables and post smaller data here. I will be more careful next time.

a related question: i still would like to know how to keep the last forecast by analyst , in order to compute other measures such as mean forecast, can you show me how to edit my code from post #1?

Rochelle

[Clyde Schechter]

To identify the last forecast for a firm in a given year by an analyst:

by cusip fyear analys (anndate), sort: gen byte last_forecast = (_n == _N)

The analyst's final forecast will show last_forecast = 1; the other will show last_forecast = 0.

[Rochelle Zhang]

It works great as always. Thanks Clyde

[Helen Chang]

To identify the last forecast for a firm in a given year by an analyst:

by cusip fyear analys (anndate), sort: gen byte last_forecast = (_n == _N)

The analyst's final forecast will show last_forecast = 1; the other will show last_forecast = 0.

Just a related question:

I have a year-firm panel data. How should I write the code to find the most recent observation of the last five years with an increase in sales? Thanks!

[Helen Chang]

Here is the sample data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey double(fyear sale) float Cost
 36704 2019    4.796     2.961
 36465 2018        0         .
 36569 2018  214.381         .
 36572 2018        0     1.223
 36571 2018        0         .
 36722 2019 5254.815         .
 36633 2018        .   232.851
 36722 2015        .         .
 36494 2019    731.4         .
 36636 2018   78.503         .
 36660 2019   56.693         .
 36236 2018    6.829         .
 36711 2019   138.18         .
 36477 2018  282.195         .
 36757 2018    2.166         .
 36635 2019  539.501         .
 36768 2018        0         .
 36619 2019  143.985         .
 36726 2017        .         .
 36399 2018   56.308     3.628
 36677 2018        0         .
 36641 2019   2438.1         .
 36767 2018   37.449         .
 36416 2018  319.042         .
 36512 2019 1191.821         .
 36473 2019    2.319         .
 36720 2018        .         .
 36464 2018        0         .
335466 2015        .         .
 36558 2019        0         .
 36474 2017        .         .
 36786 2019     .165         .
 36779 2019        0         .
 36769 2019        0         .
 36705 2019     .967     1.809
 36642 2019     .115         .
 36637 2019        0         .
 36570 2019   64.428 16.318129
 36599 2019        0    17.126
 36571 2019        0         .
 36558 2018        0         .
 36496 2018        0         .
 36477 2019  456.065  95.63548
 36474 2015        .         .
 36471 2018   30.368         .
 36464 2019   57.052         .
 36084 2019  109.622         .
 36769 2018        0         .
335466 2019   41.813         .
 36474 2016        .         .
 36754 2018   91.867         .
 36444 2018  126.399         .
335466 2016        .         .
 36473 2018     .379         .
 36569 2019  295.245         .
 36641 2016        .         .
 36633 2019  627.316   246.903
 36556 2018        0         .
 34609 2018        0         .
 36722 2018 4308.874         .
 36409 2018   24.669     1.928
 36556 2019        0         .
 36660 2018   31.299         .
 36692 2018     22.5         .
 36704 2018    2.067     4.007
 36482 2019   57.264    39.476
 36718 2018  188.805         .
 36637 2018        .         .
 36635 2018  226.205         .
 36728 2018  317.938         .
 36570 2018   43.657         .
 36330 2019    40.84   100.573
 36722 2016        .         .
 36790 2018     2164         .
 36550 2017        .         .
 36395 2018   322.51         .
335466 2017        .         .
 36444 2017        .         .
 36550 2019  424.385         .
 36717 2018  146.562         .
 36641 2015        .         .
 36717 2019  204.027         .
 36757 2019    2.836         .
 36471 2019   54.815 27.752073
 36599 2018     .036    11.702
 36786 2018    1.135         .
 36754 2019  112.103         .
 36444 2019  158.381  38.71335
 36619 2018  181.974         .
 36642 2018     6.55         .
 36556 2017        .         .
 36465 2019        0         .
 36555 2019  100.557         .
 36084 2018   77.306         .
 36641 2018   2452.8         .
 36607 2019        0         .
 36555 2018   164.09         .
 36722 2017        .         .
 34609 2019        0         .
 36615 2019        .         .
end

[Nick Cox]

You've posted this twice. Please don't that.