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!
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.
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.
Thank you for the suggestion--this looks to be exactly what I need!
However, upon running the code, I ran into an error message:
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
Thank you. This is just what I was looking for.
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;7071 data paper.ceo_firm;72 set paper.ceo_firm;73 by company year;74 last_return = lag(annual_return);75 if not first.company76 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 secondsuser cpu time 0.01 secondssystem cpu time 0.00 secondsmemory 2315.59kOS Memory 29100.00kTimestamp 04/02/2018 12:38:26 PMStep Count 23 Switch Count 0Page Faults 0Page Reclaims 681Page Swaps 0Voluntary Context Switches 31Involuntary Context Switches 0Block Input Operations 288Block Output Operations 87980 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?
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!
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.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.