DATA Step, Macro, Functions and more

Using array to split a concatanated string

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 124
Accepted Solution

Using array to split a concatanated string

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


Accepted Solutions
Solution
‎06-13-2014 12:05 PM
Respected Advisor
Posts: 3,156

Re: Using array to split a concatanated string

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

View solution in original post


All Replies
Super User
Posts: 5,441

Re: Using array to split a concatanated string

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

Data never sleeps
Solution
‎06-13-2014 12:05 PM
Respected Advisor
Posts: 3,156

Re: Using array to split a concatanated string

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

Frequent Contributor
Posts: 124

Re: Using array to split a concatanated string

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

Respected Advisor
Posts: 3,156

Re: Using array to split a concatanated string

Yep! Happens to me all the time:smileylaugh:

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1342 views
  • 3 likes
  • 3 in conversation