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

Solved
Occasional Contributor
Posts: 17

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

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,

Accepted Solutions
Solution
‎04-13-2016 11:20 PM
Posts: 1,245

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

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'.

All Replies
PROC Star
Posts: 2,340

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

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

NB_CC=3

Solution
‎04-13-2016 11:20 PM
Posts: 1,245

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

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'.

PROC Star
Posts: 2,340

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

@FreelanceReinhard 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!

PROC Star
Posts: 2,340

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

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.
Posts: 1,245

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

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.

PROC Star
Posts: 2,340

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

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

Occasional Contributor
Posts: 17

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

[ Edited ]

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

Posts: 1,270

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

Another way using combination of count and cats functions:

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

Posts: 1,245

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

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').

Super User
Posts: 10,770

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

countw()  would be better.

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

Posts: 1,245

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

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.

Occasional Contributor
Posts: 17

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

Thank you all for helping solve the problem!

PROC Star
Posts: 2,340