topic Re: Identify rows with repeat character strings in New SAS User

Identify rows with repeat character strings

abaker_ca — Fri, 11 Nov 2022 04:48:32 GMT

I have a simple problem that I can solve in R easily but just can't figure out how to solve in SAS.

I have a data set where there are numerous diagnostic results concatenated into one long string for each target being identified by the assay.

I simply want to identify subjects that are positive for more than one target.

Here is an example data set.

data test;
input subject_id $1-8
result $11 - 33;
datalines;
ABCD0001 A POS B POS C NEG D NEG
ABCD0002 A POS B NEG C NEG D NEG
ABCD0003 A NEG B NEG C NEG D NEG
ABCD0004 A POS B NEG C POS D NEG
;
run;

Ideally, I'd write code that would return this data set with a flag for subjects ABCD0001 and ABCD0004 because they are positive for more than one target in the 'result' variable.

Thanks for anyone's help!

Re: Identify rows with repeat character strings

FreelanceReinh — Fri, 11 Nov 2022 09:51:05 GMT

Hello @abaker_ca and welcome to the SAS Support Communities!

You can use the COUNT function to count the number of "POS" substrings in variable RESULT:

data want;
set test;
flag=count(result,'POS','i')>1;
run;

The optional 'i' modifier makes the search case-insensitive so that "pos", "Pos", etc. would be counted as well. The numeric variable FLAG will contain the value 1 or 0 if the inequality count(...)>1 is true or false, respectively.

Re: Identify rows with repeat character strings

Ksharp — Fri, 11 Nov 2022 11:45:14 GMT

To avoid this kind of scenario :

ABCD0001 APOSA POS BNEGB POS C NEG D NEG

You could try this one :

data test;
input subject_id $ result $20.;
datalines;
ABCD0001 A POS B POS C NEG D NEG
ABCD0002 A POS B NEG C NEG D NEG
ABCD0003 A NEG B NEG C NEG D NEG
ABCD0004 A POS B NEG C POS D NEG
;
run;
data want;
 set test; 
 if findw(result,'POS',' ') ne findw(result,'POS',' ','b') then flag='Y';
run;

Re: Identify rows with repeat character strings

Astounding — Fri, 11 Nov 2022 12:04:31 GMT

Could the actual target names include the letters "POS"? For example, could any of these words appear in a target name?

POST
OPPOSITE
POSE
POSTERIOR
SUPPOSE

Re: Identify rows with repeat character strings

abaker_ca — Fri, 11 Nov 2022 16:58:21 GMT

It's unlikely they would. For the most part the string in this variable would be a disease target followed by the pos/neg call for the target (e.g. "Flu A POS").

Re: Identify rows with repeat character strings

Tom — Fri, 11 Nov 2022 20:30:35 GMT

Count the occurrences of ' POS '. You might need to append a space to the string if there is a value that completely fills the variable.

data want;
  set test;
  n_positive = count(result||' ',' POS ');
run;

Note that COUNT() has an optional third argument you can use to make the string comparisons case insensitive.

  n_positive = count(result||' ',' POS ','i');