Filter Data

[jozuleta]

Hi all,

I want to compare forecasts done by at least two different analysts (ESTIMID) for the same stock (TICKER) having the same horizon (HORIZON), currency (ESTCUR), and announce date (ANNDATS).

Since my dataset is still too large I want to filter out all the forecast matches which are done by the same analysts (since it would not be useful for me to compare forecasts of the same stock done by the same analyst).

As an example please see three different matches:

ROW // TICKER // ESTIMID // HORIZON // VALUE // ESTCURR // USFIRM // ANNDATS
1 // X // A // 12 // 4.6 // CAD // 0 // 23MAR2014
2 // X // A //12 // 4.7 // CAD // 0 // 23MAR2014

>>>> This match is NOT useful since the two forecasts are done by the same ESTMID (A), therefore these two rows should be filtered out (delete) of the table.

3 // Y // B // 12 // 5.5 // CAD // 0 // 25JUN2014
4 // Y // C // 12 // 3.9 // CAD // 0 // 25JUN2014

>>>> This match is useful since the two forecasts are done by two different ESTIMID (B and C), therefore these two rows should stay in the table

5 // Z // D // 12 // 120 // BPN // 0 // 06DEC2016
6 // Z // E // 12 // 130 // BPN // 0 // 06DEC2016
7 // Z // E // 12 // 105 // BPN // 0 // 06DEC2016

>>>> This match is also useful since the three forecasts are done by at least two different ESTIMID (D and E), therefore this three rows should stay in the table.

This is at the moment my code with the matches:

PROC SQL;
CREATE TABLE WORK.PTMatchInt 
AS SELECT TICKER , ESTIMID , HORIZON , 'VALUE'n , ESTCUR , USFIRM , ANNDATS 
FROM IBES.PTGDET
WHERE USFIRM=0
group by TICKER , HORIZON , ESTCUR , ANNDATS having count(*) >=2;
QUIT;

I would appreciate if someone could provide me with some help 🙂
Thanks in advance!

Jorge

[Reeza · Accepted solution]

Add another condition to your having clause. Max(estimid) NE min(estimid)

If the max is different than the min, you have at least two different estimators.

[Reeza]

In SQL MAX/MIN works on both character and numerics so you can use this approach. It will not work in a datastep, and may not in other SQL types. 

---

@jozuleta wrote:

Hi all,

I want to compare forecasts done by at least two different analysts (ESTIMID) for the same stock (TICKER) having the same horizon (HORIZON), currency (ESTCUR), and announce date (ANNDATS).

Since my dataset is still too large I want to filter out all the forecast matches which are done by the same analysts (since it would not be useful for me to compare forecasts of the same stock done by the same analyst).

As an example please see three different matches:

ROW // TICKER // ESTIMID // HORIZON // VALUE // ESTCURR // USFIRM // ANNDATS
1 // X // A // 12 // 4.6 // CAD // 0 // 23MAR2014
2 // X // A //12 // 4.7 // CAD // 0 // 23MAR2014

>>>> This match is NOT useful since the two forecasts are done by the same ESTMID (A), therefore these two rows should be filtered out (delete) of the table.

3 // Y // B // 12 // 5.5 // CAD // 0 // 25JUN2014
4 // Y // C // 12 // 3.9 // CAD // 0 // 25JUN2014

>>>> This match is useful since the two forecasts are done by two different ESTIMID (B and C), therefore these two rows should stay in the table

5 // Z // D // 12 // 120 // BPN // 0 // 06DEC2016
6 // Z // E // 12 // 130 // BPN // 0 // 06DEC2016
7 // Z // E // 12 // 105 // BPN // 0 // 06DEC2016

>>>> This match is also useful since the three forecasts are done by at least two different ESTIMID (D and E), therefore this three rows should stay in the table.

This is at the moment my code with the matches:

PROC SQL;
CREATE TABLE WORK.PTMatchInt 
AS SELECT TICKER , ESTIMID , HORIZON , 'VALUE'n , ESTCUR , USFIRM , ANNDATS 
FROM IBES.PTGDET
WHERE USFIRM=0
group by TICKER , HORIZON , ESTCUR , ANNDATS having count(*) >=2;
QUIT;

I would appreciate if someone could provide me with some help 🙂
Thanks in advance!

Jorge

---