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