topic Re: Count of specific value across row in SAS Programming

Count of specific value across row

mt88 — Tue, 01 Oct 2024 13:49:02 GMT

Hello,

I'd have a dataset where I'd like to create a new variable that gives me a count of a specific value for a set of variables across a row of data. For example, in this test dataset I want to create a new variable that that gives me a count of '0' values across variables a, b, c, and d.

data test;
input a b c d;
cards;
1 0 9 1
1 1 0 0
0 9 1 1
0 0 9 1
1 0 9 9
0 1 1 0
1 9 9 1
1 9 0 0
0 0 9 1
9 1 0 0;
run;

The new variable (newvar) would return the following:

newvar

Any help is appreciated. I can provide more information if that is helpful.

Thanks, Matt

Re: Count of specific value across row

ballardw — Tue, 01 Oct 2024 14:31:59 GMT

Your data implies all of the values are one digit, is that in fact the case? I ask because there is a moderately slick solution if it is that may not work if you have some values that are 2 or more digits or non-integer.

The CATS function creates a string with no spaces between converted numbers into a single long string and uses the Count function to determine how many 0 characters appear.

data test;
input a b c d;
Numzero = count(cats(a,b,c,d),'0');
cards;
1 0 9 1
1 1 0 0
0 9 1 1
0 0 9 1
1 0 9 9
0 1 1 0
1 9 9 1
1 9 0 0
0 0 9 1
9 1 0 0
;
run;

Note: Datalines end on the line with a semicolon or ;;;; if using datalines4; As such you do not want the semicolon on a line with data as it gets ignored.

And here is an example where the result is incorrect for two-digit value

data test;
input a b c d;
Numzero = count(cats(a,b,c,d),'11');
cards;
1 0 9 1
1 1 0 0
0 9 11 1
0 0 9 1
;
run;

The incorrect result for line 1 is because the CATS result for the line looks like "1100" and so there are two 1's next to each other and the Count function treats them as such.

If your data is more complex (and possibly contains many more variables or search for more values):

data test;
   input a b c d;
   array v(*) a b c d;
   do i=1 to dim(v);
      numzero = sum(numzero,(v[i]=0));
      numone  = sum(numzero,(v[i]=1));
      numnine = sum(numzero,(v[i]=9));
   end;
cards;
1 0 9 1
1 1 0 0
0 9 1 1
0 0 9 1
1 0 9 9
0 1 1 0
1 9 9 1
1 9 0 0
0 0 9 1
9 1 0 0
;
run;

Re: Count of specific value across row

mkeintz — Wed, 02 Oct 2024 03:47:31 GMT

Let's generalize a bit. Say you have integer values that range from -3 0 to 15, but you only want counts of 0's, 4's, 8's, and 14's. Then you could

data test;
input a b c d;
cards;
1 0 9 1
1 1 0 0
0 9 1 1
0 0 9 1
1 0 9 9
0 1 1 0
1 9 9 1
1 9 0 0
0 0 9 1
9 1 0 0
5 6 14 15
8 12 4  0
run;

data want (drop=_:);
  set test;
  array values a -- d;
  array counts {0:15}  count_0  _dum1 - _dum3
                       count_4  _dum5 - _dum7 
                       count_8  _dum9 - _dum13
                       count_14 _dum15 ;

  do _v=1 to dim(values);
    counts{values{_v}}=sum(counts{values{_v}},1);
  end;

  /*convert missing counts to zeroes */
  array cnts count_: ;    /* Was mistakenly array cnts  n_: ; */
  do over cnts;
    cnts=coalesce(cnts,0);
  end;

run;

Re: Count of specific value across row

Tom — Tue, 01 Oct 2024 18:15:07 GMT

A simple way to count is use the fact that SAS will evaluate boolean expressions to 1 (TRUE) or 0 (FALSE) so that you can just add up the results to find the number of TRUE values.

data test;
  input a b c d expect;
cards;
1 0 9 1  1
1 1 0 0  2
0 9 1 1  1
0 0 9 1  2
1 0 9 9  1
0 1 1 0  2
1 9 9 1  0
1 9 0 0  2
0 0 9 1  2
9 1 0 0  2
;

data want;
  set test;
  array x a b c d ;
  want=0;
  do over x;
    want+(x=0);
  end;
run;

Result

Obs    a    b    c    d    expect    want

  1    1    0    9    1       1        1
  2    1    1    0    0       2        2
  3    0    9    1    1       1        1
  4    0    0    9    1       2        2
  5    1    0    9    9       1        1
  6    0    1    1    0       2        2
  7    1    9    9    1       0        0
  8    1    9    0    0       2        2
  9    0    0    9    1       2        2
 10    9    1    0    0       2        2

Re: Count of specific value across row

FreelanceReinh — Tue, 01 Oct 2024 18:30:07 GMT

Hello @mt88,

@ballardw wrote:

And here is an example where the result is incorrect for two-digit value
data test;
input a b c d;
Numzero = count(cats(a,b,c,d),'11');
cards;
1 0 9 1
1 1 0 0
0 9 11 1
0 0 9 1
;
run;
The incorrect result for line 1 is because the CATS result for the line looks like "1100" and so there are two 1's next to each other and the Count function treats them as such.

This problem can be avoided by introducing a delimiter:

data want;
set test;
newvar=count(catx('||',.,of a--d,.),'|0|');
run;

(where 0 could be replaced by 11 or any other number whose character representation is to be counted -- or even by a non-numeric character string).