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
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#');
The ARRAY method shown here also works.
@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).
Chile_count=sum( col1='Chile' , col2='Chile' , col3='Chile') ;
@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;
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!
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.
Ready to level-up your skills? Choose your own adventure.