topic Re: Searching and Counting for Multiple Substrings Across an Array in SAS Programming

Searching and Counting for Multiple Substrings Across an Array

A_SAS_Man — Fri, 31 Jan 2020 21:32:33 GMT

I am attempting to count the instances that an array of columns has of specific letters (C,D,K). For example I have the following:

data have;
input dx1 $ dx2 $ dx3 $;
datalines;
d12 l34 r23
t67 u12 p90
k13 c56 _
;
run;

I want to generate the following off of it:

data want;
input dx1 $ dx2 $ dx3 $ count;
datalines;
d12 l34 r23 1
t67 u12 p90 0
k13 c56 _   2
;
quit;

As a corollary off of this I am also interested in how to take the substring of each element of the array to get the following:

data want2;
input dx1 $ dx2 $ dx3 $ adx1 $ adx2 $ adx3 $;
datalines;
d12 l34 r23 d l r
t67 u12 p90 t u p
k13 c56 _   k c 
;
quit;

Re: Searching and Counting for Multiple Substrings Across an Array

novinosrin — Fri, 31 Jan 2020 21:39:17 GMT


data have;
input dx1 $ dx2 $ dx3 $;
datalines;
d12 l34 r23
t67 u12 p90
k13 c56 _
;
run;

data want;
 set have;
 array d dx1-dx3;
 array e $ adx1  adx2  adx3 ;
 count=countc(catx(' ',of d(*)),'cdk');
 do over d;
  e=char(d,1);
 end;
run;

Re: Searching and Counting for Multiple Substrings Across an Array

ballardw — Fri, 31 Jan 2020 21:42:50 GMT

What if the same letter repeats in different variables? Count as 1 only or number of occurrences?

Is case a consideration? I.E. you have values of K12 and k12. Only count the lower or upper case or count both?

Example:

K12 C56 K43 is that a 2 or 3 result?

Re: Searching and Counting for Multiple Substrings Across an Array

A_SAS_Man — Fri, 31 Jan 2020 21:47:48 GMT

I would like to count number of a occurrences (so 3 in your example). If I could make it count both upper and lower cases that would be ideal, I believe the solution above will only count ones that match case.

Re: Searching and Counting for Multiple Substrings Across an Array

novinosrin — Fri, 31 Jan 2020 21:51:19 GMT

Hi @A_SAS_Man My apologies, you could add a modifier in the COUNTC function to perform a case insensitive find/count.

For example, the change would be

 count=countc(catx(' ',of d(*)),'cdk','i');

Re: Searching and Counting for Multiple Substrings Across an Array

A_SAS_Man — Fri, 31 Jan 2020 21:52:50 GMT

No apology necessary! I hadn't even thought of that possibility until @ballardw brought it up. Appreciate the modification suggestion.

Re: Searching and Counting for Multiple Substrings Across an Array

ballardw — Fri, 31 Jan 2020 22:10:52 GMT

@A_SAS_Man wrote:

No apology necessary! I hadn't even thought of that possibility until @ballardw brought it up. Appreciate the modification suggestion.

Now for the fun values:

Is it possible to have a variable with a value like: KC1?

You may want to check your data looking for unique values before answering. You might have some actual values that are not supposed to be in the data (human entry errors for example).

Or if you have valid values like KC1 is that a 1 count or a 2 count?