topic Replace values in a column in SAS Programming

Replace values in a column

Moksha — Wed, 26 Jul 2023 13:25:27 GMT

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

Re: Replace values in a column

PaigeMiller — Wed, 26 Jul 2023 13:35:25 GMT

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?

Re: Replace values in a column

A_Kh — Wed, 26 Jul 2023 13:54:57 GMT

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;

Re: Replace values in a column

Moksha — Wed, 26 Jul 2023 14:11:49 GMT

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

Re: Replace values in a column

ballardw — Wed, 26 Jul 2023 14:18:53 GMT

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

Re: Replace values in a column

mkeintz — Wed, 26 Jul 2023 14:27:01 GMT

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.

Re: Replace values in a column

Oluwaseun1_Eyeb — Thu, 27 Jul 2023 02:12:54 GMT

Hello

Re: Replace values in a column

Oluwaseun1_Eyeb — Thu, 27 Jul 2023 02:14:53 GMT

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