Count a specific value across columns

[monsterpie]

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

[FreelanceReinh]

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#');

[PaigeMiller]

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

[Reeza]

Or add counts before you transpose the data set?

[PaigeMiller]

---

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

[Ksharp]

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

[Kurt_Bremser]

---

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