topic Re: LOOPING OVER STRING VALUES IN DATASET in New SAS User

LOOPING OVER STRING VALUES IN DATASET

dm2018 — Tue, 19 Mar 2019 01:14:49 GMT

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.

Re: LOOPING OVER STRING VALUES IN DATASET

Tom — Tue, 19 Mar 2019 01:37:25 GMT

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?

Re: LOOPING OVER STRING VALUES IN DATASET

dm2018 — Tue, 19 Mar 2019 01:40:07 GMT

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.

Re: LOOPING OVER STRING VALUES IN DATASET

Tom — Tue, 19 Mar 2019 01:48:18 GMT

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.

Re: LOOPING OVER STRING VALUES IN DATASET

dm2018 — Tue, 19 Mar 2019 01:58:12 GMT

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! 🙂

Re: LOOPING OVER STRING VALUES IN DATASET

dm2018 — Tue, 19 Mar 2019 02:02:05 GMT

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.

Re: LOOPING OVER STRING VALUES IN DATASET

Reeza — Tue, 19 Mar 2019 02:26:08 GMT

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.

Re: LOOPING OVER STRING VALUES IN DATASET

ballardw — Tue, 19 Mar 2019 15:06:28 GMT

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.