DBMS/Copy is a very useful program for converting data from one format, such as an
Excel spreadsheet, to another format such as a Stata ".dta" or SPSS ".sav" file. On
the PC, it has an easy-to-use graphical interface and can convert among dozens of
different file types. On UNIX, it also has an easy-to-use graphical interface
as well as a command-line interface. It can even be used to extract data from
ascii/text files. Although the instructions here describe many steps, they are
rather simple. Typically, converting a file from SAS to Stata, for example,
should only take a few minutes.
DBMS/Copy on the PC can be found in any of the CIT clusters as well as the Data and Statistical Services data lab.
DBMS/Copy on UNIX can be found on any of the Arizona machines. If you need help
using DBMS/Copy, please contact Data and
- When you don't Need DBMS/COPY to Convert File Formats
- Some statistical packages can read data files created by other programs.
These statistical packages can read the data files directly,
without having to convert them first.
Here are some instances when you don't need DBMS/COPY to convert file formats.
STATA includes the insheet command for reading tab- and comma-delimited
files. Excel and Datastream files can be saved in this format. For details see
STATA: How to Import Excel and Datastream Files.
S-PLUS package has built-in procedures for accessing SAS data files as well
as Ascii comma- or tab-delimited files.
- A Note for SAS users
- SAS has several different types of files and how you convert them depends
on how they were created. If you have problems converting a SAS file,
please look at these instructions
- Even when a file converts successfully, it is recommended that you verify the
conversion with some summary statistics. First generate some summary statistics
(e.g, means) and a few frequency distributions using the original file, and then
compare them against the same statistics generated from the new file.
Missing data values often do not convert in a way that users expect, and these
are easily detected by checking the minimum and maximum values for particular
variables along with the number of non-missing observations.
Finally, you can print all of the data for a few observations and compare them
across the original and converted files. Select a few observations from the
beginning of the file and a few from the end of the file.
- Transferring Files
- Often you need to not only covert the format of the file you are using,
but you also need to copy the file from one computing platform to another.
Some file formats, such as STATA, can be transferred from one computer to another
without any special procedures. Other files formats, such as SAS and SPSS,
must be saved in transport or portable formats before they
can be transferred from the computer where they were created.
File Transfer Protocol (FTP) is one of the most common programs used to
copy files between UNIX, MVS, PCs and other types of computers.
See the documentation on this web for
How to Transfer Files.
- When you invoke DBMS/COPY, a file named dbmscopy.log
is automatically written to your UNIX home directory.
This file contains the commands that were sent to DBMS/COPY
as well as responses to the commands.
This file is appended with subsequent invocations of DBMS/COPY.
For example, the following entry in dbmscopy.log
shows the commands that were used to convert the format
of a file. The log file indicates that the commands were sent
from an interactive (Xwindows) session.
For diagnostic purposes, you can also see the results of the
conversion, namely the number of records and variables written to the
new file. You should compare these figures against the original file.
Any errors encountered during processing would appear in
the log file.
*** Input From Interactive Copy Thu Jan 2 12:23:10 1997
3941 Records Of 89 Variables Written To /scratch/bigdata/hsb.spssport