Input data file have month-end value for each account, I need to covert to tenureship value, meaning value as of month 1, month 2, ... month n, with month 1 starting from the open_month: Here is the input file "Book1": account_id Open_month value_1611 vale_1612 value_1701 value_1702 value_1703 value_1704 value_1705 value_1706 value_1707 value_1708 value_1709 value_1710 value_1711 value_1712 value_1801 10034 Aug-17 0 0 0 0 0 0 0 0 0 115 2250 2383 3153 3377 3488 11005 May-17 0 0 0 0 0 0 551 550 556 548 558 567 582 604 622 11008 Feb-17 0 0 0 5344 5327 5295 5293 5017 4938 4940 4917 4101 4047 2299 2302 32057 Nov-16 1080 1071 1064 1055 1047 1038 1030 1022 0 0 0 37 37 38 38 32066 Oct-17 0 0 0 0 0 0 0 0 0 0 0 49 7765 8100 8391 99971 Mar-17 0 0 0 0 302 230 179 3084 3252 2391 1988 1566 1639 2254 2594 Desired output: account_id Open_month value_month0 value_month1 value_month2 value_month3 value_month4 value_month5 value_month6 value_month7 value_month8 value_month9 value_month10 value_month11 value_month12 value_month13 value_month14 10034 Aug-17 115 2250 2383 3153 3377 3488 - - - - - - - - - 11005 May-17 551 550 556 548 558 567 582 604 622 0 0 0 0 0 0 11008 Feb-17 5344 5327 5295 5293 5017 4938 4940 4917 4101 4047 2299 2302 0 0 0 32057 Nov-16 1080 1071 1064 1055 1047 1038 1030 1022 0 0 0 37 37 38 38 32066 Oct-17 49 7765 8100 8391 0 0 0 0 0 0 0 0 0 0 0 99971 Mar-17 302 230 179 3084 3252 2391 1988 1566 1639 2254 2594 0 0 0 0 This is my code, but returned error (it worked if the date sequence is consecutive, like 1701-1715, but no longer works when I have 1611, 1612, 1801 in the mix with 1701-1712), the error returned says: ERROR: Too many variables defined for the dimension(s) specified for the array bal. data transform_bal; set book1; array bal(15) value_1611-value_1801; array val(15) value_month0-value_month14; length name1 $ 32; n=0; do i=1 to 15; call vname(bal(i),name1); if input(scan(name1,2,'_'),4.)=open_month then flag=1; if flag=1 then do; n+1; val(n)=bal(i); end; end; drop value_1611-value_1801 name1 i n flag; run;
... View more