proc sql: calculate multiple summary stats based on different conditions in one step

I want to calculate two averages, one over rows before a certain date, and the other after a certain date, using proc SQL in SAS. Currently, I’m doing this in two steps and then merge. Is there a way to do this in one step? Thank you.

proc SQL;

create table temp.consensus_forecast_1 as 

select distinct gvkey, datadate, avg(meanest) as avg_before from temp.consensus_forecast

where cal_date < fdate

group by gvkey, fdate;

quit;


proc SQL;

create table temp.consensus_forecast_2 as 

select distinct gvkey, datadate, avg(meanest) as avg_after from temp.consensus_forecast

where cal_date > fdate

group by gvkey, fdate;

quit;

Answer

Using the fact that SAS represents true by 1 and false by 0:

proc SQL;
    create table temp.consensus_forecast as 
    select distinct gvkey, datadate, 
        sum(meanest * (cal_date < fdate)) / sum(cal_date < fdate) as avg_before,
        sum(meanest * (cal_date > fdate)) / sum(cal_date > fdate) as avg_after
    from temp.consensus_forecast
    where 
    group by gvkey, fdate;
quit;

gives the same result as your code.

Note that this is probably wrong, because you neglect the cases where cal_date = fdate.