EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer

Cool Tips & Tricks - 3
Moving Average calculation, Running Total summation
Summarize the output by 0.25%

Previously we introduced two useful tips:
• Calculate the moving average in SAS, instead of export the output to excel, then do the moving average there.
proc sql;
   select division, date, amount as amount_daily, 
   (select sum(b.amount)   from data1 b
   where a.division=b.division and a.date-b.date>=0 and a.date-b.date<=29 
   ) as amount_last30    
   from data1 a
   order by division, date;
Another way:
proc sql;
   select distinct a.division, a.date, a.amount as amount_daily, 
   sum(case a.division=b.division and a.date-b.date>=0 and a.date-b.date<=29 
   then b.amount end) as amount_last30 
   from data1 as a inner join data1 as b 
   on 1=1
   group by division, date;
• Return the running total for a variable
PROC SQL get get the running total by using a subquery at the SELECT clause.
proc sql;
   select name, weight, 
      (select sum(a.weight) from class as a where a.obs <= b.obs) as running_total
   from class as b;

When we summarize the output, we usually need to breakout the output by the nearest 0.25%, say. So you can get the output like the following:

Category      Count
0.0%-0.25%      #1
0.25%-0.50%     #2
0.50%-0.75%     #3

Here is the trick we can run in SAS/SQL:
proc sql;
create table output1
as select round(rate+0.0025/2,0.0025) as new_rate,  count(*) as count
from data1
group by new_rate; quit;

Related links:
Continue to next: SAS Cool Tips & Tricks: Grab data size from a folder or netezza   SAS tutorial home
Back to: SAS Cool Tips & Tricks of Proc SQL: SAS Library dictionaries   Statistics tutorial home