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.
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
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:
The ARRAY method shown here also works.
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).
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 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.
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.