* Title: Cohort Enrollment by Term at College and Elsewhere Created: October 2014 Author: Pam Borkowski-Valentin, Swarthmore College Office of Institutional Research. * Since we are submitting our cohort with a begin search date in the fall of the cohort year, we should have records from our own institution for everyone- there should be no “N” responses in the “RecordFoundYN” variable Run a frequency to double check & investigate any “N” responses (for example, records could be blocked by student) There may be a number of warnings listed for blank cells, most notably from the graduation date variable, which, depending on the cohort & when you are running it, could be mostly blank Ex: (>Warning # 1111 A numeric field contained no digits. The result has been set to the system-missing value). FREQUENCIES VARIABLES=RecordFoundYN /ORDER=ANALYSIS. RENAME VARIABLES RequesterReturnField= ID. EXECUTE. ALTER TYPE EnrollmentBegin (A8). ALTER TYPE EnrollmentEnd (A8). ALTER TYPE SearchDate (A8). ALTER TYPE GraduationDate (A8). * Date and Time Wizard: EnrollmentBegin_Date. COMPUTE EnrollmentBegin_Date=date.dmy(number(substr(ltrim(EnrollmentBegin),7,2),f2.0), number(substr(ltrim(EnrollmentBegin),5,2),f2.0), number(substr(ltrim(EnrollmentBegin),1,4),f4.0)). VARIABLE LABELS EnrollmentBegin_Date 'Enrollment Begin'. VARIABLE LEVEL EnrollmentBegin_Date (SCALE). FORMATS EnrollmentBegin_Date (ADATE10). VARIABLE WIDTH EnrollmentBegin_Date(10). EXECUTE. * Date and Time Wizard: EnrollmentEnd_Date. COMPUTE EnrollmentEnd_Date=date.dmy(number(substr(ltrim(EnrollmentEnd),7,2),f2.0), number(substr(ltrim(EnrollmentEnd),5,2),f2.0), number(substr(ltrim(EnrollmentEnd),1,4),f4.0)). VARIABLE LABELS EnrollmentEnd_Date 'Enrollment End'. VARIABLE LEVEL EnrollmentEnd_Date (SCALE). FORMATS EnrollmentEnd_Date (ADATE10). VARIABLE WIDTH EnrollmentEnd_Date(10). EXECUTE. * Date and Time Wizard: SearchDate_Date. COMPUTE SearchDate_Date=date.dmy(number(substr(ltrim(SearchDate),7,2),f2.0), number(substr(ltrim(SearchDate),5,2),f2.0), number(substr(ltrim(SearchDate),1,4),f4.0)). VARIABLE LABELS SearchDate_Date 'Search Date'. VARIABLE LEVEL SearchDate_Date (SCALE). FORMATS SearchDate_Date (ADATE10). VARIABLE WIDTH SearchDate_Date(10). EXECUTE. * Date and Time Wizard: GraduationDate_Date. COMPUTE GraduationDate_Date=date.dmy(number(substr(ltrim(GraduationDate),7,2),f2.0), number(substr(ltrim(GraduationDate),5,2),f2.0), number(substr(ltrim(GraduationDate),1,4),f4.0)). VARIABLE LABELS GraduationDate_Date 'Graduation Date'. VARIABLE LEVEL GraduationDate_Date (SCALE). FORMATS GraduationDate_Date (ADATE10). VARIABLE WIDTH GraduationDate_Date(10). EXECUTE. * Make sure you put your name in exactly as it appears in the file for "CollegeName=" However, as SPSS syntax doesn't like spaces, use only one word or a nickname for your new variable such as "Swarthmore College" for college name and "Swarthmore_record" or "SchoolA_record" for the variable. IF (CollegeName="School A") SchoolA_record=1. RECODE SchoolA_record (MISSING=0). DO IF (RecordFoundYN="N"). RECODE SchoolA_record (0=2). END IF. EXECUTE. VALUE LABELS SchoolA_record 0 'Other School' 1 'School A' 2 'No Record Found'. Execute. * This section puts enrollment begin dates into terms. DEFINE cohort() 2007 !ENDDEFINE. IF RANGE (EnrollmentBegin_Date,DATE.MDY(08,15,cohort), DATE.MDY (12,31,cohort)) | RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort), DATE.MDY (12,31,cohort)) Term_1=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(01,01,cohort+1), DATE.MDY (04,30,cohort+1)) | RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+1), DATE.MDY (04,30,cohort+1)) Term_2=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(05,01,cohort+1), DATE.MDY (08,14,cohort+1)) | RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+1), DATE.MDY (08,14,cohort+1)) Term_3=1. IF RANGE (EnrollmentBegin_Date,DATE.MDY(08,15,cohort+1), DATE.MDY (12,31,cohort+1)) | RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+1), DATE.MDY (12,31,cohort+1)) Term_4=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(01,01,cohort+2), DATE.MDY (04,30,cohort+2)) | RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+2), DATE.MDY (04,30,cohort+2)) Term_5=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(05,01,cohort+2), DATE.MDY (08,14,cohort+2)) | RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+2), DATE.MDY (08,14,cohort+2)) Term_6=1. IF RANGE (EnrollmentBegin_Date,DATE.MDY(08,15,cohort+2), DATE.MDY (12,31,cohort+2)) | RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+2), DATE.MDY (12,31,cohort+2)) Term_7=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(01,01,cohort+3), DATE.MDY (04,30,cohort+3)) | RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+3), DATE.MDY (04,30,cohort+3)) Term_8=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(05,01,cohort+3), DATE.MDY (08,14,cohort+3)) | RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+3), DATE.MDY (08,14,cohort+3)) Term_9=1. IF RANGE (EnrollmentBegin_Date,DATE.MDY(08,15,cohort+3), DATE.MDY (12,31,cohort+3)) | RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+3), DATE.MDY (12,31,cohort+3)) Term_10=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(01,01,cohort+4), DATE.MDY (04,30,cohort+4)) | RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+4), DATE.MDY (04,30,cohort+4)) Term_11=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(05,01,cohort+4), DATE.MDY (08,14,cohort+4)) | RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+4), DATE.MDY (08,14,cohort+4)) Term_12=1. IF RANGE (EnrollmentBegin_Date,DATE.MDY(08,15,cohort+4), DATE.MDY (12,31,cohort+4)) | RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+4), DATE.MDY (12,31,cohort+4)) Term_13=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(01,01,cohort+5), DATE.MDY (04,30,cohort+5)) | RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+5), DATE.MDY (04,30,cohort+5)) Term_14=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(05,01,cohort+5), DATE.MDY (08,14,cohort+5)) | RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+5), DATE.MDY (08,14,cohort+5)) Term_15=1. IF RANGE (EnrollmentBegin_Date,DATE.MDY(08,15,cohort+5), DATE.MDY (12,31,cohort+5)) | RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+5), DATE.MDY (12,31,cohort+5)) Term_16=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(01,01,cohort+6), DATE.MDY (04,30,cohort+6)) | RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+6), DATE.MDY (04,30,cohort+6)) Term_17=1. IF RANGE(EnrollmentBegin_Date,DATE.MDY(05,01,cohort+6), DATE.MDY (08,14,cohort+6)) | RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+6), DATE.MDY (08,14,cohort+6)) Term_18=1. EXECUTE. IF RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort), DATE.MDY (12,31,cohort)) GradTerm=1. IF RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+1), DATE.MDY (04,30,cohort+1)) GradTerm=2. IF RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+1), DATE.MDY (08,14,cohort+1)) GradTerm=3. IF RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+1), DATE.MDY (12,31,cohort+1)) GradTerm=4. IF RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+2), DATE.MDY (04,30,cohort+2)) GradTerm=5. IF RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+2), DATE.MDY (08,14,cohort+2)) GradTerm=6. IF RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+2), DATE.MDY (12,31,cohort+2)) GradTerm=7. IF RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+3), DATE.MDY (04,30,cohort+3)) GradTerm=8. IF RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+3), DATE.MDY (08,14,cohort+3)) GradTerm=9. IF RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+3), DATE.MDY (12,31,cohort+3)) GradTerm=10. IF RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+4), DATE.MDY (04,30,cohort+4)) GradTerm=11. IF RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+4), DATE.MDY (08,14,cohort+4)) GradTerm=12. IF RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+4), DATE.MDY (12,31,cohort+4)) GradTerm=13. IF RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+5), DATE.MDY (04,30,cohort+5)) GradTerm=14. IF RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+5), DATE.MDY (08,14,cohort+5)) GradTerm=15. IF RANGE(GraduationDate_Date,DATE.MDY(08,15,cohort+5), DATE.MDY (12,31,cohort+5)) GradTerm=16. IF RANGE(GraduationDate_Date,DATE.MDY(01,01,cohort+6), DATE.MDY (04,30,cohort+6)) GradTerm=17. IF RANGE(GraduationDate_Date,DATE.MDY(05,01,cohort+6), DATE.MDY (08,14,cohort+6)) GradTerm=18. EXECUTE. * For some projects, it may be requested to delete any records where a person was enrolled for less than a specific period of time See other video for steps & syntax if you want to delete those who, for example, were only enrolled for 30 days or less in a fall/summer term or 10 days or less in a summer term. * We are going to examine Progression & Completion through the first graduation record We therefore want to identify the first graduation record and also identify any records that occur after that - NOTE: this keeps 4 year over 2 year graduation; change or remove the "@2year4year (D)" below if this is not what you want. RECODE @2year4year ('2'='2') ('4'='4') ('L'='1'). EXECUTE. USE ALL. COMPUTE filter_$=(Graduated="Y"). VARIABLE LABELS filter_$ 'Graduated="Y" (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. * Identify Duplicate Cases. SORT CASES BY ID(A) Graduated(A) @2year4year (D) GraduationDate_Date(A) DegreeTitle(D). MATCH FILES /FILE=* /BY ID Graduated /FIRST=PrimaryFirst_Grad_Date /LAST=PrimaryLast1. DO IF (PrimaryFirst_Grad_Date). COMPUTE MatchSequence=1-PrimaryLast1. ELSE. COMPUTE MatchSequence=MatchSequence+1. END IF. LEAVE MatchSequence. FORMATS MatchSequence (f7). COMPUTE InDupGrp=MatchSequence>0. SORT CASES InDupGrp(D). MATCH FILES /FILE=* /DROP=PrimaryLast1 InDupGrp MatchSequence. VARIABLE LABELS PrimaryFirst_Grad_Date 'Indicator of each first matching case as Primary'. VALUE LABELS PrimaryFirst_Grad_Date 0 'Duplicate Case' 1 'Primary Case'. VARIABLE LEVEL PrimaryFirst_Grad_Date (ORDINAL). FREQUENCIES VARIABLES=PrimaryFirst_Grad_Date. EXECUTE. * To check slecting the first graduation record. USE ALL. COMPUTE filter_$=(Graduated="N" | (Graduated="Y" & PrimaryFirst_Grad_Date=1)). VARIABLE LABELS filter_$ 'Graduated="N" | (Graduated="Y" & PrimaryFirst_Grad_Date=1) (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. * To delete any graduation record past the first. FILTER OFF. USE ALL. SELECT IF (Graduated="N" | (Graduated="Y" & PrimaryFirst_Grad_Date=1)). EXECUTE. * Identify & delete enrollment records after the first graduation record. COMPUTE AfterGrad=0. EXECUTE. SORT CASES BY ID. AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /PRESORTED /BREAK=ID /GradTerm_sum=SUM(GradTerm). DO IF (GradTerm_sum=1 & (Term_2=1 | Term_3=1 | Term_4=1 | Term_5=1 | Term_6=1 | Term_7=1 | Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=2 & (Term_3=1 | Term_4=1 | Term_5=1 | Term_6=1 | Term_7=1 | Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=3 & (Term_4=1 | Term_5=1 | Term_6=1 | Term_7=1 | Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=4 & (Term_5=1 | Term_6=1 | Term_7=1 | Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=5 & (Term_6=1 | Term_7=1 | Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=6 & (Term_7=1 | Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=7 & (Term_8=1 | Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=8 & (Term_9=1 | Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=9 & (Term_10=1 | Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=10 & (Term_11=1 | Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=11 & (Term_12=1 | Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=12 & (Term_13=1 | Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=13 & (Term_14=1 | Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=14 & (Term_15=1 | Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=15 & (Term_16=1 | Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=16 & (Term_17=1 | Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. DO IF (GradTerm_sum=17 & (Term_18=1)). RECODE AfterGrad (0=1). END IF. EXECUTE. * This tests what we will delete. USE ALL. COMPUTE filter_$=(AfterGrad=0). VARIABLE LABELS filter_$ 'AfterGrad=0 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. * This will delete records for terms past the first graduation term. FILTER OFF. USE ALL. SELECT IF (AfterGrad=0). EXECUTE. * While we want to keep multiple records per term for individuals who were at multiple institutions, we DON'T want to double count at each institution For example, someone may be at the same instituion in the spring 2009 term, but have start dates that differ by a week or two and therefore have 2 records for the same school Or, someone may take 2 summer sessions (all summer counts as 1 term), and someone else may take classes & graduate in the spring 2009 term and have two records In this syntax, we are unduplicating only if a person has multiple records at the same institution in the same term. * First Aggregate & unduplicate based on term & college (identifying 2+ records at the same college in a single term) We are keeping grad records over enrollment records. DEFINE !Aggregate (myvars=!CMDEND) FILTER OFF. USE ALL. EXECUTE. AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID !myvars / TempTerm_sum=SUM( !myvars). USE ALL. COMPUTE filter_$=( !myvars=1). VARIABLE LABELS filter_$ ' !myvars=1 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. !ENDDEFINE. DEFINE !DUP (myvars=!CMDEND) * Identify Duplicate Cases. SORT CASES BY ID(A) !myvars(A) CollegeName(A) Graduated(D) DegreeTitle(D). MATCH FILES /FILE=* /BY ID !myvars CollegeName /FIRST=PrimaryFirst_TempTerm_byCollege /LAST=PrimaryLast1. DO IF (PrimaryFirst_TempTerm_byCollege). COMPUTE MatchSequence=1-PrimaryLast1. ELSE. COMPUTE MatchSequence=MatchSequence+1. END IF. LEAVE MatchSequence. FORMATS MatchSequence (f7). COMPUTE InDupGrp=MatchSequence>0. SORT CASES InDupGrp(D). MATCH FILES /FILE=* /DROP=PrimaryLast1 InDupGrp MatchSequence. VARIABLE LABELS PrimaryFirst_TempTerm_byCollege 'Indicator of each first matching case as Primary'. VALUE LABELS PrimaryFirst_TempTerm_byCollege 0 'Duplicate Case' 1 'Primary Case'. VARIABLE LEVEL PrimaryFirst_TempTerm_byCollege (ORDINAL). FREQUENCIES VARIABLES=PrimaryFirst_TempTerm_byCollege. EXECUTE. !ENDDEFINE. SORT CASES BY ID(A). !Aggregate myvars=Term_1. !DUP myvars=Term_1. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_1_sum PrimaryFirst_Term_1_byCollege). !Aggregate myvars=Term_2. !DUP myvars=Term_2. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_2_sum PrimaryFirst_Term_2_byCollege). !Aggregate myvars=Term_3. !DUP myvars=Term_3. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_3_sum PrimaryFirst_Term_3_byCollege). !Aggregate myvars=Term_4. !DUP myvars=Term_4. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_4_sum PrimaryFirst_Term_4_byCollege). !Aggregate myvars=Term_5. !DUP myvars=Term_5. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_5_sum PrimaryFirst_Term_5_byCollege). !Aggregate myvars=Term_6. !DUP myvars=Term_6. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_6_sum PrimaryFirst_Term_6_byCollege). !Aggregate myvars=Term_7. !DUP myvars=Term_7. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_7_sum PrimaryFirst_Term_7_byCollege). !Aggregate myvars=Term_8. !DUP myvars=Term_8. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_8_sum PrimaryFirst_Term_8_byCollege). !Aggregate myvars=Term_9. !DUP myvars=Term_9. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_9_sum PrimaryFirst_Term_9_byCollege). !Aggregate myvars=Term_10. !DUP myvars=Term_10. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_10_sum PrimaryFirst_Term_10_byCollege). !Aggregate myvars=Term_11. !DUP myvars=Term_11. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_11_sum PrimaryFirst_Term_11_byCollege). !Aggregate myvars=Term_12. !DUP myvars=Term_12. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_12_sum PrimaryFirst_Term_12_byCollege). !Aggregate myvars=Term_13. !DUP myvars=Term_13. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_13_sum PrimaryFirst_Term_13_byCollege). !Aggregate myvars=Term_14. !DUP myvars=Term_14. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_14_sum PrimaryFirst_Term_14_byCollege). !Aggregate myvars=Term_15. !DUP myvars=Term_15. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_15_sum PrimaryFirst_Term_15_byCollege). !Aggregate myvars=Term_16. !DUP myvars=Term_16. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_16_sum PrimaryFirst_Term_16_byCollege). !Aggregate myvars=Term_17. !DUP myvars=Term_17. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_17_sum PrimaryFirst_Term_17_byCollege). !Aggregate myvars=Term_18. !DUP myvars=Term_18. RENAME VARIABLES (TempTerm_sum PrimaryFirst_TempTerm_byCollege =Term_18_sum PrimaryFirst_Term_18_byCollege). FILTER OFF. USE ALL. EXECUTE. * This section creates the variable (ToDelete) that marks which same school & same term duplicate records we want to delete. IF (Term_1_sum >= 2 & PrimaryFirst_Term_1_byCollege=0) ToDelete=1. EXECUTE. DO IF (Term_2_sum >= 2 & PrimaryFirst_Term_2_byCollege=0) | (Term_3_sum >= 2 & PrimaryFirst_Term_3_byCollege=0) | (Term_4_sum >= 2 & PrimaryFirst_Term_4_byCollege=0) | (Term_5_sum >= 2 & PrimaryFirst_Term_5_byCollege=0) | (Term_6_sum >= 2 & PrimaryFirst_Term_6_byCollege=0) | (Term_7_sum >= 2 & PrimaryFirst_Term_7_byCollege=0) | (Term_8_sum >= 2 & PrimaryFirst_Term_8_byCollege=0) | (Term_9_sum >= 2 & PrimaryFirst_Term_9_byCollege=0) | (Term_10_sum >= 2 & PrimaryFirst_Term_10_byCollege=0) | (Term_11_sum >= 2 & PrimaryFirst_Term_11_byCollege=0) | (Term_12_sum >= 2 & PrimaryFirst_Term_12_byCollege=0) | (Term_13_sum >= 2 & PrimaryFirst_Term_13_byCollege=0) | (Term_14_sum >= 2 & PrimaryFirst_Term_14_byCollege=0) | (Term_15_sum >= 2 & PrimaryFirst_Term_15_byCollege=0) | (Term_16_sum >= 2 & PrimaryFirst_Term_16_byCollege=0) | (Term_17_sum >= 2 & PrimaryFirst_Term_17_byCollege=0) | (Term_18_sum >= 2 & PrimaryFirst_Term_18_byCollege=0). RECODE ToDelete (ELSE=1). END IF. EXECUTE. RECODE ToDelete (1=1) (MISSING=0). EXECUTE. * The following syntax deletes the duplicate record(s) for the same school AND the same term. FILTER OFF. USE ALL. SELECT IF (ToDelete=0). EXECUTE. * NOTE- as deleted the multiple records that were at the SAME institution, need to re-run the term sum variable since some will now have decreased In order to do this, we need to rename the existing sum variables so that we can re-run the syntax Also feel free to just delete the older variables (I've commented out the deletion, so feel free to run the Deletes instead of the Rename) (we can leave the PrimaryFirst_Term_X_byCollege alone because we're not using it again). RENAME VARIABLES (Term_1_sum Term_2_sum Term_3_sum Term_4_sum Term_5_sum Term_6_sum Term_7_sum Term_8_sum Term_9_sum Term_10_sum Term_11_sum Term_12_sum Term_13_sum Term_14_sum Term_15_sum Term_16_sum Term_17_sum Term_18_sum = Term_1_sum_old Term_2_sum_old Term_3_sum_old Term_4_sum_old Term_5_sum_old Term_6_sum_old Term_7_sum_old Term_8_sum_old Term_9_sum_old Term_10_sum_old Term_11_sum_old Term_12_sum_old Term_13_sum_old Term_14_sum_old Term_15_sum_old Term_16_sum_old Term_17_sum_old Term_18_sum_old). *DELETE VARIABLES Term_1_sum Term_2_sum Term_3_sum Term_4_sum Term_5_sum Term_6_sum Term_7_sum Term_8_sum Term_9_sum Term_10_sum Term_11_sum Term_12_sum Term_13_sum Term_14_sum Term_15_sum Term_16_sum Term_17_sum Term_18_sum. * This section identifies the number of those who were enrolled at more than one institution in a term. DEFINE !Aggregate (myvars=!CMDEND) FILTER OFF. USE ALL. EXECUTE. AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID !myvars / TempTerm_sum=SUM( !myvars). USE ALL. COMPUTE filter_$=( !myvars=1). VARIABLE LABELS filter_$ ' !myvars=1 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. !ENDDEFINE. SORT CASES BY ID(A). !Aggregate myvars=Term_1. RENAME VARIABLES (TempTerm_sum =Term_1_sum). !Aggregate myvars=Term_2. RENAME VARIABLES (TempTerm_sum =Term_2_sum). !Aggregate myvars=Term_3. RENAME VARIABLES (TempTerm_sum =Term_3_sum). !Aggregate myvars=Term_4. RENAME VARIABLES (TempTerm_sum =Term_4_sum). !Aggregate myvars=Term_5. RENAME VARIABLES (TempTerm_sum =Term_5_sum). !Aggregate myvars=Term_6. RENAME VARIABLES (TempTerm_sum =Term_6_sum). !Aggregate myvars=Term_7. RENAME VARIABLES (TempTerm_sum =Term_7_sum). !Aggregate myvars=Term_8. RENAME VARIABLES (TempTerm_sum =Term_8_sum). !Aggregate myvars=Term_9. RENAME VARIABLES (TempTerm_sum =Term_9_sum). !Aggregate myvars=Term_10. RENAME VARIABLES (TempTerm_sum =Term_10_sum). !Aggregate myvars=Term_11. RENAME VARIABLES (TempTerm_sum =Term_11_sum). !Aggregate myvars=Term_12. RENAME VARIABLES (TempTerm_sum =Term_12_sum). !Aggregate myvars=Term_13. RENAME VARIABLES (TempTerm_sum =Term_13_sum). !Aggregate myvars=Term_14. RENAME VARIABLES (TempTerm_sum =Term_14_sum). !Aggregate myvars=Term_15. RENAME VARIABLES (TempTerm_sum =Term_15_sum). !Aggregate myvars=Term_16. RENAME VARIABLES (TempTerm_sum =Term_16_sum). !Aggregate myvars=Term_17. RENAME VARIABLES (TempTerm_sum =Term_17_sum). !Aggregate myvars=Term_18. RENAME VARIABLES (TempTerm_sum =Term_18_sum). FILTER OFF. USE ALL. EXECUTE. * This section identifies the duplicates by Term & ID (NOT by College too) This counts the number of different schools per term. DEFINE !DUPbyTerm (myvars=!CMDEND) * Identify Duplicate Cases. SORT CASES BY ID(A) !myvars(A). MATCH FILES /FILE=* /BY ID !myvars /FIRST=PrimaryFirst /LAST=PrimaryLast_TempTerm. DO IF (PrimaryFirst). COMPUTE MatchSequence=1-PrimaryLast_TempTerm. ELSE. COMPUTE MatchSequence=MatchSequence+1. END IF. LEAVE MatchSequence. FORMATS MatchSequence (f7). COMPUTE InDupGrp=MatchSequence>0. SORT CASES InDupGrp(D). MATCH FILES /FILE=* /DROP=PrimaryFirst InDupGrp MatchSequence. VARIABLE LABELS PrimaryLast_TempTerm 'Indicator of each last matching case as Primary'. VALUE LABELS PrimaryLast_TempTerm 0 'Duplicate Case' 1 'Primary Case'. VARIABLE LEVEL PrimaryLast_TempTerm (ORDINAL). EXECUTE. !ENDDEFINE. !DUPbyTerm myvars=Term_1. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_1_byTerm). !DUPbyTerm myvars=Term_2. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_2_byTerm). !DUPbyTerm myvars=Term_3. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_3_byTerm). !DUPbyTerm myvars=Term_4. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_4_byTerm). !DUPbyTerm myvars=Term_5. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_5_byTerm). !DUPbyTerm myvars=Term_6. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_6_byTerm). !DUPbyTerm myvars=Term_7. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_7_byTerm). !DUPbyTerm myvars=Term_8. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_8_byTerm). !DUPbyTerm myvars=Term_9. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_9_byTerm). !DUPbyTerm myvars=Term_10. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_10_byTerm). !DUPbyTerm myvars=Term_11. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_11_byTerm). !DUPbyTerm myvars=Term_12. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_12_byTerm). !DUPbyTerm myvars=Term_13. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_13_byTerm). !DUPbyTerm myvars=Term_14. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_14_byTerm). !DUPbyTerm myvars=Term_15. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_15_byTerm). !DUPbyTerm myvars=Term_16. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_16_byTerm). !DUPbyTerm myvars=Term_17. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_17_byTerm). !DUPbyTerm myvars=Term_18. RENAME VARIABLES ( PrimaryLast_TempTerm= PrimaryLast_Term_18_byTerm). USE ALL. IF (Term_1_sum > 1 & PrimaryLast_Term_1_byTerm=1) Term_1_DuplicateRecords=Term_1_sum-1. IF (Term_2_sum > 1 & PrimaryLast_Term_2_byTerm=1) Term_2_DuplicateRecords=Term_2_sum-1. IF (Term_3_sum > 1 & PrimaryLast_Term_3_byTerm=1) Term_3_DuplicateRecords=Term_3_sum-1. IF (Term_4_sum > 1 & PrimaryLast_Term_4_byTerm=1) Term_4_DuplicateRecords=Term_4_sum-1. IF (Term_5_sum > 1 & PrimaryLast_Term_5_byTerm=1) Term_5_DuplicateRecords=Term_5_sum-1. IF (Term_6_sum > 1 & PrimaryLast_Term_6_byTerm=1) Term_6_DuplicateRecords=Term_6_sum-1. IF (Term_7_sum > 1 & PrimaryLast_Term_7_byTerm=1) Term_7_DuplicateRecords=Term_7_sum-1. IF (Term_8_sum > 1 & PrimaryLast_Term_8_byTerm=1) Term_8_DuplicateRecords=Term_8_sum-1. IF (Term_9_sum > 1 & PrimaryLast_Term_9_byTerm=1) Term_9_DuplicateRecords=Term_9_sum-1. IF (Term_10_sum > 1 & PrimaryLast_Term_10_byTerm=1) Term_10_DuplicateRecords=Term_10_sum-1. IF (Term_11_sum > 1 & PrimaryLast_Term_11_byTerm=1) Term_11_DuplicateRecords=Term_11_sum-1. IF (Term_12_sum > 1 & PrimaryLast_Term_12_byTerm=1) Term_12_DuplicateRecords=Term_12_sum-1. IF (Term_13_sum > 1 & PrimaryLast_Term_13_byTerm=1) Term_13_DuplicateRecords=Term_13_sum-1. IF (Term_14_sum > 1 & PrimaryLast_Term_14_byTerm=1) Term_14_DuplicateRecords=Term_14_sum-1. IF (Term_15_sum > 1 & PrimaryLast_Term_15_byTerm=1) Term_15_DuplicateRecords=Term_15_sum-1. IF (Term_16_sum > 1 & PrimaryLast_Term_16_byTerm=1) Term_16_DuplicateRecords=Term_16_sum-1. IF (Term_17_sum > 1 & PrimaryLast_Term_17_byTerm=1) Term_17_DuplicateRecords=Term_17_sum-1. IF (Term_18_sum > 1 & PrimaryLast_Term_18_byTerm=1) Term_18_DuplicateRecords=Term_18_sum-1. EXECUTE. VALUE LABELS Term_1_DuplicateRecords Term_2_DuplicateRecords Term_3_DuplicateRecords Term_4_DuplicateRecords Term_5_DuplicateRecords Term_6_DuplicateRecords Term_7_DuplicateRecords Term_8_DuplicateRecords Term_9_DuplicateRecords Term_10_DuplicateRecords Term_11_DuplicateRecords Term_12_DuplicateRecords Term_13_DuplicateRecords Term_14_DuplicateRecords Term_15_DuplicateRecords Term_16_DuplicateRecords Term_17_DuplicateRecords Term_18_DuplicateRecords 1 '1 additional record in term at School A & Other School' 1.5 '1 additional record in term; both at Other School' 2 '2 additional records, including 1 at School A' 2.5 '2 additional records, all at Other School' 3 '3 additional records, including 1 at School A' 3.5 '3 additional records, all at Other School' 4 '4 additional records, including 1 at School A' 4.5 '4 additional records, all at Other School' 5 '5 additional records, including 1 at School A' 5.5 '5 additional records, all at Other School'. * Although we added 1.5, 2.5 etc in the value lables above, we haven't recoded into those values yet This next section recodes the Term_X_DuplicateRecords variables to determine whether or not the term duplicate records are ONLY at others schools or if they are at your school and other schools as well. DEFINE !SchoolARecSum (myvars=!CMDEND) AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID !myvars /SchoolA_record_sum=SUM(SchoolA_record). !ENDDEFINE. DEFINE !SelectTerm (myvars=!CMDEND) USE ALL. COMPUTE filter_$=(!myvars=1). VARIABLE LABELS filter_$ '!myvars=1 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. !ENDDEFINE. !SelectTerm myvars=Term_1. !SchoolARecSum myvars=Term_1. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_1). DO IF (SchoolA_record_sum_Term_1=0). RECODE Term_1_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_2. !SchoolARecSum myvars=Term_2. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_2). DO IF (SchoolA_record_sum_Term_2=0). RECODE Term_2_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_3. !SchoolARecSum myvars=Term_3. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_3). DO IF (SchoolA_record_sum_Term_3=0). RECODE Term_3_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_4. !SchoolARecSum myvars=Term_4. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_4). DO IF (SchoolA_record_sum_Term_4=0). RECODE Term_4_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_5. !SchoolARecSum myvars=Term_5. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_5). DO IF (SchoolA_record_sum_Term_5=0). RECODE Term_5_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_6. !SchoolARecSum myvars=Term_6. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_6). DO IF (SchoolA_record_sum_Term_6=0). RECODE Term_6_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_7. !SchoolARecSum myvars=Term_7. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_7). DO IF (SchoolA_record_sum_Term_7=0). RECODE Term_7_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_8. !SchoolARecSum myvars=Term_8. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_8). DO IF (SchoolA_record_sum_Term_8=0). RECODE Term_8_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_9. !SchoolARecSum myvars=Term_9. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_9). DO IF (SchoolA_record_sum_Term_9=0). RECODE Term_9_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_10. !SchoolARecSum myvars=Term_10. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_10). DO IF (SchoolA_record_sum_Term_10=0). RECODE Term_10_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_11. !SchoolARecSum myvars=Term_11. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_11). DO IF (SchoolA_record_sum_Term_11=0). RECODE Term_11_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_12. !SchoolARecSum myvars=Term_12. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_12). DO IF (SchoolA_record_sum_Term_12=0). RECODE Term_12_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_13. !SchoolARecSum myvars=Term_13. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_13). DO IF (SchoolA_record_sum_Term_13=0). RECODE Term_13_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_14. !SchoolARecSum myvars=Term_14. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_14). DO IF (SchoolA_record_sum_Term_14=0). RECODE Term_14_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_15. !SchoolARecSum myvars=Term_15. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_15). DO IF (SchoolA_record_sum_Term_15=0). RECODE Term_15_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_16. !SchoolARecSum myvars=Term_16. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_16). DO IF (SchoolA_record_sum_Term_16=0). RECODE Term_16_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_17. !SchoolARecSum myvars=Term_17. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_17). DO IF (SchoolA_record_sum_Term_17=0). RECODE Term_17_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. !SelectTerm myvars=Term_18. !SchoolARecSum myvars=Term_18. RENAME VARIABLES (SchoolA_record_sum =SchoolA_record_sum_Term_18). DO IF (SchoolA_record_sum_Term_18=0). RECODE Term_18_DuplicateRecords (1=1.5) (2=2.5) (3=3.5) (4=4.5) (5=5.5). END IF. EXECUTE. FILTER OFF. USE ALL. EXECUTE. * Could also merge in majors, 1st generation, GPA categories, or other info. * This table shows enrollment at your institution and other institutions by term, including rows for graduated. FILTER OFF. USE ALL. * Custom Tables. CTABLES /VLABELS VARIABLES=SchoolA_record Graduated Term_1 Term_2 Term_3 Term_4 Term_5 Term_6 Term_7 Term_8 Term_9 Term_10 Term_11 Term_12 Term_13 Term_14 Term_15 Term_16 Term_17 Term_18 DISPLAY=LABEL /TABLE SchoolA_record [C] > Graduated [C] BY Term_1 [C][COUNT F40.0] + Term_2 [C][COUNT F40.0] + Term_3 [C][COUNT F40.0] + Term_4 [C][COUNT F40.0] + Term_5 [C][COUNT F40.0] + Term_6 [C][COUNT F40.0] + Term_7 [C][COUNT F40.0] + Term_8 [C][COUNT F40.0] + Term_9 [C][COUNT F40.0] + Term_10 [C][COUNT F40.0] + Term_11 [C][COUNT F40.0] + Term_12 [C][COUNT F40.0] + Term_13 [C][COUNT F40.0] + Term_14 [C][COUNT F40.0] + Term_15 [C][COUNT F40.0] + Term_16 [C][COUNT F40.0] + Term_17 [C][COUNT F40.0] + Term_18 [C][COUNT F40.0] /CATEGORIES VARIABLES=SchoolA_record [1.00, .00] EMPTY=INCLUDE /CATEGORIES VARIABLES=Graduated Term_1 Term_2 Term_3 Term_4 Term_5 Term_6 Term_7 Term_8 Term_9 Term_10 Term_11 Term_12 Term_13 Term_14 Term_15 Term_16 Term_17 Term_18 ORDER=A KEY=VALUE EMPTY=EXCLUDE. * This table counts the number of duplicate records by term. FILTER OFF. USE ALL. * Custom Tables. CTABLES /VLABELS VARIABLES=Term_1_DuplicateRecords Term_2_DuplicateRecords Term_3_DuplicateRecords Term_4_DuplicateRecords Term_5_DuplicateRecords Term_6_DuplicateRecords Term_7_DuplicateRecords Term_8_DuplicateRecords Term_9_DuplicateRecords Term_10_DuplicateRecords Term_11_DuplicateRecords Term_12_DuplicateRecords Term_13_DuplicateRecords Term_14_DuplicateRecords Term_15_DuplicateRecords Term_16_DuplicateRecords Term_17_DuplicateRecords Term_18_DuplicateRecords DISPLAY=LABEL /TABLE BY Term_1_DuplicateRecords [C][COUNT F40.0] + Term_2_DuplicateRecords [C][COUNT F40.0] + Term_3_DuplicateRecords [C][COUNT F40.0] + Term_4_DuplicateRecords [C][COUNT F40.0] + Term_5_DuplicateRecords [C][COUNT F40.0] + Term_6_DuplicateRecords [C][COUNT F40.0] + Term_7_DuplicateRecords [C][COUNT F40.0] + Term_8_DuplicateRecords [C][COUNT F40.0] + Term_9_DuplicateRecords [C][COUNT F40.0] + Term_10_DuplicateRecords [C][COUNT F40.0] + Term_11_DuplicateRecords [C][COUNT F40.0] + Term_12_DuplicateRecords [C][COUNT F40.0] + Term_13_DuplicateRecords [C][COUNT F40.0] + Term_14_DuplicateRecords [C][COUNT F40.0] + Term_15_DuplicateRecords [C][COUNT F40.0] + Term_16_DuplicateRecords [C][COUNT F40.0] + Term_17_DuplicateRecords [C][COUNT F40.0] + Term_18_DuplicateRecords [C][COUNT F40.0] /CLABELS COLLABELS=OPPOSITE /CATEGORIES VARIABLES=Term_1_DuplicateRecords Term_2_DuplicateRecords Term_3_DuplicateRecords Term_4_DuplicateRecords Term_5_DuplicateRecords Term_6_DuplicateRecords Term_7_DuplicateRecords Term_8_DuplicateRecords Term_9_DuplicateRecords Term_10_DuplicateRecords Term_11_DuplicateRecords Term_12_DuplicateRecords Term_13_DuplicateRecords Term_14_DuplicateRecords Term_15_DuplicateRecords Term_16_DuplicateRecords Term_17_DuplicateRecords Term_18_DuplicateRecords ORDER=A KEY=VALUE EMPTY=INCLUDE. * One large chart with ALL schools attended by term. FILTER OFF. USE ALL. * Custom Tables. CTABLES /VLABELS VARIABLES=CollegeName Term_1 Term_2 Term_3 Term_4 Term_5 Term_6 Term_7 Term_8 Term_9 Term_10 Term_11 Term_12 Term_13 Term_14 Term_15 Term_16 Term_17 Term_18 DISPLAY=LABEL /TABLE CollegeName BY Term_1 [COUNT F40.0] + Term_2 [COUNT F40.0] + Term_3 [COUNT F40.0] + Term_4 [COUNT F40.0] + Term_5 [COUNT F40.0] + Term_6 [COUNT F40.0] + Term_7 [COUNT F40.0] + Term_8 [COUNT F40.0] + Term_9 [COUNT F40.0] + Term_10 [COUNT F40.0] + Term_11 [COUNT F40.0] + Term_12 [COUNT F40.0] + Term_13 [COUNT F40.0] + Term_14 [COUNT F40.0] + Term_15 [COUNT F40.0] + Term_16 [COUNT F40.0] + Term_17 [COUNT F40.0] + Term_18 [COUNT F40.0] /CATEGORIES VARIABLES=CollegeName Term_1 Term_2 Term_3 Term_4 Term_5 Term_6 Term_7 Term_8 Term_9 Term_10 Term_11 Term_12 Term_13 Term_14 Term_15 Term_16 Term_17 Term_18 ORDER=A KEY=VALUE EMPTY=EXCLUDE. * A chart with schools students graduated from by term. USE ALL. COMPUTE filter_$=(Graduated="Y"). VARIABLE LABELS filter_$ 'Graduated="Y" (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. FREQUENCIES VARIABLES=Graduated /ORDER=ANALYSIS. * Custom Tables. CTABLES /VLABELS VARIABLES=CollegeName Term_1 Term_2 Term_3 Term_4 Term_5 Term_6 Term_7 Term_8 Term_9 Term_10 Term_11 Term_12 Term_13 Term_14 Term_15 Term_16 Term_17 Term_18 DISPLAY=LABEL /TABLE CollegeName BY Term_1 [COUNT F40.0] + Term_2 [COUNT F40.0] + Term_3 [COUNT F40.0] + Term_4 [COUNT F40.0] + Term_5 [COUNT F40.0] + Term_6 [COUNT F40.0] + Term_7 [COUNT F40.0] + Term_8 [COUNT F40.0] + Term_9 [COUNT F40.0] + Term_10 [COUNT F40.0] + Term_11 [COUNT F40.0] + Term_12 [COUNT F40.0] + Term_13 [COUNT F40.0] + Term_14 [COUNT F40.0] + Term_15 [COUNT F40.0] + Term_16 [COUNT F40.0] + Term_17 [COUNT F40.0] + Term_18 [COUNT F40.0] /CATEGORIES VARIABLES=CollegeName Term_1 Term_2 Term_3 Term_4 Term_5 Term_6 Term_7 Term_8 Term_9 Term_10 Term_11 Term_12 Term_13 Term_14 Term_15 Term_16 Term_17 Term_18 ORDER=A KEY=VALUE EMPTY=EXCLUDE. FILTER OFF. USE ALL. * For Term- need to change term for each term you want to run Selecting ONLY those who enrolled JUST at an other school Term x. SPLIT FILE OFF. USE ALL. COMPUTE filter_$=(SchoolA_record_sum_Term_4=0). VARIABLE LABELS filter_$ 'SchoolA_record_sum_Term_4=0 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. FREQUENCIES VARIABLES=SchoolA_record_sum_Term_4 CollegeName /ORDER=ANALYSIS. SORT CASES BY MAJOR. SPLIT FILE SEPARATE BY MAJOR. * Custom Tables. CTABLES /VLABELS VARIABLES=MAJOR CollegeName DISPLAY=LABEL /TABLE BY MAJOR [C][COUNT F40.0] + CollegeName [C][COUNT F40.0] /CATEGORIES VARIABLES=MAJOR CollegeName ORDER=A KEY=VALUE EMPTY=EXCLUDE /TITLES TITLE='Attended ONLY other school in term'. SPLIT FILE OFF. FILTER OFF. USE ALL. * This section selects those who did NOT have an enrollment record that term (Term 3 used in the example). STRING Full_Name (A41). COMPUTE Full_Name=CONCAT(FirstName," ",LastName). EXECUTE. FILTER OFF. USE ALL. SPLIT FILE OFF. AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=ID /Term_3_record_any=SUM(Term_3_sum). RECODE Term_3_record_any (1=1) (2=2) (3=3) (4=4) (5=5) (ELSE=0). EXECUTE. * Identify Duplicate Cases. SORT CASES BY ID(A) Term_3_record_any(A). MATCH FILES /FILE=* /BY ID Term_3_record_any /FIRST=PrimaryFirst1_any /LAST=PrimaryLast1_any. DO IF (PrimaryFirst1_any). COMPUTE MatchSequence=1-PrimaryLast1_any. ELSE. COMPUTE MatchSequence=MatchSequence+1. END IF. LEAVE MatchSequence. FORMATS MatchSequence (f7). COMPUTE InDupGrp=MatchSequence>0. SORT CASES InDupGrp(D). MATCH FILES /FILE=* /DROP=PrimaryFirst1_any InDupGrp MatchSequence. VARIABLE LABELS PrimaryLast1_any 'Indicator of each last matching case as Primary'. VALUE LABELS PrimaryLast1_any 0 'Duplicate Case' 1 'Primary Case'. VARIABLE LEVEL PrimaryLast1_any (ORDINAL). FREQUENCIES VARIABLES=PrimaryLast1_any. EXECUTE. USE ALL. COMPUTE filter_$=(Term_3_record_any=0 & PrimaryLast1_any=1). VARIABLE LABELS filter_$ 'Term_3_record_any=0 & PrimaryLast1_any=1 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. FREQUENCIES VARIABLES=Term_3 Full_Name /ORDER=ANALYSIS. FILTER OFF. USE ALL. * This section is to select those who had another record(s) at an other institution in ADDITION to School A in the term Term 4 again used here. FILTER OFF. USE ALL. USE ALL. COMPUTE filter_$=(Term_4_sum > 1 & SchoolA_record_sum_Term_4 > 0). VARIABLE LABELS filter_$ 'Term_4_sum > 1 & SchoolA_record_sum_Term_4 > 0 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. SORT CASES BY MAJOR. * Custom Tables. CTABLES /VLABELS VARIABLES=MAJOR CollegeName DISPLAY=LABEL /TABLE BY MAJOR + CollegeName [C][COUNT F40.0] /CATEGORIES VARIABLES=MAJOR CollegeName ORDER=A KEY=VALUE EMPTY=EXCLUDE /TITLES TITLE='Attended School A AND other school in term'. SORT CASES BY MAJOR. SPLIT FILE SEPARATE BY MAJOR. * Custom Tables. CTABLES /VLABELS VARIABLES=MAJOR CollegeName DISPLAY=LABEL /TABLE BY MAJOR [C][COUNT F40.0] + CollegeName [C][COUNT F40.0] /CATEGORIES VARIABLES=MAJOR CollegeName ORDER=A KEY=VALUE EMPTY=EXCLUDE /TITLES TITLE='Attended School A AND other school in term'. *This section selects those who graduated from another institution. FILTER OFF. USE ALL. SPLIT FILE OFF. USE ALL. COMPUTE filter_$=(Graduated="Y" & SchoolA_record=0). VARIABLE LABELS filter_$ 'Graduated="Y" & SchoolA_record=0 (FILTER)'. VALUE LABELS filter_$ 0 'Not Selected' 1 'Selected'. FORMATS filter_$ (f1.0). FILTER BY filter_$. EXECUTE. SORT CASES BY MAJOR. SPLIT FILE SEPARATE BY MAJOR. * Custom Tables. CTABLES /VLABELS VARIABLES=MAJOR CollegeName DISPLAY=LABEL /TABLE BY MAJOR [C][COUNT F40.0] + CollegeName [C][COUNT F40.0] /CATEGORIES VARIABLES=MAJOR CollegeName ORDER=A KEY=VALUE EMPTY=EXCLUDE /TITLES TITLE='Graduated from other school'.