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

Question

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;

linkonabe · Accepted Answer · 2020-07-01 16:35:05Z

2

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

answered Jul 1, 2020 at 16:35

linkonabe

8469 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dirk Horsten · Accepted Answer · 2020-06-26 05:21:21Z

1

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.

answered Jun 26, 2020 at 5:21

Dirk Horsten

3,8554 gold badges22 silver badges39 bronze badges

Comments

C8H10N4O2 · Accepted Answer · 2020-06-26 05:21:32Z

1

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..

answered Jun 26, 2020 at 5:21

C8H10N4O2

2211 silver badge10 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related