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;
You have to list the 15 variables if they are not numbered consecutively:
array bal(15) value_1611 value_1612 value_1801 etc ;
Thank you, ChrisNZ! problem solved, can't believe it's that simple, lol
You can use what is called a name range list (see documentation😞
value_1611--value_1801
(assuming that this reflects the order of the variables in dataset Book1). This would even cope with a mistyped name like your "vale_1612".
It's not clear from what you've shown that this IF condition will work:
input(scan(name1,2,'_'),4.)=open_month
It would require that variable Open_month contains numeric values like 1708 rather than SAS date values or character values like 'Aug-17'.
Also, I'm wondering if you need this wide dataset only for reporting purposes because otherwise a long format would be much more convenient (e.g. when it comes to updating the table with months 1802, 1803 etc.).
Thank you so much, FreelanceReinhard!
value_1611--value_1801 worked! Just one more hyphen, it's that simple!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.