getting max and min value by group

[chiaihuang]

Hi everyone,

I have the following data:

Company ID | Year | Quarter | EPS | EPSRank

I would like to create a new variable which is the difference between the max(EPSRank) and min(EPSRank) for each year. Can anyone help with this? Thanks in advance.

Henry

[Reeza]

This is known as the range. 

You can use PROC means/univariate or Summary. 

Here is an example with proc means -  a dataset is created with the values in addition to the output displayed.  

Proc means data=have  nway;

by year;

output out=yearly_eps_range range(epsRank) = r_epsRank min= min_epsRank max= max_epsRank;

run;

[Kurt_Bremser]

proc sql;
create table want as
select
  company_id, year,
  (max(epsrank) - min(epsrank)) as epsdiff
from have
group by company_id, year
;
quit;

[Vish33 · Accepted solution]

Hi Henry,

Hope you are looking for difference bewteen max and min of EPSRank across all companies by year. If that case try the below one.

data sample;

input Company_ID Year Quarter $2. EPS EPSRank;

cards;

101 2010 Q1 10000 100

101 2010 Q2 20000 90

101 2010 Q4 30000 50

102 2016 Q1 60000 20

102 2016 Q3 40000 30

102 2016 Q4 80000 10

;

run;

proc sort data= sample;

by Year ;

run;

proc sql;

create table required as

select * ,(max(EPSRank)-min(EPSRank)) as diffRank

from sample

group by year;

quit;

[Vish33]

Here Proc sort is not required...!! forgot 🙂