Pulling students with various combinations of test scores

[Debbie Hahs-Vaughn]

I am working with data from students that have up to 9 months of data, potentially starting in September and running through May.  I have to pull only those students who had a minimum of one assignment in each of the following months’ groupings. If there are multiple scores in the grouping, I need to use the first score for the beginning and subsequent mid-year time periods and use the last score on the final time period if available. For example, if a student has both September and October scores, I need to pull only the September score. However, if a student has only October (and not Sept.), then I pull only October.

If a student has both Nov. and Dec. scores I need to pull only November; but if they don't have November, I pull December.  If a student has both Jan. and Feb., I pull only January; but if they don't have January, I pull February.  If a student has March, April, and/or May, I pull only May if they have it.  If they have March and April, I pull April.  If they have only March, I pull March.

1.  (Sept/Oct)
2. (Nov/Dec)
3. (Jan/Feb)
4. (March/April/May)

Any suggestions are greatly appreciated.

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

[Bruce Weaver]

How is your data file formatted?  A LONG file format would work well here (e.g., with variables ID, DATE & SCORE, and multiple rows per ID).  Assuming a LONG format and a real DATE variable on each row...

1. Look up the XDATE.MONTH function.  It returns a number from 1-12, but you can recode that into a new variable giving your 'groupings':

COMPUTE Month = XDATE.MONTH(DATE).
RECODE Month
 (9 10 = 1)
 (11 12 = 2)
 (1 2 = 3)
 (3 4 5 = 4)
 (ELSE = 88) into Period.
VALUE LABELS Period
 1 "Sept/Oct"
 2 "Nov/Dec"
 3 "Jan/Feb"
 4 "March/April/May"
 88 "Other"
.
FORMATS Month Period (F2.0).

2.  Look up AGGREGATE.  To get the first score in each time period, use ID and Period as BREAK variables, and use the FIRST function to select the first score.  To get the FINAL score for each ID, do another AGGREGATE with ID as the only BREAK variable, and use the LAST function.  

HTH.

Debbie Hahs-Vaughn wrote

I am working with data from students that have up to 9 months of data, potentially starting in September and running through May.  I have to pull only those students who had a minimum of one assignment in each of the following months’ groupings. If there are multiple scores in the grouping, I need to use the first score for the beginning and subsequent mid-year time periods and use the last score on the final time period if available. For example, if a student has both September and October scores, I need to pull only the September score. However, if a student has only October (and not Sept.), then I pull only October.

If a student has both Nov. and Dec. scores I need to pull only November; but if they don't have November, I pull December.  If a student has both Jan. and Feb., I pull only January; but if they don't have January, I pull February.  If a student has March, April, and/or May, I pull only May if they have it.  If they have March and April, I pull April.  If they have only March, I pull March.

1.  (Sept/Oct)
2. (Nov/Dec)
3. (Jan/Feb)
4. (March/April/May)

Any suggestions are greatly appreciated.

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