Loop for extracting strings from multiple variables

Question

I am trying to automate the solution provided for the problem described in my previous post:

Obtain values separated by hyphens.

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.)

You need to tell us what the problem is exactly. Also, are all your CTSNo variables of similar content? You need to provide us example data for them using dataex. — user8682794
– user8682794, Commented Jun 19, 2018 at 12:55
Please also read How to Ask and how to create a minimal reproducible example. — user8682794
– user8682794, Commented Jun 19, 2018 at 12:57
Equally importantly, have you studied the code i provided carefully? Do you understand what it does? For example you don't need the order command in the outer foreach loop (which is also wrong). You do not even need the forvalues loop as i stated in my answer yesterday. — user8682794
– user8682794, Commented Jun 19, 2018 at 13:06
Based on your example data, you cannot automate the code. Your variables are different and require different rules to be applied to get what you need from each one. You will have to run a (modified) version of my code for each variable manually, save the new variable and then merge everything in the end. — user8682794
– user8682794, Commented Jun 19, 2018 at 13:13

score 1 · Accepted Answer · 2018-06-20 08:39:24Z

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  |
     +----------------------------------------------------------+

if strlen(trim(part1)) == 3 will be evaluated (always and only) as if strlen(trim(part1[1])) == 3. Are you sure about that?
@NickCox Yes and no. Yes because in this example it will always collapse to one observation and thus part1[1] is okay. But if you have more than one case in the original variable, this will have to be re-engineered i guess.
@NickCox What i was more interested in knowing was whether there is a way to combine the strmatch() conditions in the if statement. I can't imagine doing this with 50 variables! But i suppose there isn't a solution for this.
Sorry, but I lack the time and inclination to go all the way back to study the complete problem and give a view.

Collectives™ on Stack Overflow

Loop for extracting strings from multiple variables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related