BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dancer_on_data
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

13 REPLIES 13
ChrisNZ
Tourmaline | Level 20

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

 

 

 

FreelanceReinh
Jade | Level 19

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

 

 

 

ChrisNZ
Tourmaline | Level 20

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

ChrisNZ
Tourmaline | Level 20

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.
FreelanceReinh
Jade | Level 19

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.

ChrisNZ
Tourmaline | Level 20

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

stat_sas
Ammonite | Level 13

Another way using combination of count and cats functions:

 

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

FreelanceReinh
Jade | Level 19

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

Ksharp
Super User

countw()  would be better.

 

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

FreelanceReinh
Jade | Level 19

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

ChrisNZ
Tourmaline | Level 20

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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