SQL group by command

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

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

Basically, I want this in another column:

enter image description here

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

So you're looking for (select std(weekly_ret) as stdret from crash.duvol4 where rret = 1 group by permno, fyear, rret) / (select std(weekly_ret) as stdret from crash.duvol4 where rret = 0 group by permno, fyear, rret) ? — Zynon Putney II, Aug 27 '20 at 13:55
Hi, Zynon Putney II, yes exactly. I am looking for that. Infact, I tried to run your command. It works out fine separately (each part). but the division (/)doesn't work. — Rasheek Irtisam, Aug 27 '20 at 14:11

Parfait · Accepted Answer · 2020-08-27T15:53:36.190

2

Consider conditional aggregation:

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;

edited Aug 27 '20 at 15:53

answered Aug 27 '20 at 15:47

Parfait

104,375
17
94
125

thanks a lot. this is exactly what i was looking for. much appreciated. – Rasheek Irtisam Aug 27 '20 at 16:33

score 0 · Answer 2 · answered Aug 27 '20 at 15:24

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.

SQL group by command

2 Answers2