CONVERTING SAS DATA SETS TO OTHER FILE TYPES Several ways exist to import data from external files into SAS, such as data stored in Excel worksheets. A companion article on this website describing these data transfer techniques is found at: http://www.uoregon.edu/~robinh/data_trnsf1.html You can correctly assume that several data export options are also available. SAS has an EXPORT wizard from the menus and a procedure called PROC EXPORT that work in an analogous manner to the IMPORT process. Exporting data through them is satisfactory if you don't need to specify how the data will be formatted in the resulting Excel worksheet. (Note that PROC EXPORT only works if you have a license for SAS/Access). Exporting one SAS dataset to a single worksheet in a new Excel workbook with PROC EXPORT is quite simple. You need to remember that Excel is limited to a maximum size of 256 columns (variables) by 65536 rows (observations). If your dataset is larger in either dimension, you'll need to split it into smaller subsets and merge or append them together. The EXPORT procedure is not as well-developed in version 8.2 as PROC IMPORT when working with multiple worksheets or to transfer data formats. Many improvements have been made with version 9 of SAS which has been available for over a year now. PROC EXPORT and the Export Wizard are helpful tools for exporting SAS datasets to other programs or files. This procedure is available for converting SAS datasets to comma delimited files and excel files in versions 97, 2000, and 2002 under Windows. One application of PROC EXPORT is to write SAS datasets to other formats when you have specific analyses, graphics, or functions you wish to perform with other software, or when transferring your data to other users who may not be able to run SAS software. As with PROC IMPORT, the Export Wizard steps you through the process of writing an Excel workbook, while at the same time (optionally) writing and saving the program statements. To use the Export Wizard: * With the SAS Program Editor active, select File Export Data * Select the folder and the specific data set that you wish to export You will also be given the option of naming a file for saving the program statements. Doing this is recommended to document the conversion procedure. It is simple to rerun the program to update your export program file should you update or correct SAS data sets, rather than step through the wizard again and again. When you export a SAS dataset to a delimited file the column names will automatically be entered in the first row. It is not possible to omit them directly within PROC EXPORT. But for clear documentation, why would you want to? You would lose the variable names that specify their inherent order in the data file. Without them could cause you some serious problems later on. Most programs that can read a pipe-delimited dataset can omit reading a first line that contains variable names, if necessary. Other options for specialized formatting of data or saving only a subset of variables or records are available. See the online help, or the SAS procedures manual for PROC EXPORT. Converting a SAS Dataset to an External Text File With a DATA step you can write the contents of a dataset set or specify selected variables to an external text file with your choice of delimiters: FILENAME dat "c:\data\test_0999.txt"; DATA _null_; SET sashelp.class; FILE dat; *PUT (_all_) ('09'x); * For tab separated; *PUT (_all_) (','); * For comma separated; PUT (_all_) (:); * For space separated; RUN; To write a comma-separated-value file (CSV), the Output Delivery System quickly does the job: ODS tagsets.csv body='c:\sas\data\farms.csv' ; PROC PRINT data=farms NOobs; TITLE " "; run; ODS tagsets.csv close; PROC EXPORT converts a SAS dataset to a text file with several possible delimeters. Assume you have a SAS dataset on your computer called mydat.sas7bdat that is located in the directory c:\sas\data and you want to convert it into a comma separated value text file called mydat.csv. The following statements in PROC EXPORT will convert this SAS data set into a text file called mydat.csv. LIBNAME dat "c:\sas\data\"; PROC EXPORT DATA=dat.mydat OUTFILE="c:\sas\data\mydat.csv" DBMS=csv REPLACE; RUN; How to save a delimited file with EXPORT on unix On a unix system, to save sas dataset to a csv file note the following: When you submit the command file in batch, add the -noterminal option to get this next step to work, i.e.: sas exprt -noterminal where the file exprt.sas contains the EXPORT commands: PROC EXPORT DATA=tst outfile="~/sas/prgms/exprt1.csv" dbms=csv; delimiter=','; RUN; PROC EXPORT works from a windows point of view where TERMINAL is the default, that is, where an interactive environment (physical display) is expected to be available. NOTERMINAL causes SAS to not evaluate the execution environment. When you specify NOTERMINAL, SAS uses option settings that are associated with the BATCH system option, which is what running it in batch with unix expects. Convert a SAS Dataset into an Excel Worksheet The following export procedure places a SAS dataset into an Excel worksheet. This example writes an Excel file called inventory.xls with a worksheet name called 'prices' (observe this name in the lower left corner tab of the Excel worksheet). SAS places the variable names in Row 1 of the Excel worksheet. DATA prices; FORMAT product $2. ; INPUT @1 product $2. new_price on_hand ; CARDS; AA 16.95 5 BB 19.99 6 CC 10.45 8 AA 22.01 9 CC 21.35 1 ; PROC EXPORT DATA=prices OUTFILE="c:\sas\data\inventory.xls" DBMS=excel2000 REPLACE; RUN; The DBMS option is used to specify what kind (and version) of the output file is to be created. If you have problems exporting a SAS data set into an excel file, note that DBMS=excel97 and DBMS=excel2002 specifications are also available; this example is set up for use with excel2000. If the Excel file already exists, or if you aren’t sure, include the REPLACE option; otherwise, SAS will not over-write it and thus not save the data set you want to export. Note a Limitation on the Length of the File Name with PROC EXPORT. When PROC EXPORT places SAS datasets into Excel files, the length of the whole name of the Excel file, including the drive specification and path, cannot exceed 64 characters. Excel file names are usually "short", but a long path for the location of the file may be a reality. You should either change the location of the file or use the driver mapping to make the path shorter. Export SAS Datasets to Excel Spreadsheets via LIBNAME with Version 9 The following example was described and tested under SAS 9.1, Windows 2000 by Phil Mason and sent as one of his Daily Tips (#64 sent on 9/18/2003). With Version 9 of SAS you can read from and write to multiple spreadsheets in an Excel Workbook from DATA steps and procedures. This capability simplifies the process of exporting data to EXCEL available with PROC EXPORT or other methods. The following program shows how to write an Excel file called 'test.xls' that contains a worksheet called 'class'. The worksheet will have the variable names of the dataset placed in the first row, followed by respective values in each column across rows. Note this capability does not have a REPLACE option available with PROC EXPORT. An error message demonstrates some limitations with the Excel engine, preventing a sheet from being overwritten once it has been written. It is next shown how it is possible for you to write additional sheets within the same EXCEL file. Documentation on Excel LIBNAME syntax can be found at: http://v9doc.sas.com/cgi-bin/sasdoc/cgigdoc?file=../acpcref.hlp/a002107496.htm LIBNAME out excel 'c:\test.xls' ; DATA out.class; SET sashelp.class ; RUN; * try to replace the excel worksheet; DATA out.class; SET sashelp.class ; RUN; ERROR: The MS Excel table class has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This table will not be replaced. * make a new EXCEL dataset; DATA out.shoes; SET sashelp.shoes; RUN; NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. * clear the LIBNAME so one can read the spreadsheet from Excel; LIBNAME out clear; NOTE: Libref OUT has been deassigned. This example reorders the data to your specifications and also places spaces within the variable names written to the first row in the Excel workbook. OPTIONS VALIDVARNAME=any ; LIBNAME exbk excel 'c:\sas\excel\new99.xls' ver=2000; DATA exbk.'Class List'n; RETAIN 'First Name'n 'Height (in)'n 'Weight (lbs)'n Gender Age; SET sashelp.class (rename=(Name='First Name'n Height='Height (in)'n Weight='Weight (lbs)'n Sex=Gender)); run; LIBNAME exbk clear; You can export multiple datasets to one Excel file with the ODS markup statement: ods markup tagset=excelxp file='c:\sas\test.xls'; proc print data=sashelp.air NOobs ; run; proc print data=sashelp.class NOobs; run; ODS markup close; You will likely need to modify the tab names and page layout, though this approach is an effective way to send the contents of several datasets quickly. Exporting Datasets with Missing Data Suppose you need to export a dataset with numeric and character columns containing missing values. However, the flat file should not contain the default dot or space used for missing values. Use either an empty string or the pattern NULL. The Export Wizard defaults to the dot or space for the missing values. A way to override the dot or space when writing data to an comma separated value (CSV) file is to simply include missing='' in an OPTIONS statement. The character you type between the single quotes will replace the default missing character. For example: OPTIONS missing='m'; /* Specify an m to be used for all Missing Values */ DATA temp; SET class; IF (weight > 250) THEN weight = . ; /* Convert outlier to missing */ run; PROC EXPORT DATA= work.temp OUTFILE= "c:\temp\test.csv" DBMS=CSV REPLACE ; RUN; Generate Excel Files with the Output Delivery System Generating Excel spreadsheets containing SAS datasets using the Output Delivery System (ODS) is easy. ODS CSV writes a text file of values separated by commas and named with a CSV extension; ODS HTML writes a file with an XLS extension. Both files can be read by Excel as spreadsheets. ODS CSV FILE=”c:\sas\data\class.csv”; PROC PRINT DATA=class; RUN; ODS CSV close; ODS HTML FILE=”c:\sas\data\class.xls”; PROC PRINT DATA=class NOobs; RUN; ODS HTML close; You can print variable labels to the Excel file if you add the Label option on the PROC PRINT statement. However, with this data transfer approach you cannot get both variable names and labels. The NOobs option omits the sequentially numbered column named ‘obs’ that SAS prints by default. A VAR statement will print selected variables in the order you specify rather than all variables in the dataset. ODS does not actually produce Excel files since they are not an intended ODS destination; however, the files can be opened with Excel since it sees the registered extensions of .XLS or .CSV and recognizes these as valid file names to open. Additional work will very likely be required to customize the Excel file as you would like to see it. These techniques are beyond the scope of this article. However, an introduction to customizing Excel files is given at: http://www2.sas.com/proceedings/sugi28/012-28.pdf This resource discusses a variety of methods that will allow you to customize the appearance of nearly every part of the Excel file created from ODS. Export Two or More SAS Datasets to Separate Worksheets in One Excel Workbook DATA class class1 class2 class3; INPUT obs Name $ Sex $ Age Height Weight; OUTPUT class; IF MOD(_n_,3) eq 0 THEN OUTPUT class1; IF MOD(_n_,3) eq 1 THEN OUTPUT class2; IF MOD(_n_,3) eq 2 THEN OUTPUT class3; CARDS; 1 Alice F 13 56.5 84.0 2 Becka F 13 65.3 98.0 3 Gail F 14 64.3 90.0 4 Karen F 12 56.3 77.0 5 Kathy F 12 59.8 84.5 6 Mary F 15 66.5 112.0 7 Sandy F 11 51.3 50.5 8 Sharon F 15 62.5 112.5 9 Tammy F 14 62.8 102.5 10 Alfred M 14 69.0 112.5 11 Duke M 14 63.5 102.5 ; Version 9 allows you to specify a new worksheet to export data, if the workbook currently exists, with the SHEET= option. If the workbook does not already exist, SAS will make a new one. PROC EXPORT DATA=class2 OUTFILE="c:\sas\excel\x_class.xls" DBMS=excel2000 REPLACE; SHEET='class2'; RUN; EXPORT multiple worksheets with ODBC Open DataBase Connectivity (ODBC) is an industry wide standard way to communicate among databases. By using the ODBC API an application can be written that can perform database transactions on any database that has an ODBC driver. This means if you write an application using ODBC you can use it to interact with SQL Server, DB2, Oracle, SQL Anywhere, Microsoft Access, and Fox. If your database engine has an ODBC driver for it then your application can interact with it. Assume you have multiple SAS datasets, either temporary or permanent, that you want to write into one Excel workbook. ODBC allows this process with the following example. Note that the worksheet names in the Excel file will be SAS dataset name as given on the DATA statement. One important point to remember is you first need to delete the Excel workbook if it currently exists, SAS can do it for you, as shown below. ODBC does not have a "replace" option as found with PROC EXPORT. The LIBNAME statement allows you to connect to your Excel workbook as if it were a library. You may copy all SAS datasets to this library and each will appear as a separate worksheet. LIBNAME _lbxls odbc NOprompt= "dsn=Excel Files; dbq=c:\sas\excel\tst_export.xls"; DATA _lbxls.class1; SET class1; run; DATA _lbxls.class2; SET class2; run; DATA _lbxls.class3; SET class3; run; LIBNAME _lbxls clear ; * release the xls file back to the system ; RUN; Note: with the option 'clear' SAS releases control of the file and allows Excel to access it. * First delete the *.xls file, if it exists. Delete it manually, if necessary; DATA _null_; rc1 = FILENAME("outfile","c:\sas\excel\tst_export.xls"); rc2 = FEXIST("outfile"); _msg_=sysmsg(); PUT _msg_ =; IF rc1=0 and rc2=1 THEN rc3 = FDELETE("outfile"); rc4=FILENAME("outfile",''); PUT rc1= rc2= rc3= rc4= ; run; /* FILENAME returns 1(???) if the operation was successful, 0 if it was not successful FEXIST returns 1 if the external file that is associated with fileref exists 0 if the external file does not exist The return code for FILENAME can be several different numbers depending on the situation that's encountered. FEXIST and its cousin FILEEXIST simply test the presence of a fileref / file. True or false. FDELETE: Deletes an external file or an empty directory It returns 0 if the operation was successful or NE 0 if it was not successful. or you can use: OPTIONS xsync noxwait; %SYSEXEC del c:\sas\excel\tst_export.xls; */ An example which reorders the data and places spaces within the variable names in Excel. options VALIDVARNAME=ANY ; libname exbk excel 'c:\sas\excel\new99.xls' ver=2000; DATA exbk.'Class List'n; retain 'First Name'n 'Height (in)'n 'Weight (lbs)'n Gender Age; SET sashelp.class (rename=(Name='First Name'n Height='Height (in)'n Weight='Weight (lbs)'n Sex=Gender)); run; libname exbk clear;