sql - 计算 SAS 中的列累积总和和百分比

Question

我需要一些帮助来创建 SAS proc SQL 查询。

考虑以下数据集，其中来自不同地区的销售额已经按 3 小时分块(它只是一个子集，实际数据涵盖 24 小时):

 Date        ObsAtHour Region   Sales
 1/1/2018    2         Asia     76 
 1/1/2018    2         Africa   5 
 1/1/2018    5         Asia     14
 1/1/2018    5         Africa   10
 2/1/2018    2         Asia     40
 2/1/2018    2         Africa   1 
 2/1/2018    5         Asia     15
 2/1/2018    5         Africa   20

我得到过去 45 天的数据..

我想做两件事

1) 按日期、ObsAtHour 和 Region 分组并获得销售额的累计总和，这样我得到类似的东西

 Date        ObsAtHour Region   Sales CumSales
 1/1/2018    2         Asia     76    76
 1/1/2018    2         Africa   5     5
 1/1/2018    5         Asia     14    90
 1/1/2018    5         Africa   10    15
 2/1/2018    2         Asia     40    40
 2/1/2018    2         Africa   1     1
 2/1/2018    5         Asia     15    55
 2/1/2018    5         Africa   20    21

2) 获取销售额百分比，该百分比指示每个区域在任何 obsAtHour 已实现的每日销售额百分比。它看起来像:

 Date        ObsAtHour Region   Sales CumSales  Pct
 1/1/2018    2         Asia     76    76        84%
 1/1/2018    2         Africa   5     5         33%
 1/1/2018    5         Asia     14    90        100%
 1/1/2018    5         Africa   10    15        100%
 2/1/2018    2         Asia     40    40        72% 
 2/1/2018    2         Africa   1     1         4.76%
 2/1/2018    5         Asia     15    55        100%
 2/1/2018    5         Africa   20    21        100% 

非常感谢您的帮助。

Answer 1

类似下面的内容

data have;
input Date:mmddyy10.        ObsAtHour Region $  Sales;
format date mmddyy10;
datalines;
1/1/2018    2         Asia     76 
1/1/2018    2         Africa   5 
1/1/2018    5         Asia     14
1/1/2018    5         Africa   10
2/1/2018    2         Asia     40
2/1/2018    2         Africa   1 
2/1/2018    5         Asia     15
2/1/2018    5         Africa   20
 ;
 proc sort data=have;
 by date region;
 run;

/* this gives moving sum*/
 data have1;
format date mmddyy10.;
set have;
by   date region;
 if first.region then sumsales = sales;
  else sumsales+sales;
  run;

/* get the total sales from your intial table by group and join it back 
and calculate the percent*/
proc sql;
select a.*, sumsales/tot_sales  as per format =percent10.2 from 
(select * from have1)a
inner join
(select region , date, sum(sales) as tot_sales
from have
group by 1, 2)b
on a.region =b.region
 and a.date =b.date;