***
Title: Using Institutional files with Clearinghouse files for Progression & Completion
Created: February 2015
Author: Pam Borkowski-Valentin, Swarthmore College Office of Institutional Research



* 
ENROLLMENT FILES
This uses a standard begin/end date for all at your institution- you won't see if someone was only enrolled for X number of days 
Therefore, make sure that anyone in this file should be counted as someone who did not withdraw completely in that term.
*
Since this is enrollment begin/end, it is for the enrollment file ONLY (the grad file has a grad date variable).
*
This syntax uses Banner term format with fall designated by '04' and Spring designated by '02' after the year
So, fall 2012 is 201204 and Spring 2013 is 201302
If you use different term coding, have different terms (or a different variable for your TERM), adjust the syntax accordingly
The end result should be a begin & end date for the term in the same format as the Clearinghouse (YYYYMMDD).

ALTER TYPE TERM (A6).

STRING  Fall_Spring (A2).
COMPUTE Fall_Spring=CHAR.SUBSTR(TERM,5,2).
EXECUTE.


STRING  EnrollmentBeginMonthDay (A4).
COMPUTE EnrollmentBeginMonthDay="0".
EXECUTE.

DO IF (Fall_Spring="04").
RECODE EnrollmentBeginMonthDay ('0'='0901').
END IF.
EXECUTE.
DO IF (Fall_Spring="02").
RECODE EnrollmentBeginMonthDay ('0'='0120'). 
END IF.
EXECUTE.
STRING  EnrollmentBeginYEAR (A4).
COMPUTE EnrollmentBeginYEAR=CHAR.SUBSTR(TERM,1,4).
EXECUTE.
STRING  EnrollmentBegin (A8).
COMPUTE EnrollmentBegin=CONCAT(EnrollmentBeginYEAR,EnrollmentBeginMonthDay).
EXECUTE.

ALTER TYPE EnrollmentBegin (F12.0).

STRING  EnrollmentEndMonthDay (A4).
COMPUTE EnrollmentEndMonthDay="0".
EXECUTE.

DO IF (Fall_Spring="04").
RECODE EnrollmentEndMonthDay ('0'='1220').
END IF.
EXECUTE.
DO IF (Fall_Spring="02").
RECODE EnrollmentEndMonthDay ('0'='0515'). 
END IF.
EXECUTE.

STRING  EnrollmentEndYEAR (A4).
COMPUTE EnrollmentEndYEAR=CHAR.SUBSTR(TERM,1,4).
EXECUTE.

STRING  EnrollmentEnd (A8).
COMPUTE EnrollmentEnd=CONCAT(EnrollmentEndYEAR,EnrollmentEndMonthDay).
EXECUTE.

ALTER TYPE EnrollmentEnd (F12.0).


STRING Graduated (A1).
COMPUTE Graduated="N".
EXECUTE.


ALTER TYPE ID (F12.0).
ALTER TYPE FNAME (A50).
ALTER TYPE LNAME (A50).
ALTER TYPE MAJ11 (A22).




*
GRADUATE FILES
The graduation date begins as DD-MMM-YYYY
The first syntax creates a new variable to change it to YYYY/MM/DD
The second sytnax removes the / so that it is Clearinghouse format of YYYYMMDD.



FORMATS DEGDATE (SDATE10).
STRING DEGDATE_string (A10).
COMPUTE DEGDATE_string = STRING(DEGDATE,SDATE10).
EXECUTE.

STRING GraduationDate (A8).
COMPUTE
GraduationDate=concat(substr(DEGDATE_string,1,4),substr(DEGDATE_string,6,2),substr(DEGDATE_string,9,2)).
EXECUTE.

STRING Graduated (A1).
COMPUTE Graduated="Y".
EXECUTE.

ALTER TYPE ID (F12.0).
ALTER TYPE FNAME (A50).
ALTER TYPE LNAME (A50).

ALTER TYPE RMAJ1 (A8).
ALTER TYPE RMAJ2 (A8).




*
Merge the merged grad & enrollment files together
Remember to include the variables that are unique for each file in the final merged file ( such as enrollment begin/end in enrollment file & grad date info in the graduate file).

*
Once you have a master merged file, create the following variables as they are in the Clearinghouse return file
The RENAME VARIABLE syntax towards the end is used to change your internal variable names to Clearinghouse ones- replace the variable on the left of the equals sign to whatever 
your school names that variable
The ALTER TYPE recodes will also be run for the Clearinghouse file, and standardize so they can be merged
The variables are set fairly long, but lengthen if you prefer.


STRING RecordFoundYN (A1).
COMPUTE RecordFoundYN="Y".
EXECUTE.

STRING CollegeName (A100).
COMPUTE CollegeName="School A".
EXECUTE.

STRING CollegeState (A2).
COMPUTE CollegeState="PA".
EXECUTE.

STRING @2year4year (A1).
COMPUTE @2year4year="4".
EXECUTE.

STRING PublicPrivate (A7).
COMPUTE PublicPrivate="Private".
EXECUTE.

RENAME VARIABLES RDEG1= DegreeTitle1.
RENAME VARIABLES RMAJ1= DegreeMajor1.
RENAME VARIABLES RMAJ2= DegreeMajor2.

RENAME VARIABLES FNAME= FirstName.
RENAME VARIABLES LNAME= LastName.

ALTER TYPE CollegeName (A100).
ALTER TYPE DegreeTitle (A100).
ALTER TYPE DegreeMajor1 (A100).
ALTER TYPE DegreeMajor2 (A100).
ALTER TYPE EnrollmentBegin (A8).
ALTER TYPE EnrollmentEnd (A8).
ALTER TYPE SearchDate (A8).
ALTER TYPE GraduationDate (A8).
ALTER TYPE FirstName (A20).
ALTER TYPE LastName (A20).



*
We will create the School_A_record variable for the Clearinghouse file, so we are creating it in our master merge file as well
(there should not be any "no record found" records).

IF  (ANY(CollegeName,"SCHOOL A","School A")) Schoool_A_record=1.
RECODE Schoool_A_record (MISSING=0).
DO IF (RecordFoundYN="N").
RECODE Schoool_A_record (0=2).
END IF.
EXECUTE.

VALUE LABELS
Schoool_A_record
0 'Other School'
1 'School A'
2 'No Record Found'.
Execute.



*
CLEARINGHOUSE DETAIL RETURN FILE.

RENAME VARIABLES
RequesterReturnField= ID.
EXECUTE.

ALTER TYPE ID (F12.0).
ALTER TYPE CollegeName (A100).
ALTER TYPE DegreeTitle (A100).
ALTER TYPE DegreeMajor1 (A100).
ALTER TYPE DegreeMajor2 (A100).
ALTER TYPE EnrollmentBegin (A8).
ALTER TYPE EnrollmentEnd (A8).
ALTER TYPE SearchDate (A8).
ALTER TYPE GraduationDate (A8).
ALTER TYPE FirstName (A20).
ALTER TYPE LastName (A20).
ALTER TYPE @2year4year (A1).


IF  (ANY(CollegeName,"SCHOOL A","School A")) Schoool_A_record=1.
RECODE Schoool_A_record (MISSING=0).
DO IF (RecordFoundYN="N").
RECODE Schoool_A_record (0=2).
END IF.
EXECUTE.

VALUE LABELS
Schoool_A_record
0 'Other School'
1 'School A'
2 'No Record Found'.
Execute.


*
This creates a new file "OtherSchoolRecords" that is ONLY the Clearinghouse records from other schools
This way, the records for your school won't be duplicated with the Clearinghouse records.

DATASET COPY  OtherSchoolRecords.
DATASET ACTIVATE  OtherSchoolRecords.
FILTER OFF.
USE ALL.
SELECT IF (Schoool_A_record=0).
EXECUTE.

*
Merge this file & the cohort master merged file casewise to create the file that you can then use to run the Progression & Completion syntax.