use proc sql to calculate some variables

[SeanZ]

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.

[Reeza]

Can you post some sample data? And output preferably...

[SeanZ]

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.

[LinusH · Accepted solution]

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;