Looping over merge files

[Anne Buzzell]

Good evening -

I'm currently working on generating cause specific SMRs for 20 different causes of death. Due to data privacy reasons, I have 20 different data files for the General Population that includes the mortality rates standardized to Age, sex, year, etc. I've been trying to loop over consecutive causes of death which correspond to merge file names (which are numbered 1-20)

I've tried the

foreach

as well as

forvalues

command without any success when it Comes to merging.
When I don't loop and instead manually include the numbers 1/20, I can obtain the SMRs.

forvalues i = 1/20 {
stset time_out2 , failure(cause_of_death==`i') id(id) origin(time s_date) exit(time td(30sep2011)) scale(365.25)

merge m:1 _year sex _age using "$dd\CoD data\`i'.dta", generate(_merge)
drop if _merge==2
drop _merge

di "SMR for CoD" `i'
strate, smr(rate)
}

Any insights would be greatly appreciated.

(Stata 14.2)

[Mike Lacy]

It's hard to help you here because you just said "without any success." We don't know what particular kind of non-success you are experiencing. If you would show exactly what you typed and the exact response from Stata (copy/paste between CODE delimiters per the FAQ), that would give someone a much better chance at helping you.

[Anne Buzzell]

Thanks for explaining this. My Loop code is shown below.

foreach num of numlist 1/20 {
stset time_out2 , failure(cause_of_death==`num') id(id_) origin(time s_date) exit(time td(30sep2011)) scale(365.25)

* Splitting at decade
capture stsplit decade , at(0 10) after(time = mdy(1,1,1990))
recode decade 10=1
lab var decade "decade-cohort"
capture lab def decade 0" 1990-1999" 1 "2000-2011"
lab val decade decade
tab decade

capture stsplit _agei, at(16 31 46 61 76) after(time=birth_d)
recode _agei 16=1 31=2 46=3 61=4 76=5
capture lab var _agei "age categories"
capture lab def _agei 1 "16-30" 2 "31-45" 3 "46-60" 4 "61-75" 5 " 76+", replace
lab val _agei _agei
drop if _agei<1
capture rename  _agei _age

* Setting a period band
capture stsplit _year, after(time=d(1/1/1990)) at(0(1)22) trim
capture replace _year=1990+_year

capture gen sex=male
recast byte sex
sort _year sex _age

merge m:1 _year sex _age using "$dd\CoD data\"`num'".dta", generate(_merge)
drop if _merge==2
drop _merge

di "SMR for CoD" `num'
strate, smr(rate)
}

The error code I receive is r(198) with the message: invalid '1'

(_agei: 2242 changes made)
(8 observations deleted)
invalid '1'
r(198);

end of do-file

Thanks for any insights you might have!

[Jorrit Gosens]

Can you include a line

set trace on

in your script, and report the last couple of lines before it produces the error?

From this message it appears something goes wrong in your block starting from the recoding, but I can't spot any incorrect syntax in there.

[Anne Buzzell]

Hey Jorrit, thanks for bringing code this to my awareness.
I'm including the end of the Output below:

  ------------------------------------------------------------------------------------------------ end recast ---
- sort _year sex _age
- merge m:1 _year sex _age using "$dd\CoD data"`num'".dta", generate(_merge)
= merge m:1 _year sex _age using "Q:\A D P\Project New\LCE\04_Mortality\03_NTSCI\Cause-specific survival\02_stata
> \01_data\CoD data"1".dta", generate(_merge)
  ----------------------------------------------------------------------------------------------- begin merge ---
  - version 11
  - gettoken mtype 0 : 0, parse(" ,")
  - if (!strpos("`mtype'", ":") & "`mtype'"!="") {
  = if (!strpos("m:1", ":") & "m:1"!="") {
    if (_caller()>=11) {
    di as smcl as txt "{p}"
    di as smcl "(note: you are using old"
    di as smcl "{bf:merge} syntax; see"
    di as smcl "{bf:{help merge:[D] merge}} for new syntax)"
    di as smcl "{p_end}"
    }
    merge_10 `mtype' `0'
    exit
    }
  - local origmtype `"`mtype'"'
  = local origmtype `"m:1"'
  - mata: map_mtype(`"mtype"')
  - gettoken token : 0, parse(" ,")
  - if ("`token'"=="_n") {
  = if ("_year"=="_n") {
    if ("`mtype'"!="1:1") {
    error_seq_not11 "`mtype'" "`origmtype'"
    }
    gettoken token 0 : 0, parse(" ,")
    local mtype "_n"
    }
  - syntax [varlist(default=none)] using/ [, ASSERT(string) DEBUG GENerate(name) FORCE KEEP(string) KEEPUSing(str
> ing) noLabel NOGENerate noNOTEs REPLACE noREPort SORTED UPDATE ]
invalid '1'
  ------------------------------------------------------------------------------------------------- end merge ---
  drop if _merge==2
  drop _merge
  strate, smr(rate)
  }
r(198);

Do you have an inkling on how to Interpret this Output?
Many thanks,

Annie

[Jorrit Gosens]

Ah. Yes, your problem is here:

merge m:1 _year sex _age using "$dd\CoD data\"`num'".dta", generate(_merge)

which is expanded to:

merge m:1 _year sex _age using "$dd\CoD data\"1".dta", generate(_merge)

What you want is, most likely, file name such as:

merge m:1 _year sex _age using "$dd\CoD data\1.dta", generate(_merge)

Which means you need:

merge m:1 _year sex _age using "$dd\CoD data\`num'.dta", generate(_merge)

[Anne Buzzell]

Thank you !!
Ok, so I've tried this before and it didn't work. And it still didn't work, however, perhaps you can Interpret this trace?

 -------------------------------------------------------------------------------------------------------------- end merge.checksort ---
  - if (!r(sorted)) {
    sort `varlist'
    }
  - quietly describe using "`using'", varlist short
  = quietly describe using "Q:\A D P\Project New\LCE\04_Mortality\03_NTSCI\Cause-specific survival\02_stata\01_data\CoD data.dta", varlist
>  short
    ------------------------------------------------------------------------------------------------------------------- begin describe ---
    - version 9
    - local version : di "version " string(_caller()) ":"
    - syntax [anything] [using] [, SImple REPLACE *]
    - if ("`replace'"!="") {
    = if (""!="") {
      describe_mk `0'
      return add
      exit
      }
    - local varlist `"`anything'"'
    = local varlist `""'
    - if ("`simple'" == "") {
    = if ("" == "") {
    - `version' _describe `0'
    = version 11: _describe using "Q:\A D P\Project New\LCE\04_Mortality\03_NTSCI\Cause-specific survival\02_stata\01_data\CoD data.dta",
> varlist short
file Q:\A D P\Project New\LCE\04_Mortality\03_NTSCI\Cause-specific survival\02_stata\01_data\CoD data.dta not found
      return add
      }
    --------------------------------------------------------------------------------------------------------------------- end describe ---
    local sortedby "`r(sortlist)'"
    checksort "`sortedby'" "`varlist'"
    if (!r(sorted)) {
    preserve
    qui use "`using'", clear
    sort `varlist'
    tempfile using
    qui save "`using'", replace
    restore
    }
    }
  -------------------------------------------------------------------------------------------------------------------------- end merge ---
  drop if _merge==2
  drop _merge
  strate, smr(rate)
  }
r(601);

end of do-file

r(601);

P.S not sure the etiqutte for posting Output like this - if html makes sense.
Anyhoozle I really appreciate your eyes on this

[Jorrit Gosens]

As a first piece of advice: make sure you always post exact code.
In #1:

merge m:1 _year sex _age using "$dd\CoD data\`i'.dta", generate(_merge)

In #3:

merge m:1 _year sex _age using "$dd\CoD data\"`num'".dta", generate(_merge)

From #5:

merge m:1 _year sex _age using "$dd\CoD data"`num'".dta", generate(_merge)

Perhaps this is because you had been trying some small changes to the code whilst your were waiting for feedback form here, but this makes it more difficult to answer your question. Exact suggestions require exact code.

In any case, look at the lines below to see how Stata interprets your code:

- merge m:1 _year sex _age using "$dd\CoD data`num'.dta", generate(_merge)
= merge m:1 _year sex _age using "Q:\A D P\Project New\LCE\04_Mortality\03_NTSCI\Cause-specific survival\02_stata\01_data\CoD data1.dta", generate(_merge)

And make sure the files do indeed exist as

"Q:\A D P\Project New\LCE\04_Mortality\03_NTSCI\Cause-specific survival\02_stata\01_data\CoD data1.dta"

As for your question in #7:
to understand the trace we'd also need to see how you define your "`using'"
From the looks of it, it is trying to find a file named:

"somepath\01_data\CoD data.dta"

From your earlier code, it would seem you have files as:

"somepath\01_data\CoD data1.dta"

or

"somepath\01_data\CoD data\1.dta"

So that could give you trouble.

If your earlier code, for merging, still didn't work, check the use of quotation marks, and if the file that stata looks for does indeed exist. For more help, post exact code for that merge problem.

For posting code blocks, you can use code tags, which is the button left from the html code tags in the editor. Although results are mostly the same, maybe exactly the same.

[Anne Buzzell]

Thank you for the advice, Jorrit! You know I read this in the FAQ, but there's definitely something different about actually asking for help. Your comments helped me figure out the issue.
Although when I open the merge files individually they are saved with a backwards slash just before the number `n' - the Loop only works when I use a Forward slash.

"somepath\01_data\CoD data/`n'.dta"

This was a huge help. I'm quite grateful for the insight.

[Jorrit Gosens]

Ah, I missed this entirely because it's not something you run into all too often, but the issue was Stata's use of the \ as escape character.
https://www.stata.com/support/faqs/p...es-and-macros/

So according to that guide, where I advised to use:

merge m:1 _year sex _age using "$dd\CoD data\`num'.dta", generate(_merge)

I should have said:

merge m:1 _year sex _age using "$dd\CoD data\\`num'.dta", generate(_merge)

Your solution with the forward slashes also works because windows doesn't care too much about back- or forward slashes in file paths.

In any case, using the trace to figure out how Stata interprets your macros is often a good first tool in figuring out why something doesnt work.