Using array to split a concatanated string

[gsnidow]

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

[LinusH]

Your issuing the output statement prior to the assignment of Code and Month....?

[Haikuo · Accepted solution]

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

[gsnidow]

DOH!  Always something simple.  Thank you so much for the help.

[Haikuo]

Yep! Happens to me all the time:smileylaugh: