EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

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;
quit;
``````
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;
quit;
``````
• 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;
quit;
``````

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