Help using Base SAS procedures

use proc sql to calculate some variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

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;

Please help me with this.


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

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

View solution in original post


All Replies
Super User
Posts: 17,842

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,257

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 198 views
  • 4 likes
  • 3 in conversation