DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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
Trusted Advisor
Posts: 1,115

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

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

 

 

 

View solution in original post


All Replies
PROC Star
Posts: 1,561

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
Trusted Advisor
Posts: 1,115

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

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

 

 

 

PROC Star
Posts: 1,561

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! Smiley Happy

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: 1,561

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.
Trusted Advisor
Posts: 1,115

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: 1,561

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

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

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~');

Trusted Advisor
Posts: 1,204

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;

Trusted Advisor
Posts: 1,115

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: 9,681

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 .

Trusted Advisor
Posts: 1,115

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?

@ChrisNZ@Ksharp@stat_sas@FreelanceReinhard 

Thank you all for helping solve the problem!Smiley Happy

PROC Star
Posts: 1,561

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 481 views
  • 5 likes
  • 5 in conversation