help with sas code- sum

[shalmali]

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.

[Linlin]

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

[PGStats]

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