Desktop productivity for business analysts and programmers

Count only selected word in a string column

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Count only selected word in a string column

Hi all,

I have a character column, "PlayerTier1" contains "Classic" and "Emerald".  I want how many of "Classic" in that column without resorting to filter out "Classic".  Much like a CountIF function in excel.

 

 

Capture.PNG

Thank you all,


Accepted Solutions
Solution
‎02-16-2016 03:43 PM
Super Contributor
Posts: 490

Re: Count only selected word in a string column

proc sql;
select count(PlayerTier1)
from have
where PlayerTier1='Classic';
quit;

View solution in original post


All Replies
Solution
‎02-16-2016 03:43 PM
Super Contributor
Posts: 490

Re: Count only selected word in a string column

proc sql;
select count(PlayerTier1)
from have
where PlayerTier1='Classic';
quit;
Frequent Contributor
Posts: 104

Re: Count only selected word in a string column

Thank you!  But is there a Query Builder function I can use instead of resorting to proc sql?

Super Contributor
Posts: 490

Re: Count only selected word in a string column

Super Contributor
Posts: 490

Re: Count only selected word in a string column


sdang wrote:

 instead of resorting to proc sql?


btw, What this mean?

Trusted Advisor
Posts: 1,116

Re: Count only selected word in a string column


mohamed_zaki wrote:

sdang wrote:

 instead of resorting to proc sql?


btw, What this mean?


Please see "to resort to something."

Frequent Contributor
Posts: 104

Re: Count only selected word in a string column

HI,

I'm sorry if my choice of word might be mis-led.  I meant is there another way to get a summary table from Query Builder where a count column only counts Classic but others numeric columns sum by or count regardless of Classic? And using only Query Builder function, and not having to write codes in Proc SQL

 

Thanks

Super User
Posts: 19,167

Re: Count only selected word in a string column

The answer is in @Haikuo post.

 

Probably worth marking this question answered at this point as well.

 

One last tip, the comparison is case sensitive, so determine if you need to account for that. If you do, use UPPER() or LOWER() to set them all to the same case.

 

 

Frequent Contributor
Posts: 104

Re: Count only selected word in a string column

Thank you!
Frequent Contributor
Posts: 104

Re: Count only selected word in a string column

Thank you!

Respected Advisor
Posts: 3,156

Re: Count only selected word in a string column

Could be as straightforward as:

 

proc sql;
select sum(playtier1='classic') as classic_ct from have ;
quit;
Frequent Contributor
Posts: 104

Re: Count only selected word in a string column

Thank you!

Respected Advisor
Posts: 3,156

Re: Count only selected word in a string column

Add new computed column -> advanced expression -> sum(playertier1='classic') -> enter the name of your new column -> done.

Super User
Posts: 19,167

Re: Count only selected word in a string column

Query Builder writes SQL, so its the same as adding a filter within Query Builder.

 

For this type of summary its useful to determine why you want it, for display or in a table?

 

If display I would say a Summary Task would be more appropriate. 

☑ This topic is solved.

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

Discussion stats
  • 13 replies
  • 625 views
  • 2 likes
  • 5 in conversation