topic Re: getting max and min value by group in SAS Programming

getting max and min value by group

chiaihuang — Fri, 13 Jan 2017 06:00:20 GMT

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

Re: getting max and min value by group

Reeza — Fri, 13 Jan 2017 07:25:59 GMT

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;

Re: getting max and min value by group

Kurt_Bremser — Fri, 13 Jan 2017 07:30:41 GMT

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

Re: getting max and min value by group

Vish33 — Fri, 13 Jan 2017 07:33:51 GMT

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;

Re: getting max and min value by group

Vish33 — Fri, 13 Jan 2017 07:35:40 GMT

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