DATA Step, Macro, Functions and more

getting max and min value by group

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

getting max and min value by group

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


Accepted Solutions
Solution
‎01-13-2017 05:29 AM
Frequent Contributor
Posts: 117

Re: getting max and min value by group

Posted in reply to chiaihuang

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;

 

View solution in original post


All Replies
Super User
Posts: 19,804

Re: getting max and min value by group

Posted in reply to chiaihuang

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;

Super User
Posts: 7,782

Re: getting max and min value by group

Posted in reply to chiaihuang
proc sql;
create table want as
select
  company_id, year,
  (max(epsrank) - min(epsrank)) as epsdiff
from have
group by company_id, year
;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎01-13-2017 05:29 AM
Frequent Contributor
Posts: 117

Re: getting max and min value by group

Posted in reply to chiaihuang

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;

 

Frequent Contributor
Posts: 117

Re: getting max and min value by group

Here Proc sort is not required...!! forgot Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 264 views
  • 5 likes
  • 4 in conversation