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

- 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
- 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
- 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
- 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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- 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
- 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
- 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
- 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

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- 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
- 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
- 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
- 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.