topic Re: create dummy variable that captures an increase in another variable's value in SAS Studio

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

sastuck — Mon, 02 Apr 2018 11:04:26 GMT

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

2011

33.914554388

William P. Sullivan

18297

AGILENT TECHNOLOGIES INC

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

CEO

990

3521.505

3788.302

1922.258

30.199

10252.264

990

6730.759

24844.508

4325.051

28199.283

902.379

0.259

2012

33.914554388

William P. Sullivan

18297

AGILENT TECHNOLOGIES INC

Chief Executive Officer, Executive Director and Member of Executive Committee

CEO

990

3859.183

4007.791

1247.808

30.935

10135.717

990

6276.534

5032.303

4963.284

24120.483

1003.052

0.303

2013

33.914554388

William P. Sullivan

18297

AGILENT TECHNOLOGIES INC

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

CEO

1045

3789.936

4141.2

1228.875

30.661

10235.672

1045

10235.672

10577.11

4841.167

37106.157

1200.729

0.362

2014

33.914554388

William P. Sullivan

18297

AGILENT TECHNOLOGIES INC

Chief Executive Officer, Executive Director and Member of Executive Committee

CEO

1050

6632.83

4569.033

1631.089

31.781

13914.733

1050

13914.733

22578.825

5377.646

37931.67

960.661

0.29

2015

33.914554388

Michael R. McMullen

39774

AGILENT TECHNOLOGIES INC

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

CEO

845.833

2520.205

2434.524

1110.963

70.994

225.333

7207.852

845.833

7207.852

4661.915

12891.199

564.509

0.172

2016

33.914554388

Michael R. McMullen

39774

AGILENT TECHNOLOGIES INC

Chief Executive Officer, President and Director

CEO

1041.667

6341.255

1338.183

48.144

145.166

8914.415

1041.667

8914.415

7169.506

20377.078

571.321

0.177

AABA

2010

15.7205

Carol A. Bartz, Ph.D.

42606

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

6626.995

2114.474

2200

5.365

11946.834

1000

11946.834

3822.859

10429.989

24283.055

722.381

0.06

AABA

2011

15.7205

Carol A. Bartz, Ph.D.

42606

ALTABA INC

Chief Executive Officer, President and Director

CEO

735.025

9414.211

2601.376

477.534

3141.389

16369.535

735.025

16369.535

10843.741

1351.984

0.123

AABA

2012

15.7205

Marissa A. Mayer

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

454.862

35000.002

1120

40.54

36615.404

454.862

36615.404

5886.296

15453.97

47409.022

201.298

0.018

AABA

2013

15.7205

Marissa A. Mayer

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

2.25

8312.316

13847.283

1700

73.863

24935.712

1002.25

24935.711

24027.974

40222.367

164642.746

625.717

0.062

AABA

2014

15.7205

Marissa A. Mayer

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

11752.355

28194.288

1108.8

28.065

42083.508

1000

42083.508

48053.664

36078.517

157910.056

1009.336

0.108

AABA

2015

15.7205

Marissa A. Mayer

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

1.125

14495.494

19935.777

548.711

35981.107

1001.125

35981.107

37453.603

9087.414

54888.581

1340.047

0.142

AABA

2016

15.7205

Marissa A. Mayer

47607

ALTABA INC

Chief Executive Officer, President and Director

CEO

1000

11328.988

13329.961

1751.21

27410.159

1000

27410.158

16959.723

27566.557

42701.643

1599.268

0.167

AAI

2010

35.556214472

Robert L. Fornaro

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

122.834

2163.404

1434

2163.404

2579.579

2249.559

5671.197

545.469

0.398

AAL

2010

97.934339046

Gerard J. Arpey

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

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

Kurt_Bremser — Mon, 02 Apr 2018 11:53:02 GMT

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.

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

sastuck — Mon, 02 Apr 2018 12:43:17 GMT

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;

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

80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

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

art297 — Mon, 02 Apr 2018 13:41:18 GMT

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

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

Kurt_Bremser — Mon, 02 Apr 2018 14:11:06 GMT

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?

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

sastuck — Mon, 02 Apr 2018 14:31:26 GMT

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

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

sastuck — Mon, 02 Apr 2018 14:32:37 GMT

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!

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

Kurt_Bremser — Mon, 02 Apr 2018 16:37:40 GMT

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-data-AKA-generate/ta-p/258712 to convert an existing dataset to a data step.