Creating a dummy variable - assigning "1" to previous winners "0" to previous losers

[MattvanPallen]

Hi everyone!

I operate with estimates of annual earnings data (panel data). I have

company, forecaster, time, forecast, earnings realizations

DATA TEST;

INPUT company$ scenario year earnings_Poject realized;

cards;

A 1 1995 10 9

A 2 1995 4 9

A 1 1996 8 7

A 2 1996 5 7

A 1 1997 11 12

A 2 1997 10 12

B 1 1995 20 22

B 2 1995 17 22

B 1 1996 19 20

B 2 1996 26 20

B 1 1997 29 25

B 2 1997 23 25

;

Now I wanted to assign a dummy variable to the observations. The company which earnings change is higher in the last year should receive "1". And so for each year.

E.g.

The case for 1995:

A,1,1995,10,9 <- in 1996 gets "1"

A,2,1995,4,9 <- in 1996 gets "0"

B,1,1995,20,22 <- in 1996 gets "1"

B,2,1995,17,22 <- in 1996 gets "0"

and so on for each year...

I would appreciate any help as the issue gives me only sleepless nights...

[MattvanPallen]

Hi Again!

I have been fighting with this problem during the whole day.

I created an additional table with upper and lower quartiles for earnings of companies through the years.

Now I would like create a dummy variable in the original data which would give "1" if the earnings fall within the upper quartile and "0" if earnigns of a company fall within the lower one.

Hope it will be easier to understand.

Thank you in advance!

[RichardinOz]

In the first part of your question what is your approach when there is a tie between the accuracy of the projections? 

In the second part of your question what is your approach when the change in earnings year on year is in the middle 2 quartiles?

Richard

[MattvanPallen]

@RichardinOz and @ Ksharp

Thank you for your responses.

You are right, sorry for my imprecision.

Lets create two, separate dummies.

UPER and LOWER.

I want to compare the earnings of each company in the each year.

I computed the quartiles of earnigns for each company in the given year in the separate table as posted above.

Every company which will have earnings in the period t-1 in upper quartile will gain "1" in period t in the new column UPER.

Vice versa for the "losers" and lower quartile. Loser then in column LOWE appears "1"

I computed the quartiles in the separate table as posted above.

Can I somehow use the table as role model for classification?

In excel I would build up VLOOKUP and some logical columns.

I really don't know the whole POWER of SAS.

[Ksharp]

"The company which earnings change is higher in the last year should receive "1" "

Don't understand what you mean. what is change , how do you calculate it ? What is your output ?

[Ksharp · Accepted solution]

If I understand what you mean.

DATA TEST;
INPUT company$ scenario year earnings_Poject realized;
cards;
A 1 1995 10 9
A 2 1995 4 9
A 1 1996 8 7
A 2 1996 5 7
A 1 1997 11 12
A 2 1997 10 12
B 1 1995 20 22
B 2 1995 17 22
B 1 1996 19 20
B 2 1996 26 20
B 1 1997 29 25
B 2 1997 23 25
;
run;
data x;
input year upper lower;
cards;
1995 0.53 -0.23
1996 0.45 -0.43
1997 0.32 -0.52
;
run;
data x;
 set x;
run;
proc sort data=test ;by year;run;
data temp;
 merge test x;
 by year;
run;
proc sort data=temp;by company  year scenario;run;

data want;
 set temp;
 if company=lag2(company) and (lag2(earnings_Poject)-earnings_Poject) gt upper then upper_f=1;
 if company=lag2(company) and . lt (lag2(earnings_Poject)-earnings_Poject) lt lower then lower_f=1;
run;

Xia Keshan

Message was edited by: xia keshan

[MattvanPallen]

@Ksharp

Many, many thanks for your help and interest!

I've implemented some changes but it does the trick!