topic help with sas code- sum in SAS Procedures

help with sas code- sum

shalmali — Wed, 04 Jul 2012 20:22:28 GMT

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.

Re: help with sas code- sum

Linlin — Wed, 04 Jul 2012 21:20:14 GMT

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

Re: help with sas code- sum

PGStats — Wed, 04 Jul 2012 22:08:10 GMT

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;