Desktop productivity for business analysts and programmers

How do I use a COUNTIF function within computed column of query builder?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

How do I use a COUNTIF function within computed column of query builder?

Hi guys,

 

I've got a table of data where a certain string of text can appear in a variety of variables.

 

I want to use one computed column (in query builder) that counts how many times the string appears in a range of variables, rather than using multiple computed columns to count the occurrence of the string in each variable separately, as you would with the COUNTIF function over a range of cells in Excel.

 

If it helps, I'm using SAS EG 7.1.

 

Thanks in advance,

Chris


Accepted Solutions
Solution
‎11-10-2017 10:44 AM
Super User
Super User
Posts: 9,796

Re: How do I use a COUNTIF function within computed column of query builder?

Posted in reply to CWilson4815

Use the {i} above post for the code window to preserve formatting.  The colon indicates all variables with the prefix of the text before it, so you can read as "of all variables with decisiononothercoe prefix":

count(catx(',',of DECISIONOTHERCODES:),'RS006 - CONSUMER.PRIMARY')) AS RS006

  That syntax should work - I have none of your data to work with of course, but a similar code does:

proc sql;
  select count(catx(',',make,model,type),"Acura") as tmp
  from sashelp.cars;
quit;

View solution in original post


All Replies
Super User
Super User
Posts: 9,796

Re: How do I use a COUNTIF function within computed column of query builder?

Posted in reply to CWilson4815
Occasional Contributor
Posts: 11

Re: How do I use a COUNTIF function within computed column of query builder?

Thanks for the reply RW9 but how can I use this function with multiple variables being fed into the formula?



For example, if I separate them by commas, then the function doesn't work.



To put it into context, I have 10 columns I would like to reference (DECISIONOTHERCODE1,...,DECISIONOTHERCODE10) and my table name is t1.


Super User
Super User
Posts: 9,796

Re: How do I use a COUNTIF function within computed column of query builder?

Posted in reply to CWilson4815

This is why we always ask for test data, its impossible to guess what you are working with.  You can concatenate the wanted variables together then count across that:

count(catx(',',of decisiononothercode:),'myword') as want

So the catx() takes all variables with prefix decisiononothercode, puts all the strings together with commas separating.  Then the count function scans that string for occurences of myword and returns the number found.

Occasional Contributor
Posts: 11

Re: How do I use a COUNTIF function within computed column of query builder?

I understand the logic behind these functions but I am receiving a syntax error when trying to run this in my query builder as below:

 

(COUNT(CATX(',',of DECISIONOTHERCODESmiley Happy,'RS006 - CONSUMER.PRIMARY')) AS RS006
_________________
22
202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||,
~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

 

What exactly does the of and colon do in your code?

Solution
‎11-10-2017 10:44 AM
Super User
Super User
Posts: 9,796

Re: How do I use a COUNTIF function within computed column of query builder?

Posted in reply to CWilson4815

Use the {i} above post for the code window to preserve formatting.  The colon indicates all variables with the prefix of the text before it, so you can read as "of all variables with decisiononothercoe prefix":

count(catx(',',of DECISIONOTHERCODES:),'RS006 - CONSUMER.PRIMARY')) AS RS006

  That syntax should work - I have none of your data to work with of course, but a similar code does:

proc sql;
  select count(catx(',',make,model,type),"Acura") as tmp
  from sashelp.cars;
quit;
Occasional Contributor
Posts: 11

Re: How do I use a COUNTIF function within computed column of query builder?

I followed your code from the cars example to help as the of and : function still wasn't working for me.

 

So I ended up having to write it as follows:

 

COUNT(CATX(',',DECISIONOTHERCODE1,DECISIONOTHERCODE2,DECISIONOTHERCODE3,DECISIONOTHERCODE4,DECISIONOTHERCODE5,DECISIONOTHERCODE6,DECISIONOTHERCODE7,DECISIONOTHERCODE8,DECISIONOTHERCODE9,DECISIONOTHERCODE10),'RS006 - CONSUMER.PRIMARY')

Thanks a lot for your help.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 1539 views
  • 2 likes
  • 2 in conversation