topic Re: Generating rolling 12 month totals for multiple industry codes in SAS Programming

Generating rolling 12 month totals for multiple industry codes

Jack1 — Wed, 24 Aug 2016 13:56:58 GMT

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

Re: Generating rolling 12 month totals for multiple industry codes

Reeza — Wed, 24 Aug 2016 14:04:46 GMT

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

Re: Generating rolling 12 month totals for multiple industry codes

Kurt_Bremser — Wed, 24 Aug 2016 14:06:55 GMT

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.

Re: Generating rolling 12 month totals for multiple industry codes

rahul88888 — Wed, 24 Aug 2016 15:32:46 GMT

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.

Re: Generating rolling 12 month totals for multiple industry codes

Jack1 — Wed, 24 Aug 2016 18:52:30 GMT

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

Re: Generating rolling 12 month totals for multiple industry codes

Reeza — Wed, 24 Aug 2016 19:24:39 GMT

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;

Re: Generating rolling 12 month totals for multiple industry codes

Reeza — Wed, 24 Aug 2016 19:36:43 GMT

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;

Re: Generating rolling 12 month totals for multiple industry codes

Kurt_Bremser — Thu, 25 Aug 2016 06:22:35 GMT

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

Re: Generating rolling 12 month totals for multiple industry codes

Jack1 — Thu, 25 Aug 2016 18:05:57 GMT

Works great Kurt! Thanks.....Jack

Re: Generating rolling 12 month totals for multiple industry codes

Reddi — Fri, 23 Jun 2017 12:23:16 GMT

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

Character

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.

Re: Generating rolling 12 month totals for multiple industry codes

Reeza — Fri, 23 Jun 2017 13:08:02 GMT

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