topic Re: use proc sql to calculate some variables in SAS Procedures

use proc sql to calculate some variables

SeanZ — Fri, 08 Nov 2013 21:28:17 GMT

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.

Re: use proc sql to calculate some variables

Reeza — Fri, 08 Nov 2013 22:33:19 GMT

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

Re: use proc sql to calculate some variables

SeanZ — Fri, 08 Nov 2013 22:50:41 GMT

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.

Re: use proc sql to calculate some variables

LinusH — Sat, 09 Nov 2013 01:38:30 GMT

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;