Hi All,
I have a data set which contains the following information: company name, year, executive names, title of the executive, and salary. I want to create a new variable "Salary4" which is the sum of the salary of top 4 executives. While creating this new variable, I need to make sure that the salary of an executive with the title CFO is not included even if his salary is among the top 4 executive. If after excluding an executive with CFO title if there are only 3 executives left, I want Salary4 to be the sum of these three executives.
Firm year executive title salary
A 2000 M 100
A 2000 N 200
A 2000 O 300
A 2000 P 350
A 2000 Q 105
A 2000 R CFO 100
A 2001 M 100
A 2001 N 200
A 2001 O 300
A 2001 P 350
A 2001 Q 105
A 2001 R CFO 200
B 2000 X CFO 300
B 2000 Y 200
B 2000 Z 300
B 2000 E 350
B 2000 T 105
C 2000 X 300
C 2000 Y CFO 200
C 2000 Z 300
C 2000 E 350
I an trying to get an output which would look like the following:
Firm year executive title salary salary4
A 2000 M 100 955
A 2000 N 200 955
A 2000 O 300 955
A 2000 P 350 955
A 2000 Q 105 955
A 2000 R CFO 100 955
A 2001 M 100 1005
A 2001 N 250 1005
A 2001 O 300 1005
A 2001 P 350 1005
A 2001 Q 105 1005
A 2001 R CFO 200 1005
B 2000 X CFO 300 855
B 2000 Y 100 855
B 2000 Z 300 855
B 2000 E 350 855
B 2000 T 105 855
C 2000 X 300 950
C 2000 Y CFO 200 950
C 2000 Z 300 950
C 2000 E 350 950
Variable "title" is character and has information only if the executive is a CFO. I would greatly appreciate if someone can help me with the coding.
Thank you.
Hi,
your data is different than the data in your output.
data have;
infile cards missover;
input Firm $ year executive $ salary title $;
cards;
A 2000 M 100
A 2000 N 200
A 2000 O 300
A 2000 P 350
A 2000 Q 105
A 2000 R 100 CFO
A 2001 M 100
A 2001 N 250
A 2001 O 300
A 2001 P 350
A 2001 Q 105
A 2001 R 200 CFO
B 2000 X 300 CFO
B 2000 Y 100
B 2000 Z 300
B 2000 E 350
B 2000 T 105
C 2000 X 300
C 2000 Y 200 CFO
C 2000 Z 300
C 2000 E 350
;
proc sort data=have;
by firm year title decending salary;
run;
data have;
set have;
by firm year;
if first.year then count=0;
count+1;
if count<5 and title='' then gp=1;
run;
proc sql;
create table want as
select a.Firm,a.year,a.executive,a.salary,a.title,salary4
from have a,(select firm,year ,sum(salary) as salary4 from have where gp=1 group by firm,year) b
where a.firm=b.firm and a.year=b.year;
quit;
proc print;run;
Linlin
Here is one way :
data have;
infile cards missover;
input Firm $ year executive $ salary title $;
cards;
A 2000 M 100
A 2000 N 200
A 2000 O 300
A 2000 P 350
A 2000 Q 105
A 2000 R 100 CFO
A 2001 M 100
A 2001 N 250
A 2001 O 300
A 2001 P 350
A 2001 Q 105
A 2001 R 200 CFO
B 2000 X 300 CFO
B 2000 Y 100
B 2000 Z 300
B 2000 E 350
B 2000 T 105
C 2000 X 300
C 2000 Y 200 CFO
C 2000 Z 300
C 2000 E 350
;
proc sort data=have; by firm year title descending salary; run;
data top4(keep=firm year salary4);
do i=1 by 1 until (last.year);
set have(where=(title ne "CFO"));
by firm year;
if i < 5 then salary4 = sum(salary4, salary);
end;
run;
data want;
merge have top4;
by firm year;
run;
proc print; run;
PG
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.