turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- use proc sql to calculate some variables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-08-2013 04:28 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SeanZ

11-08-2013 08:38 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SeanZ

11-08-2013 05:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

11-08-2013 05:50 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SeanZ

11-08-2013 08:38 PM

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