## use proc sql to calculate some variables

Solved
Frequent Contributor
Posts: 122

# use proc sql to calculate some variables

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;

Accepted Solutions
Solution
‎11-08-2013 08:38 PM
Super User
Posts: 5,881

## Re: use proc sql to calculate some variables

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;

Data never sleeps

All Replies
Super User
Posts: 23,724

## Re: use proc sql to calculate some variables

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

Frequent Contributor
Posts: 122

## Re: use proc sql to calculate some variables

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.

Solution
‎11-08-2013 08:38 PM
Super User
Posts: 5,881

## Re: use proc sql to calculate some variables

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;

Data never sleeps
🔒 This topic is solved and locked.