Mass Conversion: Convert many SAS (.xpt) files to SPSS (.sav) datasets via batch processing

The following is a series of methods that I use to convert large collections of SAS files en masse. I frequently receive large data dumps such as this and because I don't actually have access to SAS, I have to use SPSS to convert the datasets one by one. This has helped me out tremendously when I have 100 or more files to convert.

NOTICE

For those of you who would like to skip all of the following details, I have compiled all of these methods into an easy-to-use application for Windows which can be found here.

The software that I used:
-Windows 7
-Python 2.7
-Microsoft Excel
-SPSS

1. Save all of the SAS (.xpt) files to be converted into one folder

2. Open a command prompt window with administrator privileges. If you do not know how to do this, search for "cmd" in your start menu, right-click and select "Run as Administrator"

2. Change directory (cd) to directory with SAS files and execute command to generate file list exported as a text file. (For some reason I could not set the destination to my SAS file folder so I just sent it to the root)

dir -C -1 /B /S *.xpt > C:\filelist.csv

3. Now copy filelist.csv from C: to SAS folder, This required me to approve with Admin privileges.

Open it and  you should have all of the files listed in one column.

4. Duplicate A column into B

5. Select cell B1 and then run Replace Extension macro to replace file extensions

                  Sub ReplaceExtension()  
                  Dim Result as String  
                  Do Until ActiveCell.Formula = ""  
                  prevTxt = ActiveCell.Formula  
                  result = Replace(prevTxt, ".xpt", ".sav")  
                  newTxt = result  
                  ActiveCell.Formula = newTxt  
                  ActiveCell.Offset(1, 0).Range("A1").Select  
                  Loop 
             End Sub

6. Copy both columns (highlight entire matrix; don't select columns)

and transpose (Paste Special) them into new spreadsheet; Save as a new .csv (comma-delimited) file. Also, Excel will persistently ask you to save it as a different file type, but don't.

So, you will end up with .csv file with the first row containing SAS file names and the second with corresponding SPSS file names.

7. Now, using IDLE edit the syntax_generator python script accordingly.

Change input file to your .csv file name; give explicit path if in a separate folder. Double check that your CSV file has SAS files on first row and SPSS file names on the second row. Copy code here.

8. Select Run --> Run Module or hit F5 Copy output code (in blue) from IDLE and

paste output into SPSS Syntax Editor. To create a new syntax, Select File -> New -> Syntax in the SPSS menu

9. Click Run in Menu and Select ALL

WARNING: Close any other programs as this process will likely cause CPU to spike to 100%

Many SPSS windows will appear and dissappear on your screen depending on how many files you were converting. Once completed, you should see all of you SAS files accompanied in their folder by a corresponding SPSS file.

Hope this saved you some tedious clicking. Peace.

And to save more time in the future please try: