Question: I have a table where the primary id is *permno* (company identifier). The table also contains *year* and *weekly returns* over that year. Finally, it has a column named 'rret' which is 1 when the weekly return is greater than average yearly return (upweek), otherwise 0 (downweek).

![enter image description here](https://i.stack.imgur.com/HivzH.png)

I want to calculate standard deviation of *weekly return* for upweek and downweek for a company in a given year and then calculate ratio between two. So far, I have just calculated standard deviation of *weekly return* using the following code (for upweek and downweek):

```
proc sql; 
create table crash.duvol5 as 
select permno, fyear, rret, std(weekly_ret) as stdret 
from crash.duvol4
group by permno, fyear, rret
;
quit; 

```

![enter image description here](https://i.stack.imgur.com/YJVfV.png)

Basically, I want this in another column:

![enter image description here](https://i.stack.imgur.com/OEA9x.png)

I am not sure how to calculate it with SQL. Any help will be appreciated. Thanks in advance.

Answer: 
Consider conditional aggregation:

```sql
proc sql; 
   CREATE TABLE crash.duvol5 AS
   SELECT permno, fyear, 
          std(weekly_ret) AS stdret,
          std(CASE WHEN rret = 1 THEN weekly_ret ELSE . END) /
          std(CASE WHEN rret = 0 THEN weekly_ret ELSE . END) AS stdret_ratio 
   FROM crash.duvol4
   GROUP BY permno, fyear;
quit; 

```
Division works fine but you need to use LAG() to get the value to another line.

```
data want;
    set have;
    by permno fyear;
    prev = lag(stdret);
    if last.fyear then ratio = stdret/prev;
run;

```

Untested - please post data as text to make it easier to test and answer your questions. I'd have to type out the data from the image and I'm too lazy tbh.