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. ... View more

I have two datasets and want to combine them together. Please see below. dataset A firm 1 2 3 ... dataset B date a b c I want to have a dataset C, which looks like dataset C firm     date 1          a 1          b 1          c 2          a 2          b 2          c 3          a 3          b 3          c .... How to achieve this? Thanks. ... View more

I always want to count the number of observations/list these observations that meet certain conditions I define. But I don't really know how to do it. For example, I have the following dataset. I want to find # of observations that firm=1 & dummy=B & assets=10, and list those observations. This seems to be easy as a short dataset, but if there are more than 1 million observations, it will be much more difficult. So I wonder if there is any procedure or other command that will run this purpose. firm     date     dummy$     assets    1          1          B               100    1          1          S               10     1          2          S               10 .... ... View more

I have a dataset and want to calculate a ratio. Please see below for an illustration. firm     date     dummy     assets     ratio 1          1          B               100     100/(100+10) 1          1          S               10       1          2          S               10        0/(10+0)=0 .... I have a dummy B/S, big or small. I want to calculate the ratio of big assets (I don't care about ratio of small assets because it's just a matter of 1- ). I want to use proc sql, but in this case, some firm-date observations don't have big assets (no B dummy), then I assume the ratio is 0. I think of the following code, but don't know if it makes sense. Please help me check. Thanks. proc sql;      create table want as      select *, assets(where dummy="B")/sum(assets) as ratio      from have      group by firm, date; quit; ... View more

I have a dataset with variables date, firm id, dummy and asset. What I want to achieve is for each date, each firm id, calculate a weight for each observation according to asset when dummy=0, and do the same for dummy=1. A simple example is as below date     firm id     dummy     asset     weight 1          A               0          50          50/(50+50)=0.5 1          A               0          50          50/(50+50)=0.5 1          A               1          20          20/(20+80)=0.2 1          A               1          80          80/(20+80)=0.8 I want to get the weight shown above. How can I use proc sql to get the results? Or other ways? Thanks. ... View more