I have data (or I want to enter data) in an Excel, or dBase, or Lotus format, and I want to use the data in SAS without writing out Raw (ASCII) data or re-entering.

SAS for Windows and SAS for Macintosh can read .XLS, .DBF and .DIF files easily.

If you want to use your data base data in UNIX, you'll need to do a couple of extra steps, outlined at the end of this document, namely, convert your SAS data set into a transport file, upload it (in Binary mode, always) to UNIX, then convert it back into a SAS data set there.

SAS provides three basic ways to convert data base files from other types of software into SAS Data Sets, and vice versa. The more difficult, but more flexible method is to use SAS/ACCESS software, either from ACCESS Windows or with PROC ACCESS syntax. (In order to use this method, SAS/ACCESS software must be installed. SAS/ACCESS is provided with SAS/BASE when distributed by UCS, but must be installed as a separate add-on by the user.) An easier method, if you have (or can make) .DBF or .DIF files, is to use PROC DBF or PROC DIF. The easiest method (if you have SAS for Windows 6.12) is the "Import Wizard".

These three methods are outlined below. Note that PROC DBF and PROC DIF are available for Windows and Macintosh only in Release 6.10 and later (and, of course, in SAS-PC, Release 6.04).

SAS for Windows and SAS for the Macintosh as of Release 6.12 have an "Import Wizard" that allows you to point-and-click your way through a simple process that imports .XLS, .DBF, .WK1, .WK3, and .WK4 files, as well as files with data delimited by commas and other characters. Click on File > Import Data and follow the instructions, if you have one of these types of files. To write out a SAS Data Set to an Excel File format, click File > Export Data to create the Excel version.

NOTE 1 -- SAS 6.x only knows older versions of Excel: If you are saving your data from Excel into an .xls file for use in SAS Version 6.x, you must use the option that specifies 'Excel 5.0/7.0' or 'Excel 5.0/95', or even an earlier version such as 'Excel 4.0' (not the more recent types of .xls files available in versions such as Excel 97). Otherwise, SAS may fail to import the file, giving an OLE2 error.

NOTE 2 -- Number fields may get lost in SAS data sets unless converted: Numeric fields in Excel sometimes do not translate correctly to SAS unless they are "declared" as Text in Excel before the conversion is made. It is not sufficient to use Format Cells>Number (or Text) to make this conversion. Click Data>Text-to-Columns>Choose Delimiter (Tab is usually correct) then click the Text radio button to make the conversion. After the file is saved, you can then use the SAS Import Wizard (or PROC IMPORT) to bring the data into SAS.

NOTE 3 -- Excel Workbooks with multiple Worksheets: When you want to import multple Worksheets (or a Worksheet other than Sheet1) from an Excel Workbook, you can specify the Worksheet you want using the "Options" button that activates when you choose (or specify) the .xls file you are trying to import. Note that the field which appears after clicking "Options" (labeled "Worksheet/Range" in Version 7 and higher) may be empty. You can see a list of available Worksheets by clicking the down-arrow at the right of the field.

SAS for Windows and SAS for the Macintosh can read .DBF file formats and some types of .DIF formats. .DBF is a format used in dBase and other software, and .DIF is a format used in Lotus and elsewhere. Note that not all .DIF formats are constructed the same. SAS seems to be able to convert the older Lotus-made .DIF files, but not the newer types created by Microsoft Access and Excel. When in doubt, try a .DBF format or, if you have the necessary Release level of SAS, try the file types that the "Import Wizard" (see above) can handle.

(When outputting a .DIF file from a SAS data set for use in spreadsheet (or other) software, it seems there are no problems like those found when trying to input .DIF to SAS. .DIF files created by SAS normally can be read in old Lotus or in new Microsoft Access and Excel versions.)

For further documentation on PROC DBF and PROC DIF, as well as the more customized approach (referred to as 'the hard way' above), please see the SAS/ACCESS section in the SAS® OnlineDoc(tm), which is available as a link on the ITS Web page for SAS:

www.usc.edu/its/doc/statistics/sas/

PROC DBF and PROC DIF

To convert a dBase file to a SAS Data Set, run PROC DBF as in this example. We assume here that we have a dBase V file called tommy.dbf in the current directory, and the SAS data set called tomsas will be temporary.

  filename tom 'tommy.dbf';
  proc dbf db5=tom out=tomsas;
  run;

To convert a SAS Data Set into a dBase file, run PROC DBF as in the following example. We assume here that we have a permanent SAS Data Set called "medical" in a directory called "C:\mysas", and that we want to create a dBase IV file called "medicine.dbf" to be stored on the diskette in the A: drive.

  libname oldsas 'c:\mysas';
  filename newdbf 'a:\medicine.dbf';
  proc dbf db4=newdbf data=oldsas.medical;
  run;

To convert a .DIF file to a SAS Data Set, run PROC DIF as in this example. We assume here that we have a .DIF file called "thomas.dif" in the current directory, and the SAS data set called "thomasas" will be temporary.

  filename thom 'thomas.dif';
  proc dif dif=thom out=thomasas;
  run;

To convert a SAS Data Set into a .DIF file, run PROC DIF as in the following example. We assume here that we have a permanent SAS Data Set called "medinfo" in a directory called "C:\mysas", and that we want to create a .DIF file called "medinfo.dif" to be stored on the diskette in the A: drive.

  libname oldsas 'c:\mysas';
  filename newdif 'a:\medinfo.dif';
  proc dif dif=newdif data=oldsas.medinfo;
  run;

As you can see, PROC DIF works in the same way as the examples shown above for PROC DBF, although there are more choices you may need to make, including whether you want to specify a variable name prefix (the default is COLn, where "n" is the ordinal number in which the variable appears: COL1, COL2, etc.), whether you want to skip a row or rows at the beginning of the .DIF file, etc.

Note that these examples use DOS file specifications. The same SAS statements will work in SAS for the Macintosh with Mac file specifications in place of the DOS examples.

If you prefer to customize your SAS Data Set and read in only certain variables from the .DBF or .DIF file, or change variable names or formats, for example, you can use SAS/ACCESS software. SAS/ACCESS also can read .XLS and other file types. This is done by invoking ACCESS and by making choices and filling in fields in a series of SAS/ACCESS windows. Following is an outline (with notations) of a typical SAS/ACCESS session in which a .DBF file is converted into a SAS Data Set. The procedure for converting .DIF files is similar. In any case, this is just an overview, and only the very brave should attempt these operations without a manual.

To understand the process of converting a .DBF or .DIF file to a SAS Data Set (or a SAS View, if you wish), you should notice first that there are three major steps:

• point SAS to the relevant input and output files
  
• create an Access Descriptor to 'map' the .DBF or .DIF file
  
• create a View Descriptor to describe the new SAS Data Set (or SAS View)

Here is a typical scenario for creating a customized SAS Data Set from an existing .DBF file:
• If you wish to store the new SAS Data Set permanently, issue a LIBNAME statement to tell SAS where you want it stored

        LIBNAME XYZSAS 'C:\';
  

• Start SAS/ACCESS either by clicking on Globals>Access>Access Database Files, or by running the following program:

        PROC ACCESS;
        RUN;
  

  The Access Window will appear, showing all SAS Data Sets available to your system at that moment.

• Click File>New to create a new Access Descriptor
  you will see the "New" window in which you enter the LIBREF for the directory where you want the Access Descriptor to be stored and the NAME you want it to have. An Access Descriptor is a SAS File that can be re-used in the future. Example:

                 LIBREF     NAME      TYPE
  Access Name:   XYZSAS . DBFTEST1 . ACCESS
  

  Click OK.
• Next you go to the Select Data Window, where you highlight ".DBF Files" and click OK.

• Next is the ACCESS: Create Descriptor Window, where you must fill in the path to where the .DBF file is stored. This specification is really the path and the first part of the .dbf file name, not including the .dbf extension. Let's say your file is "C:\DBASE\TOCONVRT.DBF". In this window, you should see:

  Path:  C:\DBASE\TOCONVRT
  

• Next the list of variables found in the .DBF file is displayed. Here you can choose which variables you want to delete from the .DBF file (type 'D' in the 'Func' filed on the line where each variable is listed). You can also type new variable names under "SAS Names", if you want to change the original .DBF names. When you are finished modifying this window, click Locals>End.

• Next, you are returned to the ACCESS: Create Descriptor Window, where you should click File>End to ask SAS to save the Descriptor and continue.

• Now, in the main ACCESS Window, find the Access Descriptor you have just created and saved. It will be listed as

      ___     XYZSAS    DBFTEST1    ACCESS
  

  Type "CV" (Create View) on the line to the left of the name. This will bring up the View Descriptor Display Window, in which you specify both the name for the View Descriptor, and the name for the output SAS Data Set. Your window might look like this:

  Descriptor: Library:  OLD       Member: DBFVIEW1   Type:  VIEW
  Output SAS Data Set: Library:  OLD      Member:  DBF2SAS
  Path:  C:\DBASE\TOCONVRT.DBF
         
        <variable listing, with Action fields, etc.>
  

  When you have finished customizing this view (note that unlike the Access Descriptor where you deselect variables if you wish, no variables are selected here unless you type 'S' under 'Func'), click Locals>End, and you will return to the main ACCESS Window, and both your View Descriptor and your final SAS Data Set will be written (as shown in the SAS Log).

PROC IMPORT (and the Import Wizard) on the UNIX platform currently can process only dBase (*.dbf) files, so file formats other than *.dbf created in Windows must be changed to *.dbf files before attempting to import them to SAS on the UNIX platform. This is explained in detail in the SAS document stored here (please click).

Another method (perhaps more dependable and predictable) for moving data in special PC file formats to SAS under UNIX is to import them into SAS for Windows first (using PROC IMPORT or the Import Wizard, as noted above), then create a SAS Transport File using PROC CPORT, then transmit the file (in BINARY mode only) to UNIX, then unravel the transport file there using PROC CIMPORT.

To move a SAS data set between unlike systems, such as from a Windows machine to UNIX, you need to make a SAS Transport File out of the SAS Data Set that you wish to move. An outline of this operation is shown below; for more detail, see the SAS Transport Files section in this FAQ directory, or go to:

http://www.usc.edu/ucs/userserv/statistics/sas/sastransport/

Say you have converted an Excel .xls file into a SAS data set called "project4" which is now stored in a subdirectory under Windows ("C:\SASDATA"). (Of course, the procedures shown below will work on any SAS data set, not just those that used to be Excel data.) Here is a scenario for moving the data to UNIX.

Under Windows or Macintosh:

Run PROC CPORT to make a transport file. (Example uses Windows file references.)

  libname mydat 'c:\sasdata';
  filename trans1 'c:\tmp\datamove.xpo';
  proc cport data=mydat.project4 file=trans1 sortinfo=no;
  run;

The PROC CPORT statement reads the SAS data set and writes the transport file into the DOS file referenced by TRANS1, namely "datamove.xpo".

NOTE: Once SAS for UNIX is upgraded to match the current version of SAS for Windows, the SORTINFO=NO option of PROC CPORT will no longer be necessary. See the entry in this FAQ directory called "Making Transport Files in SAS for Windows..." for more information. Also, if using SAS for the Macintosh, the SORTINFO=NO option should be omitted.

Moving the File:

Use WS-FTP, Kermit, or any other file transfer protocol (consult the documentation for your communication software; if you're in a user room, use WS-FTP on Windows and Fetch on a Macintosh), and move the file *in BINARY mode* (this is essential) to your UNIX account. For this example, we'll put it in a UNIX subdirectory of your home directory, called "~/fromthepc".

Under UNIX:

Once the file is moved to UNIX into a subdirectory called "~/fromthepc" (this is an arbitrary example; it can be stored anywhere), run the following program to convert the transport file back into a SAS data set and store it in your home directory (again, an arbitrary choice; you can store your SAS data sets anywhere you want).

  libname storeit '~/';
  filename datain '~/fromthepc';
  proc cimport library=storeit infile=datain;
  run;

SAS will re-create the original SAS data set with its original name (which in our example is called PROJECT4), ready for your use in any UNIX SAS session.

www.usc.edu/its/doc/statistics/sas

then click the SAS® OnlineDoc(tm) link.

  SAS Institute
  Box 8000
  Cary NC 27512
    919/677-8000   http://www.sas.com/