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;
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;
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;
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?
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.
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');
No apology necessary! I hadn't even thought of that possibility until @ballardw brought it up. Appreciate the modification suggestion.
@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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.