topic Re: counting occurrences over variables in SAS Programming

counting occurrences over variables

ilikesas — Fri, 20 Jan 2017 04:36:41 GMT

Hi,

suppose I have the following data:

var1	var2	var3	var4	var5	var6
a	a	a	b	b	b
a	b	a	b	a	b
b	b	b	a	a	a

what I would like to obtain is a new data set consisting of the above data set where the value "a" occurs at least 2 times over the variables var1 to var 4.

So here I will end up with 1st row because "a" occurs 3 times within var1 - var4,

and the second row because it occurs 2 times.

Thank you!

Re: counting occurrences over variables

art297 — Fri, 20 Jan 2017 04:58:51 GMT

Here is one way to do it:

data have;
  input (var1-var6) ($);
  cards;
a a a b b b
a b a b a b
b b b a a a
;

data want;
  set have;
  if countc(catt(of var1-var4),'a') ge 2 then output;
run;

HTH,

Art

Re: counting occurrences over variables

Astounding — Fri, 20 Jan 2017 07:45:42 GMT

In real life, is the key string only 1 character long? If so, Art's solution works just fine. If not, you may need to rethink your approach. Another possibility:

data want;

set have;

array vars {6} var1-var6;

totcount = 0;

do _n_=1 to 4;

if vars{_n_}='a' then totcount + 1;

end;

drop totcount;

if totcount >= 2;

run;

Other details might matter. Does capitalization play a role? Do partial words count? For example, if you are searching for "cancer" would "cancerous" count? The more details you can supply, the better the answer you will receive.

Re: counting occurrences over variables

RW9 — Fri, 20 Jan 2017 09:51:17 GMT

A slight change to @art297's great example should cover words also:

data have;
  input (var1-var6) ($);
  cards;
a a a b b b
a b a b a b
b b b a a a
;
run;

data want;
  set have;
  sum_of_a=6 - countw(tranwrd(catx(',',of var:),"a",""),",","blank");
run;

What I do is drop any "a" results, then count what is left and take that off total. If you know the other options then you could go the other way of course.