I have a bunch of values in a dataset that are formulated like 2000-3222 and 10/1-10.
I would like to split these so that it lists 2000, 2001 etc. and 10/1, 10/2 etc., all in their own rows.
Is there any command to do this in Stata or R?
EDIT:
Example data:
input int SRNo str200 SchemeName str30 CTSNo1 str4 CTSNo2
69 "Khimji Nagar SRA Co-op.Housing Society Ltd." "467" ""
70 "Jai Bhavani CHS Ltd. (Proposed)" "7 (Pt.)" ""
71 "Shivshakti SRA CHS Ltd." "364 ‘A’" ""
72 "Shree Ram CHS Ltd. (Prop.)" "96 (Pt.) -99(Pt.)" ""
end
Based on your example data (in which I added a few more observations to make things more illustrative), you need something along the following lines:
clear
input int SRNo str200 SchemeName str30 CTSNo1 str4 CTSNo2
69 "Khimji Nagar SRA Co-op.Housing Society Ltd." "467" ""
70 "Jai Bhavani CHS Ltd. (Proposed)" "7 (Pt.)" ""
71 "Bhavani Housing" "12(Pt.)-21(Pt.)" ""
72 "Shivshakti SRA CHS Ltd." "364 ‘A’" ""
73 "Shree Ram CHS Ltd. (Prop.)" "96 (Pt.)- 99(Pt.)" ""
74 "Ram CHS Ltd. (Prop.)" "107 (Pt.)- 114 (Pt.)" ""
end
generate tag = 0
replace tag = 1 if strmatch(CTSNo1, "*-*")
keep if tag == 1
generate part1 = regexs(0) if regexm(CTSNo1, "([0-9]+)")
generate part2 = substr(regexs(0), 2, .) if regexm(CTSNo1, "-.*([0-9])")
local obs = _N
forvalues i = 1 / `obs' {
local xpa = abs(real(part1[`i']) - real(part2[`i'])) + 1
expand `xpa' if _n == `i'
}
bysort SRNo (CTSNo1): egen interim = seq()
bysort SRNo (CTSNo1): generate NCTSNo1 = real(part1) + interim - 1
drop tag part1 part2 interim
order SRNo SchemeName CTSNo1 NCTSNo1 CTSNo2
The above code snipped produces the desired results:
list
+-----------------------------------------------------------------------------+
| SRNo SchemeName CTSNo1 NCTSNo1 CTSNo2 |
|-----------------------------------------------------------------------------|
1. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 12 |
2. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 13 |
3. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 14 |
4. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 15 |
5. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 16 |
|-----------------------------------------------------------------------------|
6. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 17 |
7. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 18 |
8. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 19 |
9. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 20 |
10. | 71 Bhavani Housing 12(Pt.)-21(Pt.) 21 |
|-----------------------------------------------------------------------------|
11. | 73 Shree Ram CHS Ltd. (Prop.) 96 (Pt.)- 99(Pt.) 96 |
12. | 73 Shree Ram CHS Ltd. (Prop.) 96 (Pt.)- 99(Pt.) 97 |
13. | 73 Shree Ram CHS Ltd. (Prop.) 96 (Pt.)- 99(Pt.) 98 |
14. | 73 Shree Ram CHS Ltd. (Prop.) 96 (Pt.)- 99(Pt.) 99 |
15. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 107 |
|-----------------------------------------------------------------------------|
16. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 108 |
17. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 109 |
18. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 110 |
19. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 111 |
20. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 112 |
|-----------------------------------------------------------------------------|
21. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 113 |
22. | 74 Ram CHS Ltd. (Prop.) 107 (Pt.)- 114 (Pt.) 114 |
+-----------------------------------------------------------------------------+
EDIT:
The forvalues loop in my solution above is not necessary. A different way of doing this, which avoids looping over observations, is the following:
bysort SRNo (CTSNo1): generate xpa = abs(real(part1) - real(part2)) + 1
expand xpa
Assuming all values look like your example and you variable(s) is of type string:
. clear
. set obs 1
number of observations (_N) was 0, now 1
.
. generate string1 = "2000-3222"
. generate new_string1 = substr(string1, 1, 4)
.
. generate string2 = "10/1-10"
. generate new_string2 = substr(string2, 1, 4)
.
. list
+-------------------------------------------+
| string1 new_st~1 string2 new_st~2 |
|-------------------------------------------|
1. | 2000-3222 2000 10/1-10 10/1 |
+-------------------------------------------+
This solution is useful if you only need a certain part of the original variable.
EDIT:
Using @Nick's excellent suggestion:
clear
set obs 1
generate string1 = "2000-3222"
generate string2 = "10/1-10"
split string1, parse("-") generate(split_string1)
split string2, parse("/") generate(split_string2)
list
+-----------------------------------------------------------------+
| string1 string2 split~11 split~12 split~21 split~22 |
|-----------------------------------------------------------------|
1. | 2000-3222 10/1-10 2000 3222 10 1-10 |
+-----------------------------------------------------------------+
As you can see, this solution will give you two variables for string1 and another two for string2 each containing both (separate) parts of the original variable.
