CODEWITHSUNDEEP
sqlgroup-bysasproc-sql
Rasheek Irtisam
Rasheek Irtisam

Reputation: 15

SQL group by command

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.

Upvotes: 1

Views: 64

Answers (2)

Parfait
Parfait

Reputation: 107567

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;

Upvotes: 2

Reeza
Reeza

Reputation: 21264

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.

Upvotes: 0

Related Questions