IBES Analyst Mean Forecast (EPS) for Year 1-3

[Miral Ayan]

Dear all,
I have downloaded the analyst forecast data from IBES and now want to create panel data (Using CUSIP and firm year). Particularly, I want to create variables for the mean forecasts of EPS (given in the variable "MEASURE") 1, 2, and 3. I need your guidance in preparing the panel data using the IBES data below:

TICKER CUSIP OFTIC CNAME STATPERS MEASURE FISCALP FPI NUMEST MEANEST FPEDATS ANNDATS_ACT ANNTIMS_ACT
0001 26878510 EPE EP ENGR CORP 20dec2018 EPS ANN 1 11 -.19 31dec2018 14mar2019 18:00:00
0001 26878510 EPE EP ENGR CORP 17jan2019 EPS ANN 1 11 -.2 31dec2018 14mar2019 18:00:00
0001 26878510 EPE EP ENGR CORP 14feb2019 EPS ANN 1 9 -.23 31dec2018 14mar2019 18:00:00
0001 26878510 EPE EP ENGR CORP 14mar2019 EPS ANN 1 11 -.67 31dec2019 25mar2020 16:50:00
0001 26878510 EPE EP ENGR CORP 18apr2019 EPS ANN 1 9 -.5 31dec2019 25mar2020 16:50:00
0001 26878510 EPE EP ENGR CORP 16may2019 EPS ANN 1 7 -.59 31dec2019 25mar2020 16:50:00
0001 26878510 EPEG EP ENGR CORP 20jun2019 EPS ANN 1 4 -.55 31dec2019 25mar2020 16:50:00
0001 26878510 EPEG EP ENGR CORP 18jul2019 EPS ANN 1 3 -.64 31dec2019 25mar2020 16:50:00
0001 26878510 EPEG EP ENGR CORP 15aug2019 EPS ANN 1 3 -.63 31dec2019 25mar2020 16:50:00
0001 26878510 EPEG EP ENGR CORP 19sep2019 EPS ANN 1 3 -.66 31dec2019 25mar2020 16:50:00
0001 26878510 EPEGQ EP ENGR CORP 17oct2019 EPS ANN 1 1 -.53 31dec2019 25mar2020 16:50:00
0001 26878510 EPE EP ENGR CORP 20dec2018 EPS ANN 2 12 -.31 31dec2019 25mar2020 16:50:00
0001 26878510 EPE EP ENGR CORP 17jan2019 EPS ANN 2 12 -.52 31dec2019 25mar2020 16:50:00
0001 26878510 EPE EP ENGR CORP 14feb2019 EPS ANN 2 10 -.59 31dec2019 25mar2020 16:50:00
0001 26878510 EPE EP ENGR CORP 14mar2019 EPS ANN 2 10 -.57 31dec2020
0001 26878510 EPE EP ENGR CORP 18apr2019 EPS ANN 2 9 -.55 31dec2020
0001 26878510 EPE EP ENGR CORP 16may2019 EPS ANN 2 8 -.54 31dec2020
0001 26878510 EPEG EP ENGR CORP 20jun2019 EPS ANN 2 4 -.66 31dec2020
0001 26878510 EPEG EP ENGR CORP 18jul2019 EPS ANN 2 3 -.75 31dec2020
0001 26878510 EPEG EP ENGR CORP 15aug2019 EPS ANN 2 3 -.75 31dec2020
0001 26878510 EPEG EP ENGR CORP 19sep2019 EPS ANN 2 3 -.91 31dec2020
0001 26878510 EPEGQ EP ENGR CORP 17oct2019 EPS ANN 2 1 -.48 31dec2020
0001 26878510 EPE EP ENGR CORP 20dec2018 EPS ANN 3 10 -.23 31dec2020
0001 26878510 EPE EP ENGR CORP 17jan2019 EPS ANN 3 10 -.4 31dec2020
0001 26878510 EPE EP ENGR CORP 14feb2019 EPS ANN 3 8 -.53 31dec2020
0001 26878510 EPE EP ENGR CORP 14mar2019 EPS ANN 3 1 -.41 31dec2021
0001 26878510 EPE EP ENGR CORP 18apr2019 EPS ANN 3 4 -.43 31dec2021
0001 26878510 EPE EP ENGR CORP 16may2019 EPS ANN 3 6 -.56 31dec2021
0001 26878510 EPEG EP ENGR CORP 20jun2019 EPS ANN 3 3 -.91 31dec2021
0001 26878510 EPEG EP ENGR CORP 18jul2019 EPS ANN 3 2 -1.14 31dec2021
0001 26878510 EPEG EP ENGR CORP 15aug2019 EPS ANN 3 2 -.97 31dec2021
0001 26878510 EPEG EP ENGR CORP 19sep2019 EPS ANN 3 2 -1.27 31dec2021


Thanks

[Miral Ayan]

I want the panel dataset like below:

CUSIP	FYEAR	EPS1	EPS2	EPS3
XXX	2001	1	2	3
XXX	2002	1.2	3	4.8
XXX	2003	1.4	4	6.6
XXX	2004	1.6	5	8.4
XXX	2005	1.8	6	10.2

Thanks