Searching and Counting for Multiple Substrings Across an Array

[A_SAS_Man]

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;

[novinosrin · Accepted solution]

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;

[ballardw]

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?

[A_SAS_Man]

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.

[novinosrin]

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');

[A_SAS_Man]

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

[ballardw]

---

@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?