BookmarkSubscribeRSS Feed
monsterpie
Obsidian | Level 7

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

 

6 REPLIES 6
FreelanceReinh
Jade | Level 19

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#');
Reeza
Super User
Or add counts before you transpose the data set?
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User
 Chile_count=sum( col1='Chile' ,  col2='Chile' , col3='Chile')  ;
Kurt_Bremser
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1603 views
  • 4 likes
  • 6 in conversation