Greetings all. I'm having trouble figuring out the behavior of an array I'm using. I have data consisting of two fields, 'account' and 'concatenated'. In our DB2 production system, for each customer account, there is a 24 character text field that contains a concatenated string of 1 character codes, ranging from 0 to 9 and A to C and X. Each month our billing system appends a new character to the front of the string, and lops one off the end if the string is already 24 characters long. The codes all have meaning. For example, a '0' indicates the customer paid their monthly bill on time. So, for an account that has paid on time for 24 consecutive months, the concatenated string would be '000000000000000000000000'. What I need to do is to split out each character sequentially, and show three variables, account, code, and month. So, for the previously mentioned example, assuming the account number is 1, the output would be 24 observations, each with account = 1, code = '0', and month=1 through month=24. I got it to work using an array, but the first observation in my output dataset contains an observation where month = '.' The below code is what I am trying, and in dataset 'split', I can't figure out why there is a null month, and why it only goes up to month 23.
data history ;
input account $1. concatenated $24. ;
datalines ;
1AAAAAA0001A000001A0A0A00
201110000110000000000110
300A1A00A000000000AA001A0
411110N11110111111111
5AAAAAA1AA2AAA0A1A1AAAAA1
;
run ; quit ;
data split (keep=account month code) ;
array arCodes[24] $ month1-month24 ;
set history ;
do i = 1 to 24 ;
arCodes(i) = substr(concatenated,i,1) ;
output ;
code = arCodes(i) ;
month = i ;
end ;
run ; quit ;
It works if I use a 25 element array, then do another datastep with 'if month <> . , but I would like to get it in one step, and I am sure it is something small I am missing. I appreciate any and all help. Thank you.
Greg
It is the order of your statements:
data history;
input account $1. concatenated $24.;
datalines;
1AAAAAA0001A000001A0A0A00
201110000110000000000110
300A1A00A000000000AA001A0
411110N11110111111111
5AAAAAA1AA2AAA0A1A1AAAAA1
;
run;
quit;
data split (keep=account month code);
array arCodes[24] $ month1-month24;
set history;
do i = 1 to 24;
arCodes(i) = substr(concatenated,i,1);
code = arCodes(i);
month = i;
output;
/* code = arCodes(i) ;*/
/* month = i ;*/
end;
run;
quit;
Haikuo
Your issuing the output statement prior to the assignment of Code and Month....?
It is the order of your statements:
data history;
input account $1. concatenated $24.;
datalines;
1AAAAAA0001A000001A0A0A00
201110000110000000000110
300A1A00A000000000AA001A0
411110N11110111111111
5AAAAAA1AA2AAA0A1A1AAAAA1
;
run;
quit;
data split (keep=account month code);
array arCodes[24] $ month1-month24;
set history;
do i = 1 to 24;
arCodes(i) = substr(concatenated,i,1);
code = arCodes(i);
month = i;
output;
/* code = arCodes(i) ;*/
/* month = i ;*/
end;
run;
quit;
Haikuo
DOH! Always something simple. Thank you so much for the help.
Yep! Happens to me all the time:smileylaugh:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.