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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.