topic Re: How to realign monthly column value based on length of tenureship in SAS Programming

How to realign monthly column value based on length of tenureship

hcnyl2018 — Mon, 30 Jul 2018 19:50:59 GMT

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;

drop value_1611-value_1801 name1 i n flag;

run;

Re: How to realign monthly column value based on length of tenureship

ChrisNZ — Mon, 30 Jul 2018 22:41:21 GMT

You have to list the 15 variables if they are not numbered consecutively:

array bal(15) value_1611 value_1612 value_1801 etc ;

Re: How to realign monthly column value based on length of tenureship

FreelanceReinh — Tue, 31 Jul 2018 09:09:32 GMT

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

Re: How to realign monthly column value based on length of tenureship

hcnyl2018 — Tue, 31 Jul 2018 11:18:53 GMT

Thank you, ChrisNZ! problem solved, can't believe it's that simple, lol

Re: How to realign monthly column value based on length of tenureship

hcnyl2018 — Tue, 31 Jul 2018 11:25:03 GMT

Thank you so much, FreelanceReinhard!

value_1611--value_1801 worked! Just one more hyphen, it's that simple!