topic Re: How to count the number of same value in a row with sas like the countif function in excel does? in SAS Programming

How to count the number of same value in a row with sas like the countif function in excel does?

Dancer_on_data — Tue, 12 Apr 2016 20:37:21 GMT

Hi everyone, is there a sas function that is similiar to the countif in excel that allows you to count of number of same value in a row or in a column. To be specific, here is an example below:

Suppose, I have a datatable called test that shows the following data below:

ID A1 A2 A3 A4 A5 A6

1 cc cn cc cn cn ce

2 ca cc cc ce ca cc

3 cc ca cn ce cc cc

My question is " How can I count the number of "cc" in each observation in sas? Thanks!

Best,

Re: How to count the number of same value in a row with sas like the countif function in excel does?

ChrisNZ — Tue, 12 Apr 2016 21:20:15 GMT

No countif function in SAS, but you can make your own logic.

For example:


data HAVE;
input (ID  A1  A2  A3  A4  A5  A6 ) (: $);
cards;
1    cc  cn   cc  cn   cn   ce
2    ca  cc   cc   ce  ca   cc
3    cc  ca   cn   ce  cc   cc
run;
data WANT;
  set HAVE;
  NB_CC=lengthn(compress(tranwrd(catx(' ',of A1-A6),'cc','~'),'~','k'));
  putlog NB_CC=;
run;

NB_CC=2

NB_CC=3

Re: How to count the number of same value in a row with sas like the countif function in excel does?

FreelanceReinh — Tue, 12 Apr 2016 23:30:55 GMT

Hi @Dancer_on_data,

Here's another suggestion:

NB_CC=count(catx(' ',of A:),'cc');

Like @ChrisNZ's approach, this would count 'cc' also in substrings of an Ai (i=1, ..., 6), though (e.g. in 'accord'). To avoid this, one could modify the definition to something like

NB_CC=count(catx('~~','x',of A:,'x'),'~cc~');

with a character, '~', chosen such that it does not occur in any of the Ai, and an arbitrary non-blank character 'x'.

Since the CATX function removes leading blanks, ' cc' would still be counted as 'cc'.

Re: How to count the number of same value in a row with sas like the countif function in excel does?

stat_sas — Tue, 12 Apr 2016 23:41:08 GMT

Another way using combination of count and cats functions:

data want;
set have;
count_cc=count(cats(of A1-A6),'cc');
run;

Re: How to count the number of same value in a row with sas like the countif function in excel does?

ChrisNZ — Wed, 13 Apr 2016 00:30:20 GMT

@FreelanceReinh You are right! How come? I protest! 🙂

The tranwrd function was supposed to take care of the flaw you mention as it is supposed to look for words. Is this expected behaviour?

The online doc:

TRANSTRN Function

Replaces or removes all occurrences of a substring in a character string.

TRANWRD Function

Replaces or removes all occurrences of a word in a character string.

One says word and the other one reads substring, but they do the same thing? Really?

Is there no difference between the tranwrd and transtrn functions except when the replacement string is empty? If so, talk about a misnomer! And about bad documentation!

Re: How to count the number of same value in a row with sas like the countif function in excel does?

Ksharp — Wed, 13 Apr 2016 01:05:33 GMT

countw() would be better.

Or you can use ARRAY which can avoid the error when there are some delimiter in variable value .

Re: How to count the number of same value in a row with sas like the countif function in excel does?

ChrisNZ — Wed, 13 Apr 2016 02:03:35 GMT

Also from the SAS documentation:

Definition of "Word"

In the COUNTW function, "word" refers to a substring that has one of the following characteristics:

is bounded on the left by a delimiter or the beginning of the string
is bounded on the right by a delimiter or the end of the string
contains no delimiters, except if you use the Q modifier and the delimiters are within substrings that have quotation marks

Note: The definition of "word" is the same in both the SCAN function and the COUNTW function.

Delimiter refers to any of several characters that you can specify to separate words.

Re: How to count the number of same value in a row with sas like the countif function in excel does?

FreelanceReinh — Wed, 13 Apr 2016 09:02:34 GMT

@stat_sas wrote:

Another way using combination of count and cats functions:

@stat_sas: Chris and I used CATX because it's important to keep strings like 'ac' and 'ce' apart to prevent the formation of new occurrences of 'cc' just by concatenation ('acce').

Re: How to count the number of same value in a row with sas like the countif function in excel does?

FreelanceReinh — Wed, 13 Apr 2016 10:08:49 GMT

Hi @ChrisNZ,

I agree that the wording of the TRANWRD documentation and in fact the function name itself are a bit misleading, especially from today's perspective. This has probably historical reasons. When I "grew up" (with SAS 6.12), the name TRANWRD for a function replacing substrings seemed fairly natural to distinguish it from TRANSLATE (replacing single characters), although the SCAN function documentation used the term "word" in a narrower sense.

With the advent of more word-oriented functions (in the narrower sense) in SAS 9, esp. SAS 9.2, like COUNTW, FINDW and INDEXW, it has become apparent that the naming of TRANWRD was not ideal ("TRANSTR" would have been more to the point). That's how I assume the new function TRANSTRN (SAS 9.2) got its name. But TRANWRD remains for backward compatibility and its close relationship with TRANSTRN (like SUBSTR/SUBSTRN) is obscured by the difference in the names.

Re: How to count the number of same value in a row with sas like the countif function in excel does?

FreelanceReinh — Wed, 13 Apr 2016 10:34:45 GMT

@Ksharp wrote:

countw() would be better.

Or you can use ARRAY which can avoid the error when there are some delimiter in variable value .

A function which counts the occurrences of a specified word-like substring would be helpful indeed (with the limitation you mention). But "COUNTW" is already the name of the function which returns the number of all words in a string.

Re: How to count the number of same value in a row with sas like the countif function in excel does?

Dancer_on_data — Wed, 13 Apr 2016 13:26:09 GMT

Thanks @FreelanceReinh, I like count(catx('~~','x',of A:,'x'),'~cc~');

Re: How to count the number of same value in a row with sas like the countif function in excel does?

Dancer_on_data — Wed, 13 Apr 2016 13:46:10 GMT

@ChrisNZ@Ksharp@stat_sas@FreelanceReinh

Thank you all for helping solve the problem!

Re: How to count the number of same value in a row with sas like the countif function in excel does?

ChrisNZ — Wed, 13 Apr 2016 21:48:01 GMT

FreelanceReinhard provided the working solution, so you can mark the answer as the solution.
I've opened an incident with tech support to sort the tranwrd() issue out. I don't remember that it worked that way (but then my memory...) and if it does the documentation needs fixing.

Re: How to count the number of same value in a row with sas like the countif function in excel does?

ChrisNZ — Thu, 14 Apr 2016 02:34:08 GMT

@FreelanceReinh Thank you for your reply. I've asked SAS to update the documentation.

I'll also suggest them that they should create a TRANSTR function keyword and keep TRANWRD as a legacy alias.