Conditional Count

[PhillipSherlock]

Hello everyone,

I have several variables and I would like to count the number of variables for which each person has a value of 2.

My initial thought (below) was to use 'count' and 'in', but I'm missing something or way off the mark. 

count(in(var1, var2, var3)=2) as var_total

Thank you for your help,

Phil

[ballardw · Accepted solution]

Provide example data.

IN isn't likely to be helpful because that will return a numeric 1 for "yes the value is in the list" or 0 for "no the value is not in the list". So comparing anything as IN (<list>) = 2 always returns 0 as the result of IN is never 2.

A simple COUNT function works with strings and substrings. So you would want to create a string of all the values before counting. HOWEVER if you are searching for "2" it would be found as part of a value of "12" or "21" or anything else that has a 2. So is it possible for 2 to be part of another valid value?

Counttwo = count(cats( of var1-varx),'2');

might work for you if you don't have any values like the "12" mentioned earlier appearing in the values.

This creates a character value converting var1 through varx (use your number if sequentially numbered variables or list them with commas separating the names) to one string and then counts the occurrences of 2.

If you have values of 12 and such that might appear then you will need to examine each one separately.

Something like:

data want;
   set have;
   array s{*}  var1 var2 var3;
   do i= 1 to dim(s);
      Count2 = sum(count2,(s[I]=2));
   end;
run;

Which assumes all of the variables you are searching are numeric. If they are character then use S[i]='2'.

SAS returns 1 for true, so you can use a comparison like s[i]=<value> and accumulate them as shown to get a total count.

If you have your values in mix of numeric and character values then you need to reevaluate why. A combination can be processed but you need two different arrays, one for the character and one for the numeric.

[FreelanceReinh]

Hello @PhillipSherlock,

The risk of finding "2" as a substring of "12" etc. can be avoided by using delimiters (e.g. '#' or whatever does not occur in the variables to be concatenated).

Example:

data have;
input a b $ c;
cards;
2  2 30
3  2 22
5 12  7
2  2  2
;

data want;
set have;
var_total=count(catx('##','|',of a--c,'|'),'#2#');
run;

I've arbitrarily defined b as character to demonstrate that mixed variable types are no problem. The purpose of the added '|' characters is to get the delimiters around the first and last variable. Modify the variable list (a--c) as appropriate.