topic Re: Concatenate multiple columns using catx and find position of a substring using findc in SAS Programming

Concatenate multiple columns using catx and find position of a substring using findc

ajatshatru — Wed, 21 Feb 2024 15:50:54 GMT

data your_dataset;
status = 'D';
dec01 = 'P101';
dec02 = 'P111';
dec03 = 'P102';
dec04 = '';
dec05 = '';
dec06 = '';

dec07 = '';
dec08 = '';
dec09 = '';
dec10 = '';

run;

DATA your_dataset;
SET YOUR_DATASET;
DEF = catx(' ', of dec01-dec10) ;
DEF2 = FINDC(catx(' ', of dec01-dec10),'D111');

RUN;

Ideally this should set value DEF2 to 0 as there is no D111 in the concatenated DEF. But SAS returns value of DEF2 as 2

Re: Concatenate multiple columns using catx and find position of a substring using findc

mkeintz — Wed, 21 Feb 2024 16:20:53 GMT

Why are you using FINDC, which is defined as (emphasis mine)

Searches a string for any character in a list of characters ?

So in your case, you are effectively searching "D" or "1" (or "1" again and again).

I think you want FINDW (find a word). Try


DEF3 = FINDW(catx(' ', of dec01-dec10),'D111');

Edited addition:

@Reeza's response made me realize I should have added the following to this note. The FINDW above returns the character position of the search string. If, instead you actually want the word position, you need to add a 3rd and 4th parameter to the FINDW, as in

DEF3 = FINDW(catx(' ', of dec01-dec10),'P102',' ','e');

The 'e' 4th parameter says "count words instead of characters ...". The 3rd parameter consequently becomes necessary to specify acceptable words boundaries.

When the string is not found both versions of FINDW will return a zero. But when it is found, you will see a difference. And remember if some of the left-hand words are blank, those consecutive blanks will be seen as a single word separator.

Re: Concatenate multiple columns using catx and find position of a substring using findc

Reeza — Wed, 21 Feb 2024 16:00:18 GMT

FINDC() looks up characters not words/substrings.

From the documentation:

Searches a string for any character in a list of characters

I think you want WHICHC() instead. FIND will return the character location not the index of the terms, so I think this is likely what you're after.

DEF2 = whichc('D111', of dec01-dec10);

@ajatshatru wrote:

data your_dataset;
status = 'D';
dec01 = 'P101';
dec02 = 'P111';
dec03 = 'P102';
dec04 = '';
dec05 = '';
dec06 = '';

dec07 = '';
dec08 = '';
dec09 = '';
dec10 = '';

run;

DATA your_dataset;
SET YOUR_DATASET;
DEF = catx(' ', of dec01-dec10) ;
DEF2 = FINDC(catx(' ', of dec01-dec10),'D111');

RUN;

Ideally this should set value DEF2 to 0 as there is no D111 in the concatenated DEF. But SAS returns value of DEF2 as 2

Re: Concatenate multiple columns using catx and find position of a substring using findc

ajatshatru — Wed, 21 Feb 2024 16:20:30 GMT

Thank you Reeza it will also work