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
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;
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;
proc sql;
create table want as
select
company_id, year,
(max(epsrank) - min(epsrank)) as epsdiff
from have
group by company_id, year
;
quit;
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;
Here Proc sort is not required...!! forgot 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.