LOOP calculations

[Kylie]

Hi everyone,

 

I have a dataset with 19 annual numeric variables (Var1 TO Var19). I need the maximum value, the year of the maximum value (ie, 1 TO 19), the difference between that maximum value and the minimum value that occurs AFTER the maximum value (termed the ‘rebound’), and the year of this rebound value.

 

The max value and year of the maximum value were straightforward and are stored in VarMax and VarMax.year, and I have the following loop to calculate the rebound (VarRebound) and year of rebound (VarRebound.year).

 

VECTOR X=Var1 TO Var19.

COMPUTE VarRebound = -1.

COMPUTE VarRebound.year = -1.

LOOP #Ind=1 TO 19.

  DO IF #Ind GT VarMax.year.

     IF ((VarMax - X(#Ind)) GT VarRebound) VarRebound.year=#Ind.

     COMPUTE VarRebound = MAX(VarRebound, VarMax - X(#Ind)).

 END IF.

END LOOP.

 

The above works correctly for the rebound. The year is also correct EXCEPT when the rebound occurs immediately after VarMax – in that case the result is still -1 (missing). So I assume that I am out somewhere with the indices or executes or something fiddly and/or obvious like that. Any suggestions would be appreciated!

 

Kylie.

 

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[David Marso]

These days I would likely butcher it with VARSTOCASES, AGGREGATE to get the MAX, Select everything following and then aggregate to get the MIN.  Then just match it back.

UNTESTED:
DATASET NAME raw.
DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=casecount.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Max=MAX(var ).
IF (var  EQ MAX ) #year=Year.
SELECT IF Year GE #year.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Min=MIN(var ).

Then just grab the two records of interest, flatten with CASESTOVARS and merge with raw.

[Bruce Weaver]

If I understood what Kylie is trying to do, the AGGREGATE commands need to BREAK by ID.  Here's a modified version of David's untested syntax (with some output).

* Generate a small dataset for testing.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST FREE / ID (F2.0).
BEGIN DATA
1 2 3 4 5
END DATA.
DATASET NAME raw.
VECTOR Var(19).
LOOP # = 1 to 19.
- COMPUTE Var(#) = RV.NORMAL(50,10).
END LOOP.

* Set Var1 = 99 for ID=1 and Var19=99 for ID=2.
IF ID EQ 1 Var1  = 99.
IF ID EQ 2 Var19 = 99.

DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=Year.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Max=MAX(var).
IF (var  EQ MAX ) MaxYear=Year.
IF ID EQ LAG(ID) AND MISSING(MaxYear) MaxYear = LAG(MaxYear).
SELECT IF Year GE MaxYear.
EXECUTE.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Min=MIN(var).
IF (var  EQ Min ) MinYear=Year.
SELECT IF NOT MISSING(MaxYear). /* OR NOT MISSING(MinYear).
EXECUTE.

DATASET DECLARE toMerge.
AGGREGATE
  /OUTFILE= 'toMerge'
  /BREAK=ID
  /MaxValue=FIRST(Max)
  /MinValue=FIRST(Min)
  /MaxYear=FIRST(MaxYear)
  /MinYear=FIRST(MinYear).

MATCH FILES
 FILE = 'raw' /
 FILE = 'toMerge' /
 BY ID.
EXECUTE.
DATASET NAME Final.
DATASET ACTIVATE FINAL.
DATASET CLOSE toButcher.
DATASET CLOSE toMerge.
COMPUTE Rebound = MaxValue - MinValue.
FORMATS MaxYear MinYear (F2.0).
LIST ID MaxValue MinValue Rebound MaxYear MinYear.

OUTPUT:
ID MaxValue MinValue  Rebound MaxYear MinYear
 
 1    99.00    32.99    66.01     1      12
 2    99.00    99.00      .00    19      19
 3    66.02    32.42    33.61    16      19
 4    64.39    26.89    37.50     4       9
 5    72.37    35.63    36.74    10      17

David Marso wrote

These days I would likely butcher it with VARSTOCASES, AGGREGATE to get the MAX, Select everything following and then aggregate to get the MIN.  Then just match it back.

UNTESTED:
DATASET NAME raw.
DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=casecount.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Max=MAX(var ).
IF (var  EQ MAX ) #year=Year.
SELECT IF Year GE #year.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Min=MIN(var ).

Then just grab the two records of interest, flatten with CASESTOVARS and merge with raw.

[David Marso]

Good catch Bruce,

Need to suck some coffee before posting.

On Fri, Jun 9, 2017 at 10:10 AM, Bruce Weaver [via SPSSX Discussion] <[hidden email]> wrote:

If I understood what Kylie is trying to do, the AGGREGATE commands need to BREAK by ID.  Here's a modified version of David's untested syntax (with some output).

* Generate a small dataset for testing.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST FREE / ID (F2.0).
BEGIN DATA
1 2 3 4 5
END DATA.
DATASET NAME raw.
VECTOR Var(19).
LOOP # = 1 to 19.
- COMPUTE Var(#) = RV.NORMAL(50,10).
END LOOP.

* Set Var1 = 99 for ID=1 and Var19=99 for ID=2.
IF ID EQ 1 Var1  = 99.
IF ID EQ 2 Var19 = 99.

DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=Year.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Max=MAX(var).
IF (var  EQ MAX ) MaxYear=Year.
IF ID EQ LAG(ID) AND MISSING(MaxYear) MaxYear = LAG(MaxYear).
SELECT IF Year GE MaxYear.
EXECUTE.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Min=MIN(var).
IF (var  EQ Min ) MinYear=Year.
SELECT IF NOT MISSING(MaxYear). /* OR NOT MISSING(MinYear).
EXECUTE.

DATASET DECLARE toMerge.
AGGREGATE
  /OUTFILE= 'toMerge'
  /BREAK=ID
  /MaxValue=FIRST(Max)
  /MinValue=FIRST(Min)
  /MaxYear=FIRST(MaxYear)
  /MinYear=FIRST(MinYear).

MATCH FILES
 FILE = 'raw' /
 FILE = 'toMerge' /
 BY ID.
EXECUTE.
DATASET NAME Final.
DATASET ACTIVATE FINAL.
DATASET CLOSE toButcher.
DATASET CLOSE toMerge.
COMPUTE Rebound = MaxValue - MinValue.
FORMATS MaxYear MinYear (F2.0).
LIST ID MaxValue MinValue Rebound MaxYear MinYear.

OUTPUT:
ID MaxValue MinValue  Rebound MaxYear MinYear
 
 1    99.00    32.99    66.01     1      12
 2    99.00    99.00      .00    19      19
 3    66.02    32.42    33.61    16      19
 4    64.39    26.89    37.50     4       9
 5    72.37    35.63    36.74    10      17

David Marso wrote

These days I would likely butcher it with VARSTOCASES, AGGREGATE to get the MAX, Select everything following and then aggregate to get the MIN.  Then just match it back.

UNTESTED:
DATASET NAME raw.
DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=casecount.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Max=MAX(var ).
IF (var  EQ MAX ) #year=Year.
SELECT IF Year GE #year.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Min=MIN(var ).

Then just grab the two records of interest, flatten with CASESTOVARS and merge with raw.

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/LOOP-calculations-tp5734384p5734394.html

To unsubscribe from LOOP calculations, click here.
NAML

[Kylie]

Thanks David and Bruce. Think I will indeed go that way as I’m not having any luck working out the problem with my initial approach.

 

Kylie.

 

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso
Sent: Saturday, 10 June 2017 12:19 AM
To: [hidden email]
Subject: Re: LOOP calculations

 

Good catch Bruce,

Need to suck some coffee before posting.

 

On Fri, Jun 9, 2017 at 10:10 AM, Bruce Weaver [via SPSSX Discussion] <[hidden email]> wrote:

If I understood what Kylie is trying to do, the AGGREGATE commands need to BREAK by ID.  Here's a modified version of David's untested syntax (with some output).

* Generate a small dataset for testing.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST FREE / ID (F2.0).
BEGIN DATA
1 2 3 4 5
END DATA.
DATASET NAME raw.
VECTOR Var(19).
LOOP # = 1 to 19.
- COMPUTE Var(#) = RV.NORMAL(50,10).
END LOOP.

* Set Var1 = 99 for ID=1 and Var19=99 for ID=2.
IF ID EQ 1 Var1  = 99.
IF ID EQ 2 Var19 = 99.

DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=Year.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Max=MAX(var).
IF (var  EQ MAX ) MaxYear=Year.
IF ID EQ LAG(ID) AND MISSING(MaxYear) MaxYear = LAG(MaxYear).
SELECT IF Year GE MaxYear.
EXECUTE.
AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Min=MIN(var).
IF (var  EQ Min ) MinYear=Year.
SELECT IF NOT MISSING(MaxYear). /* OR NOT MISSING(MinYear).
EXECUTE.

DATASET DECLARE toMerge.
AGGREGATE
  /OUTFILE= 'toMerge'
  /BREAK=ID
  /MaxValue=FIRST(Max)
  /MinValue=FIRST(Min)
  /MaxYear=FIRST(MaxYear)
  /MinYear=FIRST(MinYear).

MATCH FILES
 FILE = 'raw' /
 FILE = 'toMerge' /
 BY ID.
EXECUTE.
DATASET NAME Final.
DATASET ACTIVATE FINAL.
DATASET CLOSE toButcher.
DATASET CLOSE toMerge.
COMPUTE Rebound = MaxValue - MinValue.
FORMATS MaxYear MinYear (F2.0).
LIST ID MaxValue MinValue Rebound MaxYear MinYear.

OUTPUT:
ID MaxValue MinValue  Rebound MaxYear MinYear
 
 1    99.00    32.99    66.01     1      12
 2    99.00    99.00      .00    19      19
 3    66.02    32.42    33.61    16      19
 4    64.39    26.89    37.50     4       9
 5    72.37    35.63    36.74    10      17

David Marso wrote

These days I would likely butcher it with VARSTOCASES, AGGREGATE to get the MAX, Select everything following and then aggregate to get the MIN.  Then just match it back.

UNTESTED:
DATASET NAME raw.
DATASET COPY toButcher.
DATASET ACTIVATE toButcher.

VARSTOCASES /MAKE var FROM Var1 TO Var19 /INDEX=casecount.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Max=MAX(var ).
IF (var  EQ MAX ) #year=Year.
SELECT IF Year GE #year.
AGGREGATE OUTFILE  * MODE ADDVARIABLES / Min=MIN(var ).

Then just grab the two records of interest, flatten with CASESTOVARS and merge with raw.

--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

 

---

If you reply to this email, your message will be added to the discussion below:

http://spssx-discussion.1045642.n5.nabble.com/LOOP-calculations-tp5734384p5734394.html

To unsubscribe from LOOP calculations, click here.
NAML

 

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

 

---

View this message in context: Re: LOOP calculations
Sent from the SPSSX Discussion mailing list archive at Nabble.com.
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD