topic Re: Susbstrn of mixed characters and numbers in SAS Programming

Susbstrn of mixed characters and numbers

lillymaginta — Fri, 21 Sep 2018 14:06:20 GMT

data have;
input $ dx1 dx2 dx3;
cards;
ft123 rg12 st12
st1243 rt1 rt2
st1gt dr1 123; 
Run;

I have the following data where dx variables are characters but include both numbers/letters. I am trying to use the following substrn statement to retain dx's starting with 'st1' but getting zero results back.

Here is my code:

data want(drop=i);set have;; array x{*} dx:; st=1=0;

do i=1 to dim(x); if SUBSTRN(x{i},1,3)

in ('st1' )

then do;st=1;

leave;

end;

run;

Re: Susbstrn of mixed characters and numbers

RW9 — Fri, 21 Sep 2018 14:13:36 GMT

An array can only reference data of one type, either character or numeric, but not both. All your data there is character, as at least on cell contains character data, so can't be numeric.

Re: Susbstrn of mixed characters and numbers

RW9 — Fri, 21 Sep 2018 14:13:37 GMT

An array can only reference data of one type, either character or numeric, but not both. All your data there is character, as at least on cell contains character data, so can't be numeric.

Re: Susbstrn of mixed characters and numbers

lillymaginta — Fri, 21 Sep 2018 14:16:46 GMT

Thank you RW9 for the quick response. All of the variables are coded as a character. However, the above code would not retain the observations needed. Would it be possible to provide an alternative coding?

Thank you

Re: Susbstrn of mixed characters and numbers

ballardw — Fri, 21 Sep 2018 14:45:30 GMT

You should show what the desired output would be from the given input, best is also as data step. Providing code that does not perform does not really provide a good description of the actual desired output.

Re: Susbstrn of mixed characters and numbers

lillymaginta — Fri, 21 Sep 2018 15:05:50 GMT

data have;
input $ id dx1 dx2 dx3;
cards;
1  ft123   rg12   st12
2  st1243  rt1    rt2
3  st1gt   dr1    123
4  rt1     rt1    rt1
5  gt1     gt1    gt1; 
Run; 

Retain observations that have 'st1':

Output 
1  ft123   rg12   st12
2  st1243  rt1    rt2
3  st1gt   dr1    123

Re: Susbstrn of mixed characters and numbers

ballardw — Fri, 21 Sep 2018 15:40:09 GMT

@lillymaginta wrote:

data have;
input $ id dx1 dx2 dx3;
cards;
1  ft123   rg12   st12
2  st1243  rt1    rt2
3  st1gt   dr1    123
4  rt1     rt1    rt1
5  gt1     gt1    gt1; 
Run; 

Retain observations that have 'st1':

Output 
1  ft123   rg12   st12
2  st1243  rt1    rt2
3  st1gt   dr1    123

First, test any data step code to ensure it works. Yours has several errors.

This looks like one way of what you ask for:

data have;
input  id  dx1 $ dx2 $ dx3 $;
cards;
1  ft123   rg12   st12
2  st1243  rt1    rt2
3  st1gt   dr1    123
4  rt1     rt1    rt1
5  gt1     gt1    gt1
; 
Run; 

data want;
   set have;
   array c _character_;
   do i= 1 to dim(c);
      if index(c[i],'st1') > 0 then do;
         output;
         leave;
      end;
   end;
   drop i;
run;

If there are only certain variables you want to search for the value then place the names of the variables in place of _character_ which will search all character variables.

Second, this is case sensitive. If you also want to match "sT1" "St1" and "ST1" then use

if index(upcase(c[i]),'ST1') > 0 then do;

The LEAVE instruction exits the loop when executed, in effect when the first match is found in this case, so only one row is output in the case of multiple variables containing the search string.

Re: Susbstrn of mixed characters and numbers

Tom — Fri, 21 Sep 2018 15:48:24 GMT

You might want to use one of the CAT...() functions to allow you to search all of the strings in one command.

data want;
  set have;
  if index('^'||catx('^',of dx1-dx3),'^st1');
run;