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
Check if you have proc expand, if so look at convert statement.
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.
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....
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;
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;
@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.
Works great Kurt! Thanks.....Jack
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.
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.
@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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.