turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Creating a dummy variable - assigning "1" to previ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-19-2014 11:36 AM

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

Accepted Solutions

Solution

04-20-2014
05:25 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2014 05:25 AM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-19-2014 05:18 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-19-2014 09:51 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2014 04:09 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-19-2014 10:48 PM

"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 ?

Solution

04-20-2014
05:25 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2014 05:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-20-2014 04:43 PM

@Ksharp

Many, many thanks for your help and interest!

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