Quartz | Level 8

Replace values in a column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Lapis Lazuli | Level 10

Re: Replace values in a column

``````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;
``````
7 REPLIES 7
Diamond | Level 26

Re: Replace values in a column

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?

--
Paige Miller
Lapis Lazuli | Level 10

Re: Replace values in a column

``````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;
``````
Quartz | Level 8

Re: Replace values in a column

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

Super User

Re: Replace values in a column

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```

Calcite | Level 5

Re: Replace values in a column

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

Re: Replace values in a column

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Calcite | Level 5

Re: Replace values in a column

Hello

Discussion stats
• 7 replies
• 479 views
• 2 likes
• 6 in conversation