SAS Support Communities

I am trying to forecast month end balances of the TOTAL portfolio using PROC ARIMA procedure. INside the portfolio there are 3 major product segments A, B and C. Each of which has a separate trend and seasonality as shown below in the graph I wanted to try two methods, method 1: Forecast of the TOTAL portfolio balance using PROC ARIMA (univariate) method 2: Forecast of the TOTAL portfolio balance using PROC ARIMA (multi variate) using segment A, B and C BALANCES as input variables    Need help in forecasting the portoflio balance with multiple input variables, like average term , average interest rate, prepayments etc.. Which procedure can I use to achieve this?   Method 1 works fine but with method 2 I am getting these warnings More values of input variable A are needed More values of input variable B are needed More values of input variable C are needed   METHOD 1: proc arima data=upb_2 plots=all; identify var=UPB(1,12) noprint; estimate noconstant method=ml; forecast lead=17 interval=month id=RPT_PRD_DATE out=outf; run;   METHOD 2: proc arima data=UPB_INDUSTRY_2 plots=all; identify var=TOTAL(1,12) noprint crosscorr=(A B C); estimate input=(A B C)  noconstant method=ml; forecast lead=17 interval=month id=RPT_PRD_DATE_2 out=outf_input; run;     ... View more

Attached data has balance at the end of each month for different Vintages in the portfolio. I need to forecast the balance for each Vintage at the end of 2019. Is there any time series forecast procedure I could use to solve this problem?    Period Months 2010Vintage Months 2011Vintage Months 2012Vintage Months 2013Vintage Months 2014Vintage Months 2015Vintage Months 2016Vintage Months 2017Vintage Months 2018Vintage 20100131 0 $14,816,094.47                                 20100228 1 $36,073,822.81                                 20100331 2 $88,085,723.58                                 20100430 3 $166,044,748.58                                 20100531 4 $233,364,277.08                                 20100630 5 $306,311,958.90                                 20100731 6 $363,381,329.39                                 20100831 7 $411,055,725.29                                 20100930 8 $450,345,979.39                                 20101031 9 $486,305,056.45                                 20101130 10 $521,087,617.36                                 20101231 11 $553,758,985.43                                 20110131 12 $533,557,999.31 0 $37,127,481.90                             20110228 13 $512,169,116.43 1 $87,465,609.58                             20110331 14 $487,615,080.00 2 $189,230,144.11                             20110430 15 $468,013,614.86 3 $324,981,287.31                             20110531 16 $448,184,546.28 4 $450,679,702.76                             20110630 17 $429,654,785.81 5 $554,236,748.35                             20110731 18 $413,207,051.26 6 $629,886,339.85                             20110831 19 $395,690,799.44 7 $714,247,404.51                             20110930 20 $379,256,133.08 8 $788,282,125.87                             20111031 21 $363,235,500.00 9 $843,586,030.48                             20111130 22 $348,482,189.28 10 $902,364,141.74                             20111231 23 $334,235,745.73 11 $956,817,529.96                             20120131 24 $319,573,546.61 12 $921,874,086.73 0 $64,586,196.68                         20120229 25 $361,910,653.25 13 $886,452,711.23 1 $152,581,132.28                         20120331 26 $342,380,725.50 14 $846,206,649.63 2 $343,993,453.64                         20120430 27 $324,353,357.48 15 $809,590,716.71 3 $563,868,148.92                         20120531 28 $307,557,959.77 16 $774,992,546.56 4 $754,594,235.56                         20120630 29 $291,673,275.10 17 $743,132,329.27 5 $907,157,346.00                         20120731 30 $276,258,757.62 18 $712,279,477.92 6 $1,018,046,010.82                         20120831 31 $261,314,809.87 19 $680,905,577.45 7 $1,140,724,744.03                         20120930 32 $248,091,764.53 20 $653,905,368.65 8 $1,254,656,070.88                         20121031 33 $233,993,326.40 21 $623,380,884.41 9 $1,350,556,892.29                         20121130 34 $221,334,017.97 22 $596,527,594.16 10 $1,402,388,632.61                         20121231 35 $209,755,388.83 23 $571,221,132.14 11 $1,446,045,641.11                         20130131 36 $197,611,306.60 24 $544,660,957.96 12 $1,393,484,108.61 0 $85,102,561.15                     20130228 37 $185,426,506.79 25 $518,862,568.14 13 $1,343,608,821.67 1 $167,729,888.10                     20130331 38 $172,550,575.65 26 $490,392,424.40 14 $1,289,103,867.04 2 $312,321,877.93                     20130430 39 $160,525,786.69 27 $462,663,638.93 15 $1,236,139,534.77 3 $550,730,813.52                     20130531 40 $149,620,101.10 28 $437,323,804.42 16 $1,187,256,113.91 4 $790,658,875.91                     20130630 41 $140,173,339.63 29 $414,664,837.59 17 $1,143,766,963.31 5 $976,875,029.76                     20130731 42 $130,407,186.11 30 $391,116,063.78 18 $1,096,712,504.39 6 $1,147,625,985.79                     20130831 43 $121,467,848.14 31 $369,135,278.50 19 $1,051,633,027.73 7 $1,289,409,850.89                     20130930 44 $113,159,867.98 32 $348,182,248.23 20 $1,009,465,604.34 8 $1,402,457,790.27                     20131031 45 $105,162,374.47 33 $328,027,278.14 21 $966,934,920.09 9 $1,492,175,380.68                     20131130 46 $98,249,061.87 34 $309,875,919.23 22 $928,642,908.04 10 $1,550,181,775.93                     20131231 47 $91,713,175.97 35 $292,306,650.11 23 $890,942,192.78 11 $1,619,305,990.84                     20140131 48 $85,121,438.56 36 $274,559,109.29 24 $852,419,850.14 12 $1,564,405,844.22 0 $79,658,363.84                 20140228 49 $78,074,434.45 37 $257,192,323.61 25 $814,709,244.45 13 $1,508,626,242.87 1 $165,672,726.26                 20140331 50 $70,984,991.63 38 $238,524,043.14 26 $772,362,602.37 14 $1,445,557,361.29 2 $333,126,365.02                 20140430 51 $64,499,149.75 39 $222,058,703.29 27 $733,066,539.82 15 $1,388,349,812.11 3 $584,661,186.60                 20140531 52 $58,853,653.30 40 $207,250,486.84 28 $696,201,552.02 16 $1,333,271,135.46 4 $819,181,575.30                 20140630 53 $53,619,661.09 41 $193,547,049.96 29 $660,248,029.13 17 $1,279,232,622.58 5 $1,002,908,345.41                 20140731 54 $48,495,820.08 42 $180,217,094.88 30 $625,079,410.18 18 $1,226,416,495.62 6 $1,164,472,920.32                 20140831 55 $43,986,926.82 43 $168,479,950.10 31 $591,836,269.08 19 $1,176,211,643.36 7 $1,299,620,435.59                 20140930 56 $39,437,260.26 44 $156,901,110.96 32 $558,671,888.72 20 $1,124,666,367.02 8 $1,418,670,317.42                 20141031 57 $35,171,233.96 45 $146,438,997.35 33 $526,677,999.83 21 $1,075,397,908.70 9 $1,517,594,890.73                 20141130 58 $31,644,664.94 46 $137,667,222.58 34 $499,505,689.70 22 $1,032,966,898.65 10 $1,590,461,993.86                 20141231 59 $27,879,719.73 47 $128,258,624.25 35 $470,512,492.48 23 $986,611,052.39 11 $1,675,184,544.74                 20150131 60 $24,444,694.23 48 $119,929,907.14 36 $442,981,533.87 24 $942,728,508.43 12 $1,618,848,654.34 0 $94,685,593.23             20150228 61 $21,182,123.08 49 $111,160,206.08 37 $416,077,007.81 25 $899,403,019.01 13 $1,561,099,328.01 1 $207,121,218.33             20150331 62 $18,009,277.10 50 $101,625,935.40 38 $386,721,886.60 26 $849,395,823.18 14 $1,494,241,791.36 2 $410,786,293.85             20150430 63 $15,431,750.70 51 $93,496,123.83 39 $359,798,480.92 27 $804,084,077.87 15 $1,434,293,394.54 3 $724,892,892.43             20150531 64 $13,177,575.31 52 $85,913,943.47 40 $335,621,273.35 28 $762,536,181.90 16 $1,378,891,481.45 4 $1,002,250,688.98             20150630 65 $11,299,058.58 53 $78,467,147.16 41 $311,576,733.50 29 $718,733,975.08 17 $1,321,041,001.43 5 $1,224,613,464.77             20150731 66 $9,657,875.94 54 $71,461,415.62 42 $288,587,940.88 30 $677,351,817.55 18 $1,264,865,047.17 6 $1,427,452,062.66             20150831 67 $8,405,723.83 55 $65,195,186.03 43 $268,012,936.81 31 $637,668,889.71 19 $1,211,372,116.32 7 $1,577,053,715.92             20150930 68 $7,356,875.72 56 $59,160,140.96 44 $248,506,700.99 32 $599,406,381.00 20 $1,160,250,775.75 8 $1,714,652,471.15             20151031 69 $6,480,795.27 57 $53,450,012.89 45 $229,782,846.76 33 $562,540,087.17 21 $1,110,000,396.43 9 $1,814,956,977.67             20151130 70 $5,750,055.06 58 $48,367,874.41 46 $212,984,156.02 34 $528,081,626.03 22 $1,062,443,398.71 10 $1,892,038,991.36             20151231 71 $5,248,898.69 59 $43,386,941.37 47 $196,784,957.22 35 $494,220,834.27 23 $1,015,133,096.31 11 $1,971,086,601.19             20160131 72 $4,822,041.83 60 $38,916,609.99 48 $182,233,021.66 36 $463,587,266.32 24 $971,231,320.15 12 $1,908,909,571.52 0 $130,721,294.27         20160229 73 $4,365,844.42 61 $34,018,063.63 49 $165,651,113.32 37 $429,491,237.15 25 $921,174,906.28 13 $1,836,074,135.97 1 $290,083,621.26         20160331 74 $3,964,780.71 62 $29,684,512.09 50 $149,653,396.26 38 $395,065,623.87 26 $870,609,225.88 14 $1,760,772,566.49 2 $591,329,263.07         20160430 75 $3,639,422.27 63 $26,187,669.55 51 $136,536,204.87 39 $364,907,565.57 27 $824,526,764.97 15 $1,691,294,644.98 3 $911,733,254.08         20160531 76 $3,388,545.17 64 $23,062,423.23 52 $124,406,548.27 40 $336,894,195.29 28 $779,888,204.43 16 $1,623,684,013.21 4 $1,188,859,719.95         20160630 77 $3,140,723.88 65 $20,172,728.27 53 $113,259,104.90 41 $310,718,145.31 29 $736,961,617.81 17 $1,558,262,420.07 5 $1,439,374,798.55         20160731 78 $2,920,528.68 66 $17,943,895.82 54 $103,033,383.38 42 $286,524,548.32 30 $696,848,882.27 18 $1,497,177,754.50 6 $1,611,096,562.16         20160831 79 $2,734,885.09 67 $15,864,979.49 55 $92,921,405.56 43 $262,057,298.36 31 $654,701,204.15 19 $1,432,234,512.96 7 $1,785,145,815.04         20160930 80 $2,572,139.24 68 $14,081,528.82 56 $83,863,982.57 44 $239,903,671.19 32 $615,470,883.55 20 $1,371,128,879.36 8 $1,938,036,022.45         20161031 81 $2,432,106.20 69 $12,701,647.49 57 $75,548,569.80 45 $219,481,679.82 33 $577,896,074.05 21 $1,312,651,408.45 9 $2,039,832,879.17         20161130 82 $2,291,441.91 70 $11,483,239.66 58 $67,985,988.22 46 $200,773,279.83 34 $543,345,143.14 22 $1,257,470,419.20 10 $2,119,240,601.20         20161231 83 $2,176,766.65 71 $10,479,646.87 59 $60,838,941.54 47 $183,083,715.19 35 $509,419,444.76 23 $1,203,551,753.99 11 $2,198,068,373.75         20170131 84 $2,100,665.37 72 $9,618,765.64 60 $54,047,757.42 48 $165,971,927.78 36 $475,875,545.26 24 $1,148,121,392.37 12 $2,123,021,142.05 0 $115,839,429.92     20170228 85 $1,995,009.76 73 $8,797,674.92 61 $47,618,364.47 49 $149,256,199.08 37 $443,416,430.18 25 $1,094,818,108.31 13 $2,049,228,917.40 1 $241,078,214.61     20170331 86 $1,883,481.91 74 $7,875,117.03 62 $41,826,044.73 50 $131,653,756.96 38 $407,603,978.89 26 $1,034,001,631.28 14 $1,964,853,570.00 2 $476,648,887.07     20170430 87 $1,808,373.86 75 $7,322,513.76 63 $37,375,499.88 51 $118,358,526.05 39 $378,280,079.97 27 $982,209,864.08 15 $1,894,818,103.52 3 $811,142,069.10     20170531 88 $1,699,535.34 76 $6,732,964.36 64 $33,305,581.18 52 $104,996,126.60 40 $348,213,285.65 28 $928,119,616.12 16 $1,818,605,917.47 4 $1,118,228,693.52     20170630 89 $1,605,970.68 77 $6,230,685.34 65 $29,742,244.13 53 $93,545,415.90 41 $321,351,582.78 29 $877,573,384.02 17 $1,747,068,035.68 5 $1,361,337,327.31     20170731 90 $1,535,387.33 78 $5,760,666.12 66 $26,668,076.18 54 $83,667,980.00 42 $296,392,818.16 30 $829,346,695.59 18 $1,678,887,041.00 6 $1,564,676,377.51     20170831 91 $1,460,371.52 79 $5,327,703.02 67 $24,036,932.53 55 $74,722,544.78 43 $271,880,143.29 31 $781,743,717.76 19 $1,609,006,596.14 7 $1,760,934,265.15     20170930 92 $1,413,321.61 80 $4,990,106.09 68 $22,012,420.11 56 $66,933,696.83 44 $250,347,351.40 32 $737,763,451.20 20 $1,544,760,041.58 8 $1,907,073,752.15     20171031 93 $1,324,526.93 81 $4,719,105.30 69 $20,120,763.55 57 $59,485,680.41 45 $228,571,426.36 33 $693,617,421.70 21 $1,478,721,530.21 9 $2,023,921,130.25     20171130 94 $1,290,986.08 82 $4,453,860.36 70 $18,613,203.09 58 $52,959,653.99 46 $209,599,551.14 34 $652,892,173.36 22 $1,417,716,854.93 10 $2,112,179,764.37     20171231 95 $1,255,462.34 83 $4,235,055.74 71 $17,330,662.25 59 $46,972,439.95 47 $192,161,658.75 35 $614,449,160.50 23 $1,357,941,979.25 11 $2,198,825,460.34     20180131 96 $1,220,659.01 84 $3,971,340.79 72 $16,231,490.21 60 $41,005,285.12 48 $174,739,916.22 36 $574,874,504.74 24 $1,296,557,611.53 12 $2,121,553,323.91 0 $122,235,033.14 20180228 97 $1,176,631.10 85 $3,698,507.95 73 $15,135,773.73 61 $35,371,533.19 49 $158,147,957.18 37 $536,773,065.02 25 $1,236,990,263.03 13 $2,045,154,611.61 1 $254,922,049.44 20180331 98 $1,118,925.96 86 $3,488,698.16 74 $14,089,088.02 62 $30,111,303.32 50 $140,811,648.14 38 $495,234,176.30 26 $1,170,922,760.60 14 $1,959,518,577.66 2 $531,981,238.62 20180430 99 $1,061,173.76 87 $3,253,513.38 75 $13,286,483.23 63 $25,960,274.52 51 $126,794,234.18 39 $458,635,667.95 27 $1,111,817,056.92 15 $1,882,385,271.76 3 $844,907,882.41 20180531 100 $1,021,849.97 88 $3,062,574.22 76 $12,470,608.76 64 $22,289,675.40 52 $114,003,804.36 40 $423,761,790.89 28 $1,054,239,744.20 16 $1,806,256,837.47 4 $1,223,589,591.93 20180630 101 $995,869.66 89 $2,918,059.86 77 $11,712,270.61 65 $19,134,935.74 53 $102,958,926.89 41 $391,850,793.14 29 $998,602,372.05 17 $1,733,912,526.59 5 $1,522,706,322.33 20180731 102 $976,380.12 90 $2,809,873.99 78 $10,999,339.10 66 $16,477,201.90 54 $92,510,773.58 42 $361,505,405.60 30 $944,366,801.25 18 $1,662,572,330.00 6 $1,746,624,391.43 20180831 103 $933,923.19 91 $2,703,576.95 79 $10,435,446.20 67 $14,039,763.64 55 $82,715,618.33 43 $332,336,877.94 31 $890,028,967.30 19 $1,592,052,716.74 7 $1,947,848,239.84 20180930         80   68   56   44   32   20   8   20181031         81   69   57   45   33   21   9   20181130         82   70   58   46   34   22   10   20181231         83   71   59   47   35   23   11   20190131         84   72   60   48   36   24   12   20190228         85   73   61   49   37   25   13   20190331         86   74   62   50   38   26   14   20190430         87   75   63   51   39   27   15   20190531         88   76   64   52   40   28   16   20190630         89   77   65   53   41   29   17   20190731         90   78   66   54   42   30   18   20190831         91   79   67   55   43   31   19   20190930         92   80   68   56   44   32   20   20191031         93   81   69   57   45   33   21   20191130         94   82   70   58   46   34   22   20191231         95 ???? 83 ???? 71 ???? 59 ???? 47 ???? 35 ???? 23 ???? ... View more

I am trying to model counts data to rank order risk of accounts going bad by grade and band. data looks like this: Grade band year bad rate Total Accs Bad Accs A A-(0-45%) 2016 0.31% 3924 12 A A-(0-45%) 2013 0.20% 51556 103 A A-(0-45%) 2014 0.24% 49918 120 A A-(0-45%) 2015 0.25% 59723 150 A B-(>45-55%) 2016 0.80% 249 2 A B-(>45-55%) 2015 0.22% 3664 8 A B-(>45-55%) 2013 0.32% 3149 10   when I summarize the above data, I see below observed bad rate by grade*band:   Grade band bad rate Total Accs Bad Accs A A-(0-45%) 0.23% 165121 385 B A-(0-45%) 0.68% 250156 1708 C A-(0-45%) 1.92% 240478 4609 D A-(0-45%) 3.05% 33809 1030 E A-(0-45%) 3.89% 2853 111 F A-(0-45%) 1.52% 7417 113 G A-(0-45%) 3.30% 3026 100   I have used proc genmod with poisson distribution to model the above data to compare if it rank ordering according to the observed results.   proc genmod data=data; class grade band; model bad_Accs = Grade bands grade*DTI/ dist=poisson link=log; run;   I see the below results:   Analysis of Maximum likelihood paramter estimates parameter     Estimate Grade*band G A-(0-45%) 0 Grade*band D A-(0-45%) 0.5404 Grade*band A A-(0-45%) 0.9426 Grade*band B A-(0-45%) 1.0461 Grade*band C A-(0-45%) 1.2279 Grade*band E A-(0-45%) 17.6989 Grade*band F A-(0-45%) 17.7168   from the results it suggests that a F grade with A-(0-45%) is 17.71 % more likely to go bad compared to other grades?   but from the observed results, I see bad rate is high for grade E, should'nt grade E have higher parameter estimate in genmod ?   or am I modeling wrong vraiable? I feel like I should model for Total Accs/Bad Accs instead of just Bad Accs to consider severity.   when I try to do that as below, its givng me an error:   proc genmod data=DTI; class grade DTI; model (Accs/bad_Accs)*100 = Grade DTI grade*DTI/ dist=poisson link=log; run;   19 model (Accs/bad_Accs)*100 = Grade DTI grade*DTI/ dist=poisson link=log;                         _                        22                        76 ERROR 22-322: Syntax error, expecting one of the following: a name, ','. ERROR 76-322: Syntax error, statement will be ignored.   Any suggestion on how to model for bad accs including severity as well in the model?   ... View more