create dummy variable that captures an increase in another variable's value

[sastuck]

Hello,

I need create dummy variable that indicates whether the firm's market value increases in a given year.

This is to test whether executives are rewarded when the firm does well, but to be relatively insulated from downturns in firm performance. 

I was getting started on my datastep:

data paper.ceo_firm;
	Set paper.ceo_firm;
	if annual_return<=0 then increase=0;
	else if annual_return>0 then increase=1;
run;

However, I realized that this would only show whether the growth was positive or negative--nothing to do with growth from the previous year. Do you have any suggestions? Here is an example of what the data look like:

Row

Ticker

year

annual_return

2

A

2011

33.914554388

William P. Sullivan

1

0

0

0

18297

AGILENT TECHNOLOGIES INC

Chief Executive Officer, President, Executive Director and Member of Executive Committee

CEO

990

0

3521.505

3788.302

1922.258

0

30.199

10252.264

990

6730.759

24844.508

4325.051

28199.283

902.379

0.259

3

A

2012

33.914554388

William P. Sullivan

1

0

0

0

18297

AGILENT TECHNOLOGIES INC

Chief Executive Officer, Executive Director and Member of Executive Committee

CEO

990

0

3859.183

4007.791

1247.808

0

30.935

10135.717

990

6276.534

5032.303

4963.284

24120.483

1003.052

0.303

4

A

2013

33.914554388

William P. Sullivan

1

0

0

0

18297

AGILENT TECHNOLOGIES INC

Chief Executive Officer, President, Executive Director and Member of Executive Committee

CEO

1045

0

3789.936

4141.2

1228.875

0

30.661

10235.672

1045

10235.672

10577.11

4841.167

37106.157

1200.729

0.362

5

A

2014

33.914554388

William P. Sullivan

1

0

0

0

18297

AGILENT TECHNOLOGIES INC

Chief Executive Officer, Executive Director and Member of Executive Committee

CEO

1050

0

6632.83

4569.033

1631.089

0

31.781

13914.733

1050

13914.733

22578.825

5377.646

37931.67

960.661

0.29

6

A

2015

33.914554388

Michael R. McMullen

1

0

0

0

39774

AGILENT TECHNOLOGIES INC

Chief Executive Officer, President, Director and Member of Executive Committee

CEO

845.833

0

2520.205

2434.524

1110.963

70.994

225.333

7207.852

845.833

7207.852

4661.915

12891.199

12891.199

564.509

0.172

7

A

2016

33.914554388

Michael R. McMullen

1

0

0

0

39774

AGILENT TECHNOLOGIES INC

Chief Executive Officer, President and Director

CEO

1041.667

0

6341.255

0

1338.183

48.144

145.166

8914.415

1041.667

8914.415

7169.506

20377.078

20377.078

571.321

0.177

8

AABA

2010

15.7205

Carol A. Bartz, Ph.D.

1

0

0

0

42606

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

0

6626.995

2114.474

2200

0

5.365

11946.834

1000

11946.834

3822.859

10429.989

24283.055

722.381

0.06

9

AABA

2011

15.7205

Carol A. Bartz, Ph.D.

1

0

0

0

42606

ALTABA INC

Chief Executive Officer, President and Director

CEO

735.025

0

9414.211

2601.376

477.534

0

3141.389

16369.535

735.025

16369.535

10843.741

0

0

1351.984

0.123

10

AABA

2012

15.7205

Marissa A. Mayer

1

0

0

0

1

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

454.862

0

35000.002

0

1120

0

40.54

36615.404

454.862

36615.404

5886.296

15453.97

47409.022

201.298

0.018

11

AABA

2013

15.7205

Marissa A. Mayer

1

0

0

0

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

2.25

8312.316

13847.283

1700

0

73.863

24935.712

1002.25

24935.711

24027.974

40222.367

164642.746

625.717

0.062

12

AABA

2014

15.7205

Marissa A. Mayer

1

0

0

0

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

0

11752.355

28194.288

1108.8

0

28.065

42083.508

1000

42083.508

48053.664

36078.517

157910.056

1009.336

0.108

13

AABA

2015

15.7205

Marissa A. Mayer

1

0

0

0

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

1.125

14495.494

19935.777

0

0

548.711

35981.107

1001.125

35981.107

37453.603

9087.414

54888.581

1340.047

0.142

14

AABA

2016

15.7205

Marissa A. Mayer

1

0

0

0

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

0

11328.988

13329.961

0

0

1751.21

27410.159

1000

27410.158

16959.723

27566.557

42701.643

1599.268

0.167

15

AAI

2010

35.556214472

Robert L. Fornaro

1

0

0

0

36038

AIRTRAN HOLDINGS INC

Chairman, Chief Executive Officer, President, Chairman of Financial Policy & Risk Committee, Chairman of AirTran Airways, Chief Executive Officer of AirTran Airways and President of AirTran Airways

CEO

564

870

606.57

0

0

0

122.834

2163.404

1434

2163.404

2579.579

2249.559

5671.197

545.469

0.398

16

AAL

2010

97.934339046

Gerard J. Arpey

Let me know if you need any more details. Thanks for the help!

[Kurt_Bremser · Accepted solution]

Use the lag() function to access previous values, and by-group processing:

data paper.ceo_firm;
set paper.ceo_firm;
by company year;
last_return = lag(annual_return);
if not first.company
then increase = (annual_return >  last_return);
else increase = .;
run;

The condition (annual_return > last_return) will return 1 for true or 0 for false.

[sastuck]

Thank you for the suggestion--this looks to be exactly what I need!

However, upon running the code, I ran into an error message:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 data paper.ceo_firm;

72 set paper.ceo_firm;

73 by company year;

74 last_return = lag(annual_return);

75 if not first.company

76 then increase = (annual_return > last_return);

77 else increase = .;

78 run;

ERROR: BY variable company is not on input data set PAPER.CEO_FIRM.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set PAPER.CEO_FIRM may be incomplete. When this step was stopped there were 0 observations and 111 variables.

WARNING: Data set PAPER.CEO_FIRM was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 2315.59k

OS Memory 29100.00k

Timestamp 04/02/2018 12:38:26 PM

Step Count 23 Switch Count 0

Page Faults 0

Page Reclaims 681

Page Swaps 0

Voluntary Context Switches 31

Involuntary Context Switches 0

Block Input Operations 288

Block Output Operations 8

79

80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

92

"company" isn't actually a variable in the data set. "conname" might be what you're looking for here? 

[art297]

Isn't TICKER the variable for which you want to identify whether there were increases? i.e.:

data paper.ceo_firm;
set paper.ceo_firm;
by ticker year;
last_return = lag(annual_return);
if not first.ticker
then increase = (annual_return >  last_return);
else increase = .;
run;

Art, CEO, AnalystFinder.com

[sastuck]

Thank you. This is just what I was looking for. 

[Kurt_Bremser]

Since you did not post example data in a usable form (data step with datelines), I had to write the code on-the-fly. Adapt it to your data structure as needed.

---

@sastuck wrote:

Thank you for the suggestion--this looks to be exactly what I need!

However, upon running the code, I ran into an error message:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 data paper.ceo_firm;

72 set paper.ceo_firm;

73 by company year;

74 last_return = lag(annual_return);

75 if not first.company

76 then increase = (annual_return > last_return);

77 else increase = .;

78 run;

ERROR: BY variable company is not on input data set PAPER.CEO_FIRM.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set PAPER.CEO_FIRM may be incomplete. When this step was stopped there were 0 observations and 111 variables.

WARNING: Data set PAPER.CEO_FIRM was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 2315.59k

OS Memory 29100.00k

Timestamp 04/02/2018 12:38:26 PM

Step Count 23 Switch Count 0

Page Faults 0

Page Reclaims 681

Page Swaps 0

Voluntary Context Switches 31

Involuntary Context Switches 0

Block Input Operations 288

Block Output Operations 8

79

80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

92

"company" isn't actually a variable in the data set. "conname" might be what you're looking for here? 

---

[sastuck]

Could you explain or show an example of what data step with datelines this would look like? Just for future reference. Do you just mean showing the log after running the relevant datastep? Just wondering. 

Thanks!

[Kurt_Bremser]

It would look like this:

data have;
input company $ year annual_return;
datalines;
A 2015 100000
A 2016 110000
A 2017 100000
A 2018 120000
;
run;

Or you could use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert an existing dataset to a data step.