How to generate a new column that counts strings separated by a comma under a particular variable for each observation?

[anisha arya]

Hi,

I have date that looks like

Observation	X
1	1,2,3
2	3,4
3	1,5,4,2
4	1
5	1,2,3

How do I generate a column that counts the number of strings (numbers inputted as strings) for every observation?

So I want something like:

Observation	X	Count_X
1	1,2,3	3
2	3,4	2
3	1,5,4,2	4
4	1	1
5	1,2,3	3

Thanks in advance!

[Andrew Musau]

Count the commas + 1.

gen wanted = length(X) - length(subinstr(X, ",", "", .)) +1

[Nick Cox]

Please use Stata terminology on Statalist. By columns you mean variables.

Please use dataex to give examples (FAQ Advice #12)

Count the number of commas and add 1. One way to count commas to see how much shorter the string would be if the commas were deleted.

See for more


SJ-11-2 dm0056 . . . . . . . Stata tip 98: Counting substrings within strings
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q2/11 SJ 11(2):318--320 (no commands)
tip on counting substrings within strings

* Example generated by -dataex-. For more info, type help dataex
clear
input byte observation str7 x
1 "1,2,3"  
2 "3,4"    
3 "1,5,4,2"
4 "1"      
5 "1,2,3"  
end

gen wanted = strlen(x) - strlen(subinstr(x, ",", "", .)) + 1 

list 

     +-----------------------------+
     | observ~n         x   wanted |
     |-----------------------------|
  1. |        1     1,2,3        3 |
  2. |        2       3,4        2 |
  3. |        3   1,5,4,2        4 |
  4. |        4         1        1 |
  5. |        5     1,2,3        3 |
     +-----------------------------+