turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Generating rolling 12 month totals for multiple in...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-24-2016 09:56 AM

Hello,

I am trying to generate 12 month rolling totals for bankruptcy counts for multiple industry codes. This is what my data looks like for one of those industry codes:

I ran the following code which gives me a running 12-month total for the selected code:

************************************

%let roll_num = 12;

data new;

set newview;

array summed[&roll_num] _temporary_;

if E = &roll_num then E = 1;

else E + 1;

summed[E] = Bankruptcy_CA;

if _N_ >= &roll_num then do;

CA_CT = sum(of summed[*]); * CT means cumulative total;

end;

format CA_CT comma8.;

run;

**********************

Which gives me the desired rolling totals:

The new series is called 'CA_CT'.

My question: How do I code this so I can add more than 1 code (I have 600+) so I can generate the kind of rolling totals shown above? If I add more industry codes to the existing SAS program the rolling totals don't generate properly.

How do I modify the code above?

Thanks

Jack

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jack1

08-24-2016 10:04 AM

Check if you have proc expand, if so look at convert statement.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jack1

08-24-2016 10:06 AM

Sort by industry codes and months. Use "by code;" in the data step.

Instead of using _n_, create your own counter (reatain!) that is reset whenever the industry code changes (first.code); you might also clear your array at that point.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

08-24-2016 02:52 PM

Thanks. Makes sense. In your statement:

*Instead of using _n_, create your own counter (reatain!) that is reset whenever the industry code changes (first.code); you might also clear your array at that point.*

How would I modify my code above to get the desired outcome? I am fairly new to SAS coding. Thanks again....

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jack1

08-24-2016 03:24 PM

http://support.sas.com/kb/41/380.html

```
/* create monthly sales data from January 2008 to December 2010 */
data test;
do mo_period = '01jan2008'd to '31dec2010'd;
sales = round(ranuni(1234567) * 1000, .01);
mo_period = intnx('month', mo_period, 0, 'END');
output;
end;
format mo_period monyy7. sales comma10.2;
run;
/*specify the number of periods in the rolling sum and average*/
%let roll_num = 12;
data new;
set test;
/*create array with specific number of elements*/
/*passing _temporary_ arrays as arguments to functions is not supported*/
/*until SAS 9.2*/
/*if the array is a _TEMPORARY_ array, the elements are automatically retained*/
array summed[&roll_num] _temporary_;
/*alternatively, a non-temporary array can be used but must be retained:*/
/*array summed[&roll_num];*/
/*retain summed;*/
/*E represents the element of the array to assign a sales value*/
/*Increment it by one unless it is equal to &roll_num, at which point*/
/*start over and assign it a value of 1. This causes the oldest period to*/
/*be replaced by the newest period once &roll_num periods have been read.*/
if E = &roll_num then E = 1;
else E + 1;
/*assign value to proper element of the array*/
summed[E] = sales;
/*start summing once &roll_num values have been read from the data set*/
if _N_ >= &roll_num then do;
roll_sum = sum(of summed[*]);
roll_avg = mean(of summed[*]);
end;
format roll_sum roll_avg comma10.2;
run;
proc print; run;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jack1

08-24-2016 03:36 PM

Here's a fully worked example, including both using temporary arrays AND proc expand for a second check.

```
proc sort data=sashelp.stocks out=stocks;
by stock date;
run;
data stocks_avg12;
set stocks;
by stock;
array values(12) _temporary_;
if first.stock then do;
call missing(of values(*));
count=0;
end;
count+1;
values(mod(count, 12)+1) = open;
if count>=12 then average=mean(of values(*));
format average dollar12.2;
run;
proc expand data=stocks_avg12 out=stocks_avg12_method2;
by stock;
id date;
convert open=average2 / transformout=(movave 12 trimleft 11);
run;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jack1

08-25-2016 02:22 AM

Jack1 wrote:

Thanks. Makes sense. In your statement:

Instead of using _n_, create your own counter (reatain!) that is reset whenever the industry code changes (first.code); you might also clear your array at that point.

How would I modify my code above to get the desired outcome? I am fairly new to SAS coding. Thanks again....

This would look like

```
%let roll_num = 12;
data new;
set newview;
by code;
retain counter;
array summed[&roll_num] _temporary_;
if first.code
then do;
counter = 1;
do e = 1 to &roll_num;
summed[e] = 0;
end;
end;
else counter + 1;
if E >= &roll_num
then E = 1;
else E + 1;
summed[E] = Bankruptcy_CA;
if counter >= &roll_num
then do;
CA_CT = sum(of summed[*]); * CT means cumulative total;
end;
format CA_CT comma8.;
run;
```

Note that I slightly changed the condition where e is reset to 1, as to accommodate the result of using e as loop counter.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

08-25-2016 02:05 PM

Works great Kurt! Thanks.....Jack

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jack1

08-24-2016 11:32 AM

These links might help you

http://www.sas-programming.com/2015/05/fast-sql-moving-average-calculation.html

http://www.ats.ucla.edu/stat/sas/faq/creating_tiimeseries_variables_proc_expand.htm

have a look through them.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to rahul88888

06-23-2017 08:23 AM

Dear sirs,

I have a data set from where i need to generate rolling 12 months sum on all the numeric variables. I'm not able to provide the sample data for you but the variable names are below.

Variable Name | repyr | repqtr | repmthdt | DSN | hbr_pi | hbr_ser | htm_pi | htm_ser | flg_NilSettledPay | flg_Windscreen | ap_ind | type | inc_amtt_bnd | prefix2 | product_desc | Source_System | cnt_Outstand_Sum | pay_amtm_Sum | pay_amtt_Sum | rt_est_amt_Sum | inc_outstand_Sum | rec_settle_Sum | inc_settle_Sum | life_cyc_Sum | cnt_notified_Sum | cnt_Settle_Sum | cnt_Reopen_Sum | rec_amtm_Sum | inc_amtt_Sum | notificationtime_Sum | nillsettled_Sum |

Type | Date | Date | Date | Character | Character | Character | Character | Character | Character | Character | Character | Character | Character | Character | Character | Character | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric | Numeric |

and my program code is below using proc sql.

**proc** **sql** nowarn;

create table summary_12MTD as

select distinct t1.repyr label="Reporting Year",t1.repqtr label="Reporting Quarter",t1.repmthdt label="Reporting Month",t1.DSN label="Dataset Name",

t1.hbr_pi label="PI Handling Branch",t1.hbr_ser label="Service Handling Branch",t1.htm_pi label="PI Handling Team",

t1.htm_ser label="Service Handling Team",t1.flg_NilSettledPay label="Nill Settled Flag",

t1.flg_Windscreen label="Windscreen Flag",t1.ap_ind label="PI Indicator",t1.type label="Claim Type",

t1.inc_amtt_bnd label="Incurred Amount Band",t1.prefix2 label="Policy Prefix",

t1.product_desc label="Product Group",t1.Source_System,t1.cnt_Outstand_sum label="Monthly Outstanding Count",

t1.pay_amtm_sum label="Monthly Paid Movement",t1.pay_amtt_sum label="Monthly Total Paid",t1.rt_est_amt_sum label="Estimate Amount",

t1.inc_outstand_sum label="Outstanding Incurred Amount",t1.rec_settle_sum label="Settled Recovered Amount(Monthly)",

t1.inc_settle_sum label="Settled Incurred Amount(Monthly)",t1.life_cyc_sum label="Life cycle(Monthly)",

t1.cnt_notified_sum label="Notified Count(Monthly)",t1.cnt_Settle_sum label="Settled Count(Monthly)",

t1.cnt_Reopen_sum label="Reopen Count(Monthly)",t1.rec_amtm_sum label="Recovered Movement(Monthly)",

t1.inc_amtt_sum label="Total Incurred Amount(Monthly)",t1.notificationtime_sum label="Notification days(Monthly)",

t1.nillsettled_sum label="Nill Settled Count(Monthly)",

sum(t2.life_cyc_sum) as life_cyc_12MTD label="12MTD Lifecycle",

sum(t2.pay_amtt_sum) as pay_amtt_12MTD label="12MTD Total Paid",

sum(t2.rec_settle_sum) as rec_settle_12MTD label="12MTD Settled Recovered Amount",

sum(t2.inc_settle_sum) as inc_settle_121MTD label="12MTD Settled Incurred Amount",

sum(t2.cnt_notified_sum) as cnt_notified_12MTD label="12MTD Notified Count",

sum(t2.cnt_Settle_sum) as cnt_Settle_12MTD label="12MTD Settled Count",

sum(t2.cnt_Reopen_sum) as cnt_Reopen_12MTD label="12MTD Reopened Count",

sum(t2.inc_amtt_sum) as inc_amtt_12MTD label="12MTD Incurred Total",

sum(t2.notificationtime_sum) as notificationtime_12MTD label="12MTD Notification Time",

sum(t2.nillsettled_sum) as nillsettled_12MTD label="12MTD Nill Settled Count"

from work.summary t1 left join work.summary t2

on t1.DSN =t2.DSN

and t1.hbr_pi =t2.hbr_pi

and t1.hbr_ser =t2.hbr_ser

and t1.htm_pi =t2.htm_pi

and t1.htm_ser =t2.htm_ser

and t1.flg_NilSettledPay =t2.flg_NilSettledPay

and t1.flg_Windscreen =t2.flg_Windscreen

and t1.ap_ind =t2.ap_ind

and t1.type =t2.type

and t1.inc_amtt_bnd =t2.inc_amtt_bnd

and t1.prefix2 =t2.prefix2

and t1.product_desc =t2.product_desc

and t1.Source_System =t2.Source_System

and intck("month",t1.repmthdt,t2.repmthdt) between **0** and -**11**

group by t1.repyr,t1.repqtr,t1.repmthdt,t1.DSN,t1.hbr_pi,t1.hbr_ser,t1.htm_pi,t1.htm_ser,t1.flg_NilSettledPay,

t1.flg_Windscreen,t1.ap_ind,t1.type,t1.inc_amtt_bnd,t1.prefix2,t1.product_desc,t1.Source_System,

t1.nillsettled_Sum,t1.notificationtime_Sum,t1.inc_amtt_Sum,t1.rec_amtm_Sum,t1.cnt_Reopen_Sum,

t1.cnt_Settle_Sum,t1.cnt_notified_Sum,t1.life_cyc_Sum,t1.inc_settle_Sum,t1.rec_settle_Sum,

t1.inc_outstand_Sum,t1.rt_est_amt_Sum,t1.pay_amtt_Sum,t1.pay_amtm_Sum,t1.cnt_Outstand_Sum

;

**quit**;

I'm not able to get the correct 12MTD values...

ANY HELP IS MUCH APPRECIATED.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reddi

06-23-2017 09:08 AM

@Reddi Please post your question as it's own new question and note that rolling statistics are more easily calculated in a data step. Especially if you want to do all numeric variables.