topic Count a specific value across columns in SAS Procedures

Count a specific value across columns

monsterpie — Sat, 28 Mar 2020 14:33:57 GMT

Hi all,

I have a transposed dataset and I am trying to create a variable to count the number of times "Chile" appears per id. Below is an example of my data and the final dataset I am trying to achieve.

EXAMPLE DATASET:

ID Col1 Col2 Col3

01 USA China Chile

02 France Chile Chile

03 Chile USA Greece

Final dataset I am trying to achieve:

ID Col1 Col2 Col3 Chile_count

01 USA China Chile 1

02 France Chile Chile 2

03 Chile USA Greece 1

Re: Count a specific value across columns

FreelanceReinh — Sat, 28 Mar 2020 15:03:32 GMT

Hi @monsterpie,

Use the COUNT function:

data want;
set have;
Chile_count=count(catx('#',of Col:),'Chile');
run;

If the "Col" variables may contain strings of which "Chile" is only a substring (as in "Chilean") and you don't want to count these, use:

Chile_count=count('#'||catx('##',of Col:)||'#','#Chile#');

Re: Count a specific value across columns

PaigeMiller — Sat, 28 Mar 2020 16:07:06 GMT

The ARRAY method shown here also works.

https://communities.sas.com/t5/SAS-Programming/Sum-by-an-ID-variable-according-to-a-logical-test/m-p/635325#M188634

Re: Count a specific value across columns

Reeza — Sat, 28 Mar 2020 20:18:36 GMT

Or add counts before you transpose the data set?

Re: Count a specific value across columns

PaigeMiller — Sat, 28 Mar 2020 20:44:48 GMT

@Reeza wrote:
Or add counts before you transpose the data set?

Probably the best solution so far!

As I said in the thread that I linked to, the best solution is a long data set, rather than a wide one (which is true in almost any situation).

Re: Count a specific value across columns

Ksharp — Sun, 29 Mar 2020 12:01:03 GMT

 Chile_count=sum( col1='Chile' ,  col2='Chile' , col3='Chile')  ;

Re: Count a specific value across columns

Kurt_Bremser — Sun, 29 Mar 2020 14:47:22 GMT

@monsterpie wrote:

Hi all,

I have a transposed dataset

which is what causes your problem, because with the untransposed data it's just

proc sql;
create table want as
select
  id,
  sum(ifn(col = "Chile",1,0)) as count
from have
group by id;
quit;