I have a data with firmid(id) and returns(ret). I want for each id calculate the mean of the top 5% returns, the mean of the left 95% returns, and take the difference between them. I created a new variable rank, which indicates whether the ret is 5%, 10%, 15%, etc. and mark it as 1, 2, ...20 (20 groups). how can I use a proc sql to simply calculate this? I want to write something like
proc sql;
create table want as
select*, (mean(ret) if rank=1) - (mean(ret) if rank>1) as diff
from have
group by id;
quit;
Please help me with this.
The corresponding syntax in SQL for IF is CASE:
proc sql;
create table want as
select id, mean(case rank when 1 then ret else . end) - mean(case when rank>1 then ret else . end) as diff
from have
group by id;
quit;
Can you post some sample data? And output preferably...
sure. Some data like below.
id ret rank
1 0.1 1
1 0.05 1
1 0.2 2
1 0.8 20
1 1 20
I want the mean(ret) if rank=1 is (0.1+0.05)/2=.0075, mean(ret) if rank>1 is (0.2+0.8+1)/3=0.67, so the diff=0.0075-0.67.
The corresponding syntax in SQL for IF is CASE:
proc sql;
create table want as
select id, mean(case rank when 1 then ret else . end) - mean(case when rank>1 then ret else . end) as diff
from have
group by id;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.