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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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