DARTS : Organizing Student Tracker (NSC) Results Using SPSS

SPSS syntax for the transformation of National Student Clearinghouse (NSC) Student Tracker data was originally provided by John Runfeldt, Associate Director of Institutional Research and Assessment at William Paterson University, at the 37th Annual Conference of the NorthEast Association for Institutional Research (NEAIR), November 15, 2010.

Step-by-step guide for transforming Student Tracker data into one record per student format

1. Set aside all aggregate and control files from the National Student Clearinghouse (NSC).

2. Combine all NSC detail report files into a single spreadsheet. Change column A, which is labeled by default as “Your Unique Identifier” to UMSCampus, or some other unique name. Make sure that the name of the students' originating campus is filled in, such that if the detail report was for UMA students that the UMSCampus field is filled in with UMA for each row of data.  That way, when the data from multiple campuses is combined, it will be easier to determine the student’s campus of origin within UMS.

   Step 2 - Change Column A

3. Remove the underscore “_” and hyphen “-“ from the Requester Return Field. If not, the SPSS syntax will return an error when the syntax attempts to run stating that the RANK command cannot run on a string field and that the field needs to be numeric. Removing the underscore and hyphen can be done using the Find & Replace command in Excel:

   • Make sure the ReqReturnField column is highlighted
   • Use ctrl + F to bring up the find dialog box and click on the replace tab
   • Put an underscore “_” in the Find what text box and leave the Replace with box empty
   • Click Replace All
   Repeat for the hyphen “-“

4. Import the saved spreadsheet into SPSS. The names of the headers may need to be changed manually if they contain any special characters so that the characters are removed and only numbers or letters without spaces remain. For most fields, SPSS will automatically remove the space between words and convert header names to the proper format.

   Step 4 - Import The Excel Spreadsheet

5. Create a folder where the syntax can save files in the syntax shown. The directory in the syntax should be adjusted to accurately reflect the directory where your documents will be saved. Syntax that needs to be manually adjusted is in blue text.
6. Use the following syntax, saved as a syntax file, and choose All under the Run menu to Run All. It will combine all records into one record per student.  Make sure to check the SPSS output window for potential errors in execution, which can easily happen if steps are missed in the process. SPSS occasionally has a problem where transformation actions are “pending,” which requires checking the data editor or checking online for help with this error. Most of the time, it can be solved by going to EDIT > OPTIONS, click on the DATA tab and make sure Calculate Values Immediately is selected.
   Note: The Syntax will create three files. The NO ACT file is all students with missing activity in NSC’s files. The Duplicapted file contains the early steps in the record combination process, which shows one record, per enrollment activity, per student. The Unduplicated file contains what we want, which is one record, per institution, per student.

SAVE OUTFILE = 'C:\Users\(your directory)\(your sub directory)\Duplicated.sav'.

DATASET NAME CORE.

DATASET COPY NO_ACT.

DATASET ACTIVATE NO_ACT.


SELECT IF(RecordFoundYN = 'N').

DELETE VARIABLES CollegeName
CollegeState CollegeBranchCode @2year4year PublicPrivate EnrollmentBegin
EnrollmentEnd EnrollmentStatus CollegeSequence Graduated GraduationDate
DegreeTitle .

SAVE OUTFILE = 'C:\Users\(your directory)\(your sub directory)\NO_ACT.sav'.

EXECUTE.


DATASET ACTIVATE CORE.

SELECT IF(RecordFoundYN = 'Y').

EXECUTE.


COMPUTE year=number(SUBTSTR(string(EnrollmentBegin,F8),3,2),F2).

COMPUTE day=number(SUBTSTR(string(EnrollmentBegin,F8),7,2),F2).

COMPUTE month=number(SUBTSTR(string(EnrollmentBegin,F8),5,2),F2).

COMPUTE ENR_BEGIN=DATE.MDY(month,day,year).

FORMATS ENR_BEGIN (ADATE8).

EXECUTE.


COMPUTE year=number(SUBSTR(string(EnrollmentEnd,F8),3,2),F2).

COMPUTE day=number(SUBSTR(string(EnrollmentEnd,F8),7,2),F2).

COMPUTE month=number(SUBSTR(string(EnrollmentEnd,F8),5,2),F2).

COMPUTE ENR_END=DATE.MDY(month,day,year).

FORMATS ENR_END (ADATE8).

EXECUTE.


SORT CASES BY UMSCampus (A)
LastName (A) FirstName (A) ENR_BEGIN (A).

IF (SYSMIS(CollegeSequence))
CollegeSequence=LAG(CollegeSequence).

COMPUTE ENR_DAYS = DATEDIFF(ENR_END,ENR_BEGIN,"days").

SELECT IF(ENR_DAYS > 10).

EXECUTE.


RANK 

VARIABLES=ENR_BEGIN (A) BY
ReqReturnField CollegeSequence /RANK /PRINT=NO/TIES=MEAN .

DO IF (RENR_BEG ~=1).

RECODE ENR_BEGIN (ELSE=SYSMIS) .

END IF.


IF (SYSMIS(ENR_BEGIN)) ENR_BEGIN = LAG(ENR_BEGIN).\

SORT CASES BY ReqReturnField (A)CollegeName (A).

MATCH FILES /FILE = * /BY ReqReturnField CollegeName /LAST = PrimaryLast.

VARIABLE LABELS PrimaryLast 'Indicator'.

VALUE LABELS PrimaryLast 0 'Duplicate Case' 1 'Primary Case'.

VARIABLE LEVEL PrimaryLast (ORDINAL).

SELECT IF(PrimaryLast = 1).

RENAME VARIABLES(RENR_BEG=Semesters_at_Inst) .

DATASET ACTIVATE CORE.

SAVE OUTFILE = 'C:\Users\(your directory)\(your sub directory)\Duplicated.sav'.

DATASET NAME UNDUPLICATED.

DATASET ACTIVATE UNDUPLICATED.

SAVE OUTFILE = 'C:\Users\(your directory)\(your sub directory)\Unduplicated.sav'.


SORT CASES BY ReqReturnField (A) ENR_BEGIN (A).

CASESTOVARS

/ID = ReqReturnField

/GROUPBY = INDEX.

DATASET ACTIVATE UNDUPLICATED.

ADD FILES /FILE=*

/FILE='NO_ACT'.

Related articles