procsql：在一个步骤中根据不同的条件计算多个摘要统计信息

eivgtgni 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(327)

我想使用sas中的procsql计算两个平均值，一个在某个日期之前的行上，另一个在某个日期之后。现在，我分两步来做，然后合并。有没有办法一步到位？谢谢您。

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;

sql sas

来源：https://stackoverflow.com/questions/62588323/proc-sql-calculate-multiple-summary-stats-based-on-different-conditions-in-one

3条答案

按热度按时间

mctunoxg1#

使用sas表示 true 由 1 以及 false 由 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;

给出与代码相同的结果。
请注意，这可能是错误的，因为您忽略了 cal_date = fdate .

赞(0）回复(0）举报 2021-07-24

gmxoilav2#

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;

不需要使用distinct子句，group by将处理该问题。使用proc summary也很容易

赞(0）回复(0）举报 2021-07-24

voj3qocg3#

选择任意值，avg（case when date>cutoff then myvalue else null end）作为avg\u aft，avg（case when date<=cutoff then myvalue else null end）作为avg\u bef from…等。。

赞(0）回复(0）举报 2021-07-24