topic Re: Count only selected word in a string column in SAS Enterprise Guide

Count only selected word in a string column

sdang — Tue, 16 Feb 2016 19:01:35 GMT

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.

Thank you all,

Re: Count only selected word in a string column

mohamed_zaki — Tue, 16 Feb 2016 19:10:42 GMT

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

Re: Count only selected word in a string column

Haikuo — Tue, 16 Feb 2016 19:11:59 GMT

Could be as straightforward as:

proc sql;
select sum(playtier1='classic') as classic_ct from have ;
quit;

Re: Count only selected word in a string column

sdang — Tue, 16 Feb 2016 19:12:24 GMT

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

Re: Count only selected word in a string column

sdang — Tue, 16 Feb 2016 19:12:54 GMT

Thank you!

Re: Count only selected word in a string column

mohamed_zaki — Tue, 16 Feb 2016 19:17:19 GMT

Check this

Sample 31447: Counting the Number of Observations in a BY Group in SAS® Enterprise Guide

Re: Count only selected word in a string column

Haikuo — Tue, 16 Feb 2016 19:19:15 GMT

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

Re: Count only selected word in a string column

mohamed_zaki — Tue, 16 Feb 2016 19:32:09 GMT

@sdang wrote:

instead of resorting to proc sql?

btw, What this mean?

Re: Count only selected word in a string column

Reeza — Tue, 16 Feb 2016 20:06:57 GMT

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.

Re: Count only selected word in a string column

FreelanceReinh — Tue, 16 Feb 2016 20:57:55 GMT

@mohamed_zaki wrote:

@sdang wrote:

instead of resorting to proc sql?

btw, What this mean?

Please see "to resort to something."

Re: Count only selected word in a string column

sdang — Tue, 16 Feb 2016 21:04:58 GMT

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

Re: Count only selected word in a string column

Reeza — Tue, 16 Feb 2016 21:08:58 GMT

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.

Re: Count only selected word in a string column

sdang — Tue, 16 Feb 2016 21:10:22 GMT

Thank you!

Re: Count only selected word in a string column

sdang — Tue, 16 Feb 2016 21:11:01 GMT

Thank you!