statuser
Reputation: 63
Loop for extracting strings from multiple variables
I am trying to automate the solution provided for the problem described in my previous post:
I am having trouble getting the following loop to work correctly for each variable CTSNo:
foreach i in CTSNo1-CTSNo54{
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 SchemeAddress ProposalNo CTSNo1 NCTSNo1 CTSNo2
}
How can I do this?
EDIT:
Example data:
input str30 CTSNo1 float NCTSNo1 str4 CTSNo2 str24 CTSNo3 str20 CTSNo4
str13 CTSNo5 str7 CTSNo6 str17 CTSNo7 str11 CTSNo8 str18 CTSNo9 str3
CTSNo10
"455-D" 455 "" " 455-D/2 - 24" "" "" "" ""
"" "" ""
"156-B" 156 "" " 156-B/36 - 57" " 156-B/70 - 89" "" "" " 156-D
(pt.)" "" " 156-D/158 - 69" ""
"938-A (Part)" 938 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 631 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 632 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 633 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 634 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 635 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 636 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 637 "" "" "" "" "" ""
"" "" ""
"631 Part - 3" 638 "" "" "" "" "" ""
"" "" ""
I would like each CTSNo to have a NCTSNo following it in order to expand the values. For example, 156-B/36-57 should have separate rows for 156-B/36, 156-B/37, all the way to 156-B/57.
(I'm also not sure why 631 Part - 3 is expanded and would like values like 455-D to retain the -D instead of just being 455.)
Upvotes: 0
Views: 340
Answers (1)
user8682794
Reputation:
Based on your sample data, you cannot generalize the code from your previous post. This is because the contents of your CTSNo variables do not present a consistent pattern. Thus they require different rules to be applied in order to get what you need from each one.
However, below you can find a simplified toy example of how you can automate the 'expansion' of values when you have multiple CTSNo variables, which are all of the form 156-B/36-57.
Example data:
clear
input int SRNo str200 SchemeName str30 CTSNo3 str15 CTSNo4 str15 CTSNo9
69 "SRA Co-op.Housing Society Ltd." " 156-F/233 - 47" ""
70 "Jai Bhavani CHS Ltd. (Proposed)" "7 (Pt.)" ""
71 "Bhavani Housing" " " "156-B/70 - 89 " ""
72 "Shivshakti SRA CHS Ltd." "364 ‘A’" ""
73 "Shree Ram CHS Ltd. (Prop.)" " " ""
74 "Ram CHS Ltd. (Prop.)" " " " " " 156-D/158 - 69 "
end
list
+---------------------------------------------------------------------------------------------+
| SRNo SchemeName CTSNo3 CTSNo4 CTSNo9 |
|---------------------------------------------------------------------------------------------|
1. | 69 SRA Co-op.Housing Society Ltd. 156-F/233 - 47 |
2. | 70 Jai Bhavani CHS Ltd. (Proposed) 7 (Pt.) |
3. | 71 Bhavani Housing 156-B/70 - 89 |
4. | 72 Shivshakti SRA CHS Ltd. 364 ‘A’ |
5. | 73 Shree Ram CHS Ltd. (Prop.) |
|---------------------------------------------------------------------------------------------|
6. | 74 Ram CHS Ltd. (Prop.) 156-D/158 - 69 |
+---------------------------------------------------------------------------------------------+
The modified code snippet:
preserve
generate tag = 0
replace tag = 1 if !strmatch(CTSNo3, "*/*") & !strmatch(CTSNo4, "*/*") & ///
!strmatch(CTSNo9, "*/*")
keep if tag == 1
save base, replace
restore
ds CTSNo*
local CTSvars "`r(varlist)'"
foreach var of local CTSvars {
preserve
generate tag = 0
replace tag = 1 if strmatch(`var', "*/*")
keep if tag == 1
generate part0 = substr(`var', 1, strpos(`var', "/"))
generate part1 = substr(`var', strpos(`var', "/") + 1, strpos(`var', "-") - 1)
generate part2 = substr(`var', strrpos(`var', "-") + 1, .)
if strlen(trim(part1)) == 3 {
generate xpa = abs(real(substr(part1, 2, .)) - real(part2)) + 1
}
else {
generate xpa = abs(real(part1) - real(part2)) + 1
}
expand xpa
bysort SRNo (`var'): egen interim = seq()
bysort SRNo (`var'): generate N`var' = part0 + string(real(part1) + interim - 1)
drop tag part0 part1 part2 xpa interim
list
save `var', replace
restore
}
use base, clear
append using `CTSvars'
sort SRNo NCTS*
The final result:
list SRNo SchemeName NCTS*
+-----------------------------------------------------------------------------+
| SRNo SchemeName NCTSNo3 NCTSNo4 NCTSNo9 |
|-----------------------------------------------------------------------------|
1. | 69 SRA Co-op.Housing Society Ltd. 156-F/233 |
2. | 69 SRA Co-op.Housing Society Ltd. 156-F/234 |
3. | 69 SRA Co-op.Housing Society Ltd. 156-F/235 |
4. | 69 SRA Co-op.Housing Society Ltd. 156-F/236 |
5. | 69 SRA Co-op.Housing Society Ltd. 156-F/237 |
|-----------------------------------------------------------------------------|
6. | 69 SRA Co-op.Housing Society Ltd. 156-F/238 |
7. | 69 SRA Co-op.Housing Society Ltd. 156-F/239 |
8. | 69 SRA Co-op.Housing Society Ltd. 156-F/240 |
9. | 69 SRA Co-op.Housing Society Ltd. 156-F/241 |
10. | 69 SRA Co-op.Housing Society Ltd. 156-F/242 |
|-----------------------------------------------------------------------------|
11. | 69 SRA Co-op.Housing Society Ltd. 156-F/243 |
12. | 69 SRA Co-op.Housing Society Ltd. 156-F/244 |
13. | 69 SRA Co-op.Housing Society Ltd. 156-F/245 |
14. | 69 SRA Co-op.Housing Society Ltd. 156-F/246 |
15. | 69 SRA Co-op.Housing Society Ltd. 156-F/247 |
|-----------------------------------------------------------------------------|
16. | 70 Jai Bhavani CHS Ltd. (Proposed) |
17. | 71 Bhavani Housing 156-B/70 |
18. | 71 Bhavani Housing 156-B/71 |
19. | 71 Bhavani Housing 156-B/72 |
20. | 71 Bhavani Housing 156-B/73 |
|-----------------------------------------------------------------------------|
21. | 71 Bhavani Housing 156-B/74 |
22. | 71 Bhavani Housing 156-B/75 |
23. | 71 Bhavani Housing 156-B/76 |
24. | 71 Bhavani Housing 156-B/77 |
25. | 71 Bhavani Housing 156-B/78 |
|-----------------------------------------------------------------------------|
26. | 71 Bhavani Housing 156-B/79 |
27. | 71 Bhavani Housing 156-B/80 |
28. | 71 Bhavani Housing 156-B/81 |
29. | 71 Bhavani Housing 156-B/82 |
30. | 71 Bhavani Housing 156-B/83 |
|-----------------------------------------------------------------------------|
31. | 71 Bhavani Housing 156-B/84 |
32. | 71 Bhavani Housing 156-B/85 |
33. | 71 Bhavani Housing 156-B/86 |
34. | 71 Bhavani Housing 156-B/87 |
35. | 71 Bhavani Housing 156-B/88 |
|-----------------------------------------------------------------------------|
36. | 71 Bhavani Housing 156-B/89 |
37. | 72 Shivshakti SRA CHS Ltd. |
38. | 73 Shree Ram CHS Ltd. (Prop.) |
39. | 74 Ram CHS Ltd. (Prop.) 156-D/158 |
40. | 74 Ram CHS Ltd. (Prop.) 156-D/159 |
|-----------------------------------------------------------------------------|
41. | 74 Ram CHS Ltd. (Prop.) 156-D/160 |
42. | 74 Ram CHS Ltd. (Prop.) 156-D/161 |
43. | 74 Ram CHS Ltd. (Prop.) 156-D/162 |
44. | 74 Ram CHS Ltd. (Prop.) 156-D/163 |
45. | 74 Ram CHS Ltd. (Prop.) 156-D/164 |
|-----------------------------------------------------------------------------|
46. | 74 Ram CHS Ltd. (Prop.) 156-D/165 |
47. | 74 Ram CHS Ltd. (Prop.) 156-D/166 |
48. | 74 Ram CHS Ltd. (Prop.) 156-D/167 |
49. | 74 Ram CHS Ltd. (Prop.) 156-D/168 |
50. | 74 Ram CHS Ltd. (Prop.) 156-D/169 |
+-----------------------------------------------------------------------------+
@Nick Cox's Note:
I haven't studied the problem, just your solution. The following version includes some simplifications. Take what you want and ignore the rest.
clear
input int SRNo str200 SchemeName str30 CTSNo3 str15 CTSNo4 str15 CTSNo9
69 "SRA Co-op.Housing Society Ltd." " 156-F/233 - 47" ""
70 "Jai Bhavani CHS Ltd. (Proposed)" "7 (Pt.)" ""
71 "Bhavani Housing" " " "156-B/70 - 89 " ""
72 "Shivshakti SRA CHS Ltd." "364 ‘A’" ""
73 "Shree Ram CHS Ltd. (Prop.)" " " ""
74 "Ram CHS Ltd. (Prop.)" " " " " " 156-D/158 - 69 "
end
list
preserve
generate tag = !strmatch(CTSNo3, "*/*") & !strmatch(CTSNo4, "*/*") & ///
!strmatch(CTSNo9, "*/*")
keep if tag
save base, replace
restore
unab CTSvars : CTSNo*
foreach var of local CTSvars {
preserve
generate tag = strmatch(`var', "*/*")
keep if tag
generate part0 = substr(`var', 1, strpos(`var', "/"))
generate part1 = substr(`var', strpos(`var', "/") + 1, strpos(`var', "-") - 1)
generate part2 = substr(`var', strrpos(`var', "-") + 1, .)
if strlen(trim(part1)) == 3 {
generate xpa = abs(real(substr(part1, 2, .)) - real(part2)) + 1
}
else {
generate xpa = abs(real(part1) - real(part2)) + 1
}
expand xpa
bysort SRNo (`var'): generate N`var' = part0 + string(real(part1) + _n - 1)
drop tag part0 part1 part2 xpa
list
save `var', replace
restore
}
use base, clear
append using `CTSvars'
sort SRNo NCTS*
list SRNo SchemeName NCTS*
EDIT:
When you have more than one observation in a CTSNo variable, the following portion of the code:
if strlen(trim(part1)) == 3 {
generate xpa = abs(real(substr(part1, 2, .)) - real(part2)) + 1
}
else {
generate xpa = abs(real(part1) - real(part2)) + 1
}
Should be replaced with:
drop tag
generate tag = strlen(trim(part1))
generate xpa = .
replace xpa = abs(real(part1) - real(part2)) + 1
replace xpa = abs(real(substr(part1, 2, .)) - real(part2)) + 1 if tag == 3
For example, if your CTSNo4 variable looked like this:
+----------------------------------------------------------+
| SRNo SchemeName CTSNo4 |
|----------------------------------------------------------|
1. | 69 SRA Co-op.Housing Society Ltd. |
2. | 70 Jai Bhavani CHS Ltd. (Proposed) |
3. | 71 Bhavani Housing 156-B/70 - 89 |
4. | 72 Shivshakti SRA CHS Ltd. |
5. | 73 Shree Ram CHS Ltd. (Prop.) |
|----------------------------------------------------------|
6. | 74 Ram CHS Ltd. (Prop.) |
7. | 75 CHS Society Housing 113-Z/160 - 72 |
+----------------------------------------------------------+
Upvotes: 1
Related Questions
- Loop for particular values of variable in Stata
- Join string variables in pairs of two using loops in Stata
- How to loop through specific string values of a variable in Stata?
- Dropping various string variables in a loop in Stata
- Stata - Add Prefix to Variable Values
- Stata: Extracting values and save them as scalars (and more)
- Loop over string variables in R
- Looping through variable names in R
- looping through variable names according to their prefix
- Looping in Stata through pairs of strings