BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MattvanPallen
Calcite | Level 5

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

6 REPLIES 6
MattvanPallen
Calcite | Level 5

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.

Capture.JPG

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
Quartz | Level 8

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
Calcite | Level 5

@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
Super User

"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
Super User

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
Calcite | Level 5

@Ksharp

Many, many thanks for your help and interest!

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1050 views
  • 0 likes
  • 3 in conversation