replacing previous observations according to current observation, foreach? replace?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

replacing previous observations according to current observation, foreach? replace?

Charlotte Picard
Hello Stata users,
I am a new stata user and I am trying to work backwards in my data.

Region Week Cases
1             1       5
2             1       4
3             1       0
4             1       2
1             2       3
2             2       2
3             2       1
4             2       0
1             3       2
2             3      -3
3             3       0
4             3       4

I am trying to create code that will find negative values for Cases and subtract them to the cases from the previous week, if that weeks cases then become negative it will subtract them from the week before that and so on until the case count isn't negative anymore and fill in these new values of case in a new variable.  I want to do this within each region seperately but I'm having trouble determining if I need to make some sort of foreach loop, which I've never tried before of if there is a simpler way.

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
Reply | Threaded
Open this post in threaded view
|

Re: replacing previous observations according to current observation, foreach? replace?

Eric A. Booth
<>

On Dec 2, 2010, at 1:14 PM, Charlotte Picard wrote:
> I am trying to create code that will find negative values for Cases and subtract them to the cases from the previous week, if that weeks cases then become negative it will subtract them from the week before that and so on until the case count isn't negative anymore and fill in these new values of case in a new variable.


I'm not sure I completely understand.  An example of what you want this new variable to look like would be helpful.

Below is some code that does what I _think_ you are asking for -- that is, when "cases" value is negative, it adds that negative "cases" value to the previous week's "cases" value within (-by-) each region and puts that new value in a new variable ("cases2").  If "cases2" is still not positive after being added to the prior week's "case" value, then the negative "case2" is added to "cases" from to 2 weeks prior and so on until "case2" is >= 0 or the number of weeks runs out.

I changed the value in region 4, week 3 to show an example of a negative value that never goes positive because it runs out of weeks to go back in time.

Look at the changes in the "list" command at each step of the loop to see that changes that are being made to "cases2".  Only regions 2 and 4 are affected because the others do not have negative "cases".

*******!
clear
inp region week cases
1             1       5
2             1       4
3             1       0
4             1       2
1             2       3
2             2       2
3             2       1
4             2       0
1             3       2
2             3      -3
3             3       0
4             3      -4
end


sort region week
clonevar cases2 = cases

**compare tables in the loop to this table**
l if inlist(region, 2, 4), sepby(region)

qui su week, d
forval n = 1/`r(max)' {
by region: replace cases2 = cases[_n-`n']+cases2  ///
        if cases2<0 & region[_n] == region[_n-`n']
di in r "Changes based on `n' Weeks Prior to Week `n' -->"
li if inlist(region, 2, 4) , sepby(region)
}

*******!

- Eric

__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
[hidden email]
Office: +979.845.6754


On Dec 2, 2010, at 1:14 PM, Charlotte Picard wrote:

> Hello Stata users,
> I am a new stata user and I am trying to work backwards in my data.
>
> Region Week Cases
> 1             1       5
> 2             1       4
> 3             1       0
> 4             1       2
> 1             2       3
> 2             2       2
> 3             2       1
> 4             2       0
> 1             3       2
> 2             3      -3
> 3             3       0
> 4             3       4
>
> I am trying to create code that will find negative values for Cases and subtract them to the cases from the previous week, if that weeks cases then become negative it will subtract them from the week before that and so on until the case count isn't negative anymore and fill in these new values of case in a new variable.  I want to do this within each region seperately but I'm having trouble determining if I need to make some sort of foreach loop, which I've never tried before of if there is a simpler way.
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/




*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
Reply | Threaded
Open this post in threaded view
|

Re: replacing previous observations according to current observation, foreach? replace?

Sergiy Radyakin
In reply to this post by Charlotte Picard
On Thu, Dec 2, 2010 at 2:14 PM, Charlotte Picard
<[hidden email]> wrote:

> Hello Stata users,
> I am a new stata user and I am trying to work backwards in my data.
>
> Region Week Cases
> 1             1       5
> 2             1       4
> 3             1       0
> 4             1       2
> 1             2       3
> 2             2       2
> 3             2       1
> 4             2       0
> 1             3       2
> 2             3      -3
> 3             3       0
> 4             3       4
>
> I am trying to create code that will find negative values for Cases and subtract them to the cases from the previous week, if that weeks cases then become negative it will subtract them from the week before that and so on until the case count isn't negative anymore and fill in these new values of case in a new variable.  I want to do this within each region seperately but I'm having trouble determining if I need to make some sort of foreach loop, which I've never tried before of if there is a simpler way.


Dear Charlotte,

could you please clarify, whether the number of cases is
"number of cases in that week" or
"cumulative number of cases in all weeks precedeing
(and including) the current week"?

if you have weekly data then by definition what you are
looking for is the sum of all the cases (positive and negative).
E.g. if cases are patients, and positive are new patients, and
negative are patients that didn't survive, then you can find
the current number of patients in the hospital simply by
totalling the number of arriving and departing patients up
to that week.

It would be strange if the positives were cumulative stocks and
negatives were flows.

Best, Sergiy Radyakin

>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
>

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
Reply | Threaded
Open this post in threaded view
|

Re: replacing previous observations according to current observation, foreach? replace?

Charlotte Picard
In reply to this post by Charlotte Picard
Eric, Thank you so much for your input, that was what I was looking for however when I tried to run your code with the example data I am getting an invalid syntax error.  I can't seem to find what is wrong.  Any suggestions?  Thanks again.








On Dec 2, 2010, at 1:14 PM, Charlotte Picard wrote:
> I am trying to create code that will find negative values for Cases and subtract them to the cases from the previous week, if that weeks cases then become negative it will subtract them from the week before that and so on until the case count isn't negative anymore and fill in these new values of case in a new variable.


I'm not sure I completely understand.  An example of what you want this new variable to look like would be helpful.

Below is some code that does what I _think_ you are asking for -- that is, when "cases" value is negative, it adds that negative "cases" value to the previous week's "cases" value within (-by-) each region and puts that new value in a new variable ("cases2").  If "cases2" is still not positive after being added to the prior week's "case" value, then the negative "case2" is added to "cases" from to 2 weeks prior and so on until "case2" is >= 0 or the number of weeks runs out.

I changed the value in region 4, week 3 to show an example of a negative value that never goes positive because it runs out of weeks to go back in time.

Look at the changes in the "list" command at each step of the loop to see that changes that are being made to "cases2".  Only regions 2 and 4 are affected because the others do not have negative "cases".

*******!
clear
inp region week cases
1             1       5
2             1       4
3             1       0
4             1       2
1             2       3
2             2       2
3             2       1
4             2       0
1             3       2
2             3      -3
3             3       0
4             3      -4
end


sort region week
clonevar cases2 = cases

**compare tables in the loop to this table**
l if inlist(region, 2, 4), sepby(region)

qui su week, d
forval n = 1/`r(max)' {
by region: replace cases2 = cases[_n-`n']+cases2  ///
        if cases2<0 & region[_n] == region[_n-`n']
di in r "Changes based on `n' Weeks Prior to Week `n' -->"
li if inlist(region, 2, 4) , sepby(region)
}

*******!

- Eric

__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
[hidden email]
Office: +979.845.6754


On Dec 2, 2010, at 1:14 PM, Charlotte Picard wrote:

> Hello Stata users,
> I am a new stata user and I am trying to work backwards in my data.
>
> Region Week Cases
> 1             1       5
> 2             1       4
> 3             1       0
> 4             1       2
> 1             2       3
> 2             2       2
> 3             2       1
> 4             2       0
> 1             3       2
> 2             3      -3
> 3             3       0
> 4             3       4
>
> I am trying to create code that will find negative values for Cases and subtract them to the cases from the previous week, if that weeks cases then become negative it will subtract them from the week before that and so on until the case count isn't negative anymore and fill in these new values of case in a new variable.  I want to do this within each region seperately but I'm having trouble determining if I need to make some sort of foreach loop, which I've never tried before of if there is a simpler way.
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/




*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
Reply | Threaded
Open this post in threaded view
|

Re: replacing previous observations according to current observation, foreach? replace?

Eric A. Booth
<>

Not without a more detailed description of what lines are running when the error occurred. First, what version of Stata do you have and what platform are you running it on?
It runs fine on my machine (Stata 11 MP, Mac OSX 10.6), so my best guesses are that:
 (1) there's some kind of wrapping issue (that is, the code wrapped in your email program and needs to be unwrapped -- maybe you should try copying/pasting it from here:  http://www.stata.com/statalist/archive/2010-12/msg00105.html )  
or
(2) you've got an older version of Stata for which some command in my example doesn't work or has changed syntax.

Finally, if none of these fix your issue, try setting -trace on- before running the code and report to us the lines where Stata chokes.  

- Eric


__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
[hidden email]
Office: +979.845.6754
Fax: +979.845.0249
http://ppri.tamu.edu


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
Reply | Threaded
Open this post in threaded view
|

RE: replacing previous observations according to current observation, foreach? replace?

Charlotte Picard
I am using Stata 11 IC, (64 bit Windows)

I tried copying and pasting the code from the website and still got the same issue.  It seems that the error occurs at the 2nd line of the loop.  I set trace on and got this:

. *******!
.
. clear
--------------------------------------------------------------------------------- begin clear ---
- if _caller() < 10 {
  _clear_9 `0'
  exit
  }
- version 10
- syntax [anything]
- tokenize `anything'
= tokenize
- if `"`2'"' != "" {
= if `""' != "" {
  display as err "`2' not allowed"
  exit 198
  }
- if "`1'"=="" {
= if ""=="" {
- drop _all
- label drop _all
  ------------------------------------------------------------------------------- begin label ---
  - version 10.0
  - gettoken val : 0
  - if (strpos("`val'", "val") > 0 ) {
  = if (strpos("drop", "val") > 0 ) {
    gettoken val 0 : 0
    syntax anything [, nofix]
    if "`fix'" != "" {
    local fix ", nofix"
    }
    gettoken var rest : anything
    while `"`rest'"' != "" {
    gettoken lab rest : rest
    local label "`lab'"
    }
    local vlist : list anything - lab
    if "`lab'" == "." {
    local lab ""
    }
    foreach var of varlist `vlist' {
    _label `val' `var' `lab' `fix'
    }
    }
  - else {
  - _label `macval(0)'
  = _label drop _all
  - }
  --------------------------------------------------------------------------------- end label ---
- }
- else if "`1'"=="mata" {
= else if ""=="mata" {
  mata: mata clear
  }
- else if inlist("`1'", "results", "matrix") {
= else if inlist("", "results", "matrix") {
  return clear
  clearreturn
  ereturn clear
  sreturn clear
  _return drop _all
  if ("`1'" == "matrix") {
  matrix drop _all
  _est drop _all
  }
  }
- else if "`1'"=="programs" {
= else if ""=="programs" {
  program drop _all
  }
- else if "`1'"=="ado" {
= else if ""=="ado" {
  program drop _allado
  }
- else if "`1'"=="*" | "`1'"=="all" {
= else if ""=="*" | ""=="all" {
  drop _all
  label drop _all
  matrix drop _all
  scalar drop _all
  constraint drop _all
  eq drop _all
  file close _all
  postutil clear
  _return drop _all
  discard
  program drop _all
  timer clear
  mata: mata clear
  }
- else {
  display as err "`1' not allowed"
  exit 198
  }
----------------------------------------------------------------------------------- end clear ---
.
. inp region week cases
        region       week      cases
  1.
. 1             1       5
  2.
. 2             1       4
  3.
. 3             1       0
  4.
. 4             1       2
  5.
. 1             2       3
  6.
. 2             2       2
  7.
. 3             2       1
  8.
. 4             2       0
  9.
. 1             3       2
 10.
. 2             3      -3
 11.
. 3             3       0
 12.
. 4             3      -4
 13.
. end
.
.
.
.
.
. sort region week
.
. clonevar cases2 = cases
------------------------------------------------------------------------------ begin clonevar ---
- version 8.0
- gettoken newvar 0 : 0, parse("= ")
- gettoken eqs 0 : 0, parse("= ")
- gettoken varname 0 : 0
- syntax [if] [in]
- if "`eqs'" != "=" {
= if "=" != "=" {
  di "{p}{err}syntax is {cmd:clonevar {it:newvar} = {it:varname}} ...{p_end}"
  exit 198
  }
- confirm new var `newvar'
= confirm new var cases2
- confirm var `varname'
= confirm var cases
- local type : type `varname'
= local type : type cases
- gen `type' `newvar' = `varname' `if' `in'
= gen float cases2 = cases  
- local w : variable label `varname'
= local w : variable label cases
- if `"`w'"' != "" label variable `newvar' `"`w'"'
= if `""' != "" label variable cases2 `""'
- local vallbl : value label `varname'
= local vallbl : value label cases
- if "`vallbl'" != "" label val `newvar' `vallbl'
= if "" != "" label val cases2
- format `newvar' `: format `varname''
= format cases2 %9.0g
- tokenize `"`: char `varname'[]'"'
= tokenize `""'
- while `"`1'"' != "" {
= while `""' != "" {
  char `newvar'[`1'] `"`: char `varname'[`1']'"'
  mac shift
  }
-------------------------------------------------------------------------------- end clonevar ---
.
.
.
. **compare tables in the loop to this table**
.
. l if inlist(region, 2, 4), sepby(region)
     +--------------------------------+
     | region   week   cases   cases2 |
     |--------------------------------|
  4. |      2      1       4        4 |
  5. |      2      2       2        2 |
  6. |      2      3      -3       -3 |
     |--------------------------------|
 10. |      4      1       2        2 |
 11. |      4      2       0        0 |
 12. |      4      3      -4       -4 |
     +--------------------------------+
.
.
.
. qui su week, d
.
. forval n = 1/`r(max)' {
  2.
. by region: replace cases2 = cases[_n-`n']+cases2  ///
  3.
. if cases2<0 & region[_n] == region[_n-`n']
  4.
. di in r "Changes based on `n' Weeks Prior to Week `n' -->"
  5.
. li if inlist(region, 2, 4) , sepby(region)
  6.
. }
- forval n = 1/`r(max)' {
= forval n = 1/3 {
- by region: replace cases2 = cases[_n-`n']+cases2 ///
= by region: replace cases2 = cases[_n-1]+cases2 ///
invalid syntax
  if cases2<0 & region[_n] == region[_n-`n']
  di in r "Changes based on `n' Weeks Prior to Week `n' -->"
  li if inlist(region, 2, 4) , sepby(region)
  }
r(198);
.
.
.
. *******!
.

________________________________________

From: [hidden email] [[hidden email]] On Behalf Of Eric Booth [[hidden email]]
Sent: Sunday, December 05, 2010 10:10 PM
To: <[hidden email]>
Subject: Re: st: replacing previous observations according to current observation, foreach? replace?

<>

Not without a more detailed description of what lines are running when the error occurred. First, what version of Stata do you have and what platform are you running it on?
It runs fine on my machine (Stata 11 MP, Mac OSX 10.6), so my best guesses are that:
 (1) there's some kind of wrapping issue (that is, the code wrapped in your email program and needs to be unwrapped -- maybe you should try copying/pasting it from here:  http://www.stata.com/statalist/archive/2010-12/msg00105.html )
or
(2) you've got an older version of Stata for which some command in my example doesn't work or has changed syntax.

Finally, if none of these fix your issue, try setting -trace on- before running the code and report to us the lines where Stata chokes.

- Eric


__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
[hidden email]
Office: +979.845.6754
Fax: +979.845.0249
http://ppri.tamu.edu


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
Reply | Threaded
Open this post in threaded view
|

RE: replacing previous observations according to current observation, foreach? replace?

Nick Cox
The continuation bracket /// can not be used interactively, only from a do-file or program.

Nick
[hidden email]

Charlotte Picard

I am using Stata 11 IC, (64 bit Windows)

I tried copying and pasting the code from the website and still got the same issue.  It seems that the error occurs at the 2nd line of the loop.  I set trace on and got this:

[snip]

- by region: replace cases2 = cases[_n-`n']+cases2 ///
= by region: replace cases2 = cases[_n-1]+cases2 ///
invalid syntax
  if cases2<0 & region[_n] == region[_n-`n']
  di in r "Changes based on `n' Weeks Prior to Week `n' -->"
  li if inlist(region, 2, 4) , sepby(region)
  }
r(198);

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/