Replace values in a column

[Moksha]

Hi All,

  I have the following data:

Id       x     y

101     a      1

101     b      2

101    c1     3

101    c2    4

101   c2     5

101   c2     6

101   c2     7

101   c2     8

101   c2     9

102   a       1

102    b      2

102    c1     3

102    c2    4

102    c2    5

102    c2    6

102    c2    7

102    c2    8

102    c2    9

103    a      1

103    b      2

103    d1    3

103    d2   4

103    d2   5

103    d2   6

103    d2   7

103    d2   8

103    d2   9

and I want the output as below:

Id       x     y

101     a      1

101     b      2

101    c1     3

101    c2    4

101   c3    5

101   c4     6

101   c5     7

101   c6     8

101   c7     9

102   a       1

102    b      2

102    c1     3

102    c2    4

102    c3    5

102    c4    6

102    c5    7

102    c6    8

102    c7    9

103    a      1

103    b      2

103    d1    3

103    d2   4

103    d3   5

103    d4   6

103    d5   7

103    d6   8

103    d7   9

Can anyone help?

Thanks

[PaigeMiller]

Repeating what I said in your previous thread:

I really really really want you to tell me what the pattern is and what the logic is; instead of me trying to figure it out (and possibly getting it wrong). And you have not said what determines where these extra rows will be added.

Please tell us what you know, that is the best way to get the help you need. Please don't make us guess, please don't make us try to figure it out, we could possibly get it wrong and that is not a good way to get the help you need. (And this applies not only to this question, but to all questions you ask in the future, please tell us what you know, don't make us guess, and don't make us ask for the logic or details to be explained).

Why do I need to repeat this, @Moksha ? Why don't you explain without us having to ask repeatedly?

[A_Kh · Accepted solution]

data have;
set have;
by id y x;
z= substr(x, 1,1);
run; 
data want;
set have;
by id z;
if not (first.z and last.z) then do;
 if first.z then seq=1;
 else seq+1;
 x= cats(z,seq);
end;
drop z seq;
proc print;run; 

[Moksha]

Thank you very much, it's working and it helped me a lot. Once again, thank you very much.

[ballardw]

Here is why we need RULES not example.

If that example data is not your complete data set I have to assume somewhere you have values like below only XX is something other than your a, b, cx (or dx ). Without some rule I have no idea as you have only provided examples for C and D.  Maybe M values need to be treated some other way. Or what to do if there are only 3 sets of XX values instead of 7 or .... (I can think of lots of garbage). 

133    a    1
133    b    2
133    xx   3
133    xx   4
133    xx   5
133    xx   6
133    xx   7
133    xx   8
133    xx   9

[Oluwaseun1_Eyeb]

i will like to link up with you atleast to become a better proficient sas programmer, kindly message me 

[mkeintz]

What is the rule you intend to apply?

It appears that whenever the first letter of X is the same in two successive observations for the same ID, you want to append a sequence number to that character. 

If you want good answers, then help us help you by describing the rule.  Also please put your sample data in the form of working data steps.  

[Oluwaseun1_Eyeb]

Hello