Moving Data Between SAS and EXCEL

NC STATE University
Department of Statistics

Moving Data Between SAS and EXCEL, SAS version 6.12

Using SAS version 6.12, this is easier than ever before.

There are 2 methods for moving data back and forth.

     1)Using a data step, you can read or write tab delimited files. 
     2)Using the SAS import|export wizards, you can read or write tab 
       delimited files. (On the pc you can also read and write EXCEL 
       files.)

Either way, it is important that you construct your EXCEL file in the following manner.

1) Enter variable names on the first row. Use variable names that are 8 characters or less in length. It is best to start variable names with a letter and avoid using special characters. If you follow these rules, SAS will be able to use these variable names.

2) Enter only data in your EXCEL file, simple columns of numbers and letters. Keep all other comments and graphs in separate files.

3) You may wish to save your file as a tab delimited file.

How do I save an EXCEL file as a tab delimited file?

1) Using the pull down menus choose: File-->save as-->
2) Enter your file name
3) Choose the filetype: Text(OS2orMSDOS)(.txt)

Using command line commands, how do I create a SAS data set from a tab delimited file?

Entering the following command on the command line will create the SAS data set work.sample1 from the tab delimited file sample1.txt .

  dimport  "sample1.txt"  sample1

Note: A menuing system will be invoked if there is some confusion about your file. At this point, you can follow through the menus or check your file and try again.

Using command line commands, how do I create a tab delimited file from a SAS data set?

Entering the following command on the command line will cause SAS to create the tab delimited text file sample2.txt from the SAS data set work.sample2 .

   dexport  sample2  "sample2.txt"

Using a data step, how do I create a SAS data set from a tab delimited file ?

Here is sample code for reading a tab delimited file.
Notice the options on the infile statement that indicate the file is tab delimited (dlm=) and that the first record is not data (firstobs=).

 
data a;
infile "filename"  dlm='09'x dsd missover firstobs=2;
input x1 x2 x3;

Explanation:
 dlm='09'x  (means tab delimited)
 dsd (means 2 tabs together indicate a missing value)
 missover (means do not go to next line to fill the program data vector;
           each record is a separate observation)
 firstobs=2 (means start reading data from second line. Usually the first line
              contains the variable names.)

Using a data step, how do I create a tab delimited file from a SAS data set ?

Here is sample code for writing a tab delimited text file.

  
       data _null_; set a;
       file 'mydata.txt';
       put var1  '09'x  var2  '09'x var3 '09'x;
       run;

OR

      data _null_; set a;
      file 'mydata.txt';
      put var1 (var2-var5) ('09'x);

Go to: SAS Consulting Home Page
Department of Statistics Home Page

Maintained by: Sandy Donaghy and Joy Smith
Last Modified: Dec 4, 1998