foreach

[Fadzai Chikwava]

Please help identify why this loop wont work on "`x'=="Z591". I get the message "type mismatch" before the command is fully executed.

generate vaed_homeless=0
foreach x of varlist TDIAG1-TDIAG40 {
recode vaed_homeless (0=1) if (`x'=="Z590"|`x'=="Z591")
}

[Andrew Musau]

You probably have some numeric variables in the varlist TDIAG1-TDIAG40. You can do something like:

generate vaed_homeless=0
ds TDIAG1-TDIAG40, has(type string)
foreach x in `r(varlist)'{
    recode vaed_homeless (0=1) if (`x'=="Z590"|`x'=="Z591")
}

[Fadzai Chikwava]

Thanks for this. But it seems I still have a few cases missing. Not sure how to have complete data which captures both numeric and string variables

[Clyde Schechter]

Well, if there is a numeric variable somewhere in the list TDIAG1-TDIAG40, it will, inevitably, not take on the values "Z590" or "Z591" so that omission should be of no consequence at all since your code does not call for any action except when the value Z590 or Z591 is present.

If that explanation does not satisfy you, I think you need to post back some example data that exhibits the problem you are encountering, and then show what results you are getting, and explain how they differ from what you want. When posting the example data, do use the -dataex' command. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Note: Since this looks like perhaps you are working with health care data, when posting an example, do not include any identifier information. Show only the variables TDIAG* and vaed_homeless.

[Fadzai Chikwava]

Dear all

Following from example above: I wanted to find out what the syntax "ds TDIAG1-TDIAG40, has(type string)" does. Because as described earlier "TDIAG1-TDIAG40" has both string and numeric data.
Please help with syntax to create a new variable "mh_child" which captures ICD-10 diagnostic codes "F90-F98" i.e. anything from F90, F91, F92 up to F98. I tried this below and it didnt work. See data below and syntax (only went up to TDIAG10 because of size limit): generate mh_child=0 ds TDIAG1-TDIAG40, has(type string) foreach x in `r(varlist)'{ recode mh_child (0=1) if `x'== range(F90/F98) }

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 TDIAG1 str6(TDIAG2 TDIAG3 TDIAG4 TDIAG5 TDIAG6 TDIAG7) str5 TDIAG8 str6 TDIAG9 str5 TDIAG10
"F500"  "F3220" "Z915"  "Z638"  ""      ""      ""     ""      ""     ""    
"O81"   "O688"  "O631"  "O721"  "O700"  "O864"  "O16"  "O7182" "Z658" "Z370"
"F410"  "Z590"  ""      ""      ""      ""      ""     ""      ""     ""    
"Q833"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"Q048"  "G4011" "L299"  "Y450"  "Y9222" "R11"   "I959" ""      ""     ""    
"F120"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K353"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R104"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R633"  "K219"  ""      ""      ""      ""      ""     ""      ""     ""    
"K358"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S199"  "S299"  "W109"  "Y929"  "U739"  "Z638"  "F919" ""      ""     ""    
"K759"  "Z290"  ""      ""      ""      ""      ""     ""      ""     ""    
"S959"  "X59"   "Y9230" "U5009" ""      ""      ""     ""      ""     ""    
"K590"  "K564"  "R14"   "Z934"  ""      ""      ""     ""      ""     ""    
"E1064" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"J353"  "T16"   "W44"   "Y929"  "U739"  "H659"  ""     ""      ""     ""    
"R104"  "J069"  ""      ""      ""      ""      ""     ""      ""     ""    
"H653"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F919"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S199"  "W51"   "Y9231" "U5709" ""      ""      ""     ""      ""     ""    
"R103"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K37"   ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K37"   "Z753"  ""      ""      ""      ""      ""     ""      ""     ""    
"N12"   ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S526"  "W188"  "Y9230" "U501"  ""      ""      ""     ""      ""     ""    
"J459"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K029"  "K036"  "K049"  ""      ""      ""      ""     ""      ""     ""    
"S0085" "S500"  "Y0402" "Y9209" "U739"  ""      ""     ""      ""     ""    
"F100"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"T424"  "T519"  "Y15"   "Y11"   "Y929"  "U739"  ""     ""      ""     ""    
"B349"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"M6743" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S064"  "S021"  "S0602" "V4818" "Y929"  "U739"  "R455" "H532"  "Z720" ""    
"K011"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S6261" "X59"   "Y929"  "U72"   ""      ""      ""     ""      ""     ""    
"R468"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F500"  "Z915"  ""      ""      ""      ""      ""     ""      ""     ""    
"F500"  "R680"  "R000"  "E162"  "F419"  "S5188" "W49"  "Y9222" "U739" ""    
"T407"  "R073"  "R000"  "X42"   "T598"  "X479"  "T510" "X45"   "Y929" "U739"
"F100"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F6031" "Z631"  ""      ""      ""      ""      ""     ""      ""     ""    
"N832"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K37"   ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R4581" "Z592"  ""      ""      ""      ""      ""     ""      ""     ""    
"S099"  "W22"   "Y929"  "U739"  ""      ""      ""     ""      ""     ""    
"F913"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F432"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F3220" "R4581" "K590"  "Z933"  ""      ""      ""     ""      ""     ""    
"F432"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F100"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"O82"   "O629"  "O688"  "O624"  "Z370"  "Z223"  "Z292" ""      ""     ""    
"F3220" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"J050"  "E876"  ""      ""      ""      ""      ""     ""      ""     ""    
"K010"  "K011"  ""      ""      ""      ""      ""     ""      ""     ""    
"F919"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R4581" "Z631"  ""      ""      ""      ""      ""     ""      ""     ""    
"K004"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R630"  "E86"   ""      ""      ""      ""      ""     ""      ""     ""    
"F500"  "S519"  "X788"  "S1090" "X83"   "Y9222" "U738" ""      ""     ""    
"S0265" "S0266" "Y0404" "Y9221" "U739"  ""      ""     ""      ""     ""    
"Z638"  "R073"  ""      ""      ""      ""      ""     ""      ""     ""    
"J039"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F101"  "F111"  "F121"  "R456"  "F410"  "H518"  "Y494" "Y9222" "Z720" ""    
"K029"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R4589" "R465"  "R51"   ""      ""      ""      ""     ""      ""     ""    
"F3290" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F341"  "R4581" "F913"  "F430"  "Z731"  "Z639"  "Z631" "Z915"  ""     ""    
"F431"  "F819"  "Z616"  "Z631"  ""      ""      ""     ""      ""     ""    
"Z038"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K358"  "K380"  ""      ""      ""      ""      ""     ""      ""     ""    
"J350"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S6232" "W22"   "Y929"  "U739"  "Z720"  ""      ""     ""      ""     ""    
"K4090" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F602"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S9081" "S198"  "R101"  "V4709" "Y9249" "U72"   ""     ""      ""     ""    
"S199"  "V4769" "Y9248" "U739"  ""      ""      ""     ""      ""     ""    
"M205"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R630"  "Z915"  ""      ""      ""      ""      ""     ""      ""     ""    
"S5311" "W094"  "Y9288" "U72"   ""      ""      ""     ""      ""     ""    
"T391"  "X60"   "Y929"  "U738"  "F430"  "Z614"  ""     ""      ""     ""    
"H020"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"R103"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"E201"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S5250" "V8000" "Y929"  "U635"  ""      ""      ""     ""      ""     ""    
"S8281" "W22"   "Y9209" "U739"  ""      ""      ""     ""      ""     ""    
"R104"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"K8050" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S0152" "Y34"   "X58"   "Y9218" "U739"  "K121"  "F840" ""      ""     ""    
"T407"  "X42"   "Y9209" "U739"  ""      ""      ""     ""      ""     ""    
"S0151" "Y0409" "Y929"  "U739"  "F100"  "Y908"  ""     ""      ""     ""    
"R13"   "K220"  ""      ""      ""      ""      ""     ""      ""     ""    
"K029"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"T509"  "X64"   "Y929"  "U738"  ""      ""      ""     ""      ""     ""    
"F3290" ""      ""      ""      ""      ""      ""     ""      ""     ""    
"G248"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"F418"  "F913"  ""      ""      ""      ""      ""     ""      ""     ""    
"E109"  ""      ""      ""      ""      ""      ""     ""      ""     ""    
"S050"  "S058"  "Y0402" "Y9209" "U739"  ""      ""     ""      ""     ""    
"R51"   "A099"  "R21"   ""      ""      ""      ""     ""      ""     ""    
"K590"  "K592"  "Z911"  "F3290" "Z934"  "Z924"  ""     ""      ""     ""    
end

------------------ copy up to and including the previous line ------------------

[Nick Cox]

ds TDIAG1-TDIAG40, has(type string)

looks at the variables you name and tells you which of them are string variables.

Otherwise the code you give defines a data example and does work. I don't know what 'captures ICD-10 diagnostic codes "F90-F98" ' means.


You could get an indicator variable for whether any of the variables are F90 ... F98 and a concatenation of any such codes as follows.

gen isF = 0 

gen codeF = "" 

foreach v of var TDIAG* { 
       replace isF = 1 if inlist(trim(`v'), "F90", "F91", "F92", "F93", "F94", "F95", "F96", "F97", "F98") 
       replace codeF = codeF + trim(`v') + " "  if inlist(`v', "F90", "F91", "F92", "F93", "F94", "F95", "F96", "F97", "F98") 
}

replace codeF = trim(codeF)

The code assumes that all the TDIAG* are string. A casual glance at your data example suggests that extra spaces are not a problem but even if so the trim() will do no harm.

[Nick Cox]

But if spaces are a problem then each inlist() call should be in terms of a variable squeezed by trim(). That is to say: the first use of inlist() in #6 is good practice and the second use is different only because I forgot to edit it.

[Fadzai Chikwava]

Dear Sir

The above syntax didn't produce any data. But I think I didn't explain myself well.

I just want to create a new variable (coded 0 or 1) where 1 represents the presence of any of "F90-F98" codes from any of "TDIAG1-TDIAG40" variables.

[Andrew Musau]

gen obsno=_n
reshape long TDIAG, i(obsno) j(which)
gen wanted= ustrregexm(trim(lower(TDIAG)), "^f9[0-8]$")
reshape wide TDIAG wanted, i(obsno) j(which)
egen wanted= rowtotal(wanted*)
replace wanted= wanted>0
drop wanted? wanted?? obsno

[Fadzai Chikwava]

Thanks. The above did not work. First error message I get is
"variable TDIAG38 type mismatch with other TDIAG variables". TDIAG38, TDIAG39 and TDIAG40 are numeric variables and the rest are string.

Maybe you may help simplify the below syntax, which worked for the first part, but seems the last line is too long or not necessary at all. Please advise. :

foreach v of varlist TDIAG1 TDIAG2 TDIAG3 TDIAG4 TDIAG5 TDIAG6 TDIAG7 TDIAG8 TDIAG9 TDIAG10 TDIAG11 TDIAG12 TDIAG13 TDIAG14 TDIAG15 TDIAG16 TDIAG17 TDIAG18 TDIAG19 TDIAG20 {
icd10 generate mh_`v' = `v', range(F90* F91* F92* F93* F94* F95* F96* F97* F98*)
}
gen mh_child = (mh_TDIAG1==1 | mh_TDIAG2==1| mh_TDIAG3==1 | mh_TDIAG4==1 | mh_TDIAG5==1 | .................................................. ...)

[Clyde Schechter]

Try

egen mh_child = rowmax(mh_TDIAG*)

instead of your -gen mh_childe = ...- command.

[Fadzai Chikwava]

Thanks, thats much simpler. But is there no way of reducing the syntax, by avoiding new variables "mh`v'" being created because I will drop these eventually. The variable of interest is "mh_child"

[Clyde Schechter]

But is there no way of reducing the syntax, by avoiding new variables "mh`v'" being created because I will drop these eventually. The variable of interest is "mh_child"

There is a way. You encountered a problem because for some reason TDIAG38, TDIAG39, and TDIAG40 are numeric, but the rest of the TDIAG* variables are strings. Since those numeric variables can never contain a code starting with letter F, the simplest solution is to -rename- them so they don't mess up the code. Then they can be renamed back at the end.

rename (TDIAG38 TDIAG39 TDIAG40), lower
gen long obs_no = _n
reshape long TDIAG, i(obs_no) j(_j)
icd10 generate mh_child = TDIAG, range(F90* F91* F92* F93* F94* F95* F96* F97* F98*)
by obs_no (_j), sort: replace mh_child = sum(mh_child)
by obs_no (_j): replace mh_child = min(mh_child[_N], 1)
reshape wide
rename (tdiag38 tdiag39 tdiag40), upper

By the way, I think this can even be simplified a bit farther. You have specified you want to look for codes F90-F98. But there is also an F99, which is mental disorder, not otherwise specified. Is there a reason not to include F99? If there is no such reason, then your icd10 generate command can be simplified to -icd10 generate mh_child = TDIAG, range(F9*)-.

Added: How does it happen that variables TDIAG38 TDIAG39 and TDIAG40 are numeric in your data set? All valid ICD10 codes must begin with a letter. It would seem that something is wrong with your data, and before you proceed further with it, you should review the data management to find out how those variables got mangled, and whether anything else went wrong along the way.

[Fadzai Chikwava]

Dear sir

I have checked and the 3 TDIAG variables do not have any data. So I will delete the 3 and you are right, these should be string.

I am looking at various mental health variables, so the childhood disorders goes from F90 to F98.

So after taking out the 3 variables: the below syntax worked, but I thought this could be further shortened:

foreach v of varlist TDIAG* {
icd10 generate mh2_`v' = `v', range(F90* F91* F92* F93* F94* F95* F96* F97* F98*)
}
egen mh2_child = rowmax(mh2_TDIAG*)

[Clyde Schechter]

I do not think there is any way to shorten the code you show in #14. It's only 4 lines--that's good!

The code in #13 is a bit longer, though it can be shortened slightly by removing the -rename- commands that appear at the beginning and end, now that you have removed the TDIAG38, TDIA39, and TDIAG40 variables. Despite being more lines of code, it might (or might not) run somewhat faster than the code in #14.