使用宏在数据集中按组对列求和 [英] Summing a Column By Group In a Dataset With Macros

问题描述

我有一个如下所示的数据集:

I have a dataset that looks like:

 Month   Cost_Center      Account    Actual    Annual_Budget
 June     53410           Postage       13      234
 June     53420           Postage       0       432
 June     53430           Postage       48      643
 June     53440           Postage       0       917
 June     53710           Postage       92      662
 June     53410           Phone         73      267
 June     53420           Phone         103     669
 June     53430           Phone         90      763
 ...

我想先分别对 Actual 和 Annual 列求和，然后创建一个变量，如果全年外推的 Actual 大于 Annual 列，它会在其中标记.

I would like to first sum the Actual and Annual columns, respectively and then create a variable where it flags if the Actual extrapolated for the entire year is greater than than Annual column.

我有以下代码:

 Data Test; 
 set Combined;
 %All_CC; /*MACRO TO INCLUDE ALL COST CENTERS*/
 %Total_Other_Expenses;/*MACRO TO INCLUDE SPECIFIC Account Descriptions*/
 Sum_Actual = sum(Actual);
 Sum_Annual = sum(Annual_Budget);
 Run_Rate = Sum_Actual*12;
 if Run_Rate > Sum_Annual then Over_Budget_Alarm = 1;
 run; 

但是，当我运行此代码时，它不会按组求和，例如，这是我得到的输出:

However, when I run this code, it does not sum by group, for example, this is the output I get:

 Account_Description    Sum_Actual Sum_Annual   Run_Rate  Over_Budget_Alarm
      Postage             13      234             146           
      Postage             0       432              0 
      Postage             48      643             963            1
      Postage             0       917             0
      Postage             92      662             634            1

我正在寻找将所有邮资"汇总为实际和年度的输出，只留下一行数据.

I'm looking for output where all the 'postage' are summed for Actual and Annual, leaving just one row of data.

推荐答案

1. 使用 PROC MEANS 汇总数据

2. 使用数据步骤和 IF/THEN 语句来创建您的标志.

1. Use PROC MEANS to summarize the data

2. Use a data step and IF/THEN statement to create your flags.

proc means data=have N SUM NWAY STACKODS;
   class account;
   var amount annual_budget;
   ods output summary = summary_stats1;
   output out = summary_stats2 N = SUM= / AUTONAME;
run;

data want;
  set summary_stats;
  if sum_actual > sum_annual_budget then flag=1; 
  else flag=0;
run;

这篇关于使用宏在数据集中按组对列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！