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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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
  • 2142 views
  • 4 likes
  • 6 in conversation