CODEWITHSUNDEEP
sqlsas
Victor Wang
Victor Wang

Reputation: 935

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;

Upvotes: 1

Views: 408

Answers (3)

linkonabe
linkonabe

Reputation: 846

proc sql;
  create table temp.consensus_forecast as
  select gvkey, datadate,
    avg(case when cal_date < fdate then meanest else . end) as avg_before,
    avg(case when cal_date >= fdate then meanest else . end) as avg_after
  from temp.consensus_forecast
  group by gvkey, fdate;
quit;

No DISTINCT clause is needed, GROUP BY will take care of that. It's also quite easy with PROC SUMMARY

Upvotes: 2

C8H10N4O2
C8H10N4O2

Reputation: 221

select whatever, avg(case when date > cutoff then myValue else null end) as avg_aft, avg(case when date <= cutoff then myValue else null end) as avg_bef from ...etc..

Upvotes: 1

Dirk Horsten
Dirk Horsten

Reputation: 3845

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.

Upvotes: 1

Related Questions