LOOPING OVER STRING VALUES IN DATASET

[dm2018]

Hi,

I'm trying to loop over a string and assign a value  based on whether the value is a number or a character e.g.

data _null_;

length Newcat $ 20;
retain NewCat;
Ref="123ABC";
do i=1 to length(Ref);
val=substr(Ref,i,1);
if val in ("1","2","3","4","5","6","7","8","9") then preCat="0"; else preCat="A";
NewCat=catt(NewCat,preCat);
end;
run;

This correctly solves to NewCat="000AAA". The issue is that I need this to work in a data step on a dataset with a number of rows

e.g.

data t1;
set Work.Data;
do i=1 to length(Ref);
length Newcat $ 20;
val=substr(Ref,i,1);
if val in ("1","2","3","4","5","6","7","8","9") then preCat="0"; else preCat="A";
NewCat=catt(NewCat,preCat);
end;
run;

Any ideas would be massively appreciated. Thanks in advance. D.

[Tom]

Did you try your program?  A data step like that will process every observation. That is how the data step works.

What is it NOT doing that you need?

[dm2018]

Ah, believe it may be the sequencing of the do loop. Values are being assigned to NewCat, but they are not correct as per the Ref on the corresponding row. Will look into this unless you have any ideas? Many thanks for the reply. D.

[Tom]

Post examples of the values that you think are not processed correctly.

If you suspect the DO loop is doing something strange then put some PUT statements in it and check out what it actually happening.

[dm2018]

Thanks. Looks like I need to clear NewCat:

BusinessNumber Newcat                            i val preCat
123ABC               000AAA                           7 C A
ABC123               000AAAAAA000              7 3 0
1A2B3C               000AAAAAA0000A0A0A 7 C A

Not 100% sure on how to do this but thanks, getting closer! 🙂

[dm2018]

Apologies! I've got it, for some reason I'd been retaining NewCat although hadn't show this on the example above. Thanks for your help. Much appreciated. Regards, D.

[Reeza]

You should look up the ANYALPHA, ANYDIGIT functions. You can roll your own of course, but it's much easier to use the pre-written ones unless you have a really customized requirement.

[ballardw]

No loop needed:

data example;
   x='1A2B3C';
   y=translate(x,'000000000AAAAAAAAAAAAAAAAAAAAAAAAAA',
                 '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ');
run;

If your variable may contain lowercase letters then use Upcase(variablename) instead of the X as the first parameter.