The SAS Output Delivery System and reporting techniques

SAS to excel Conversion

Not applicable
Posts: 0

SAS to excel Conversion

Can some one pls explain how we can convert a SAS data set to an excel sheet using ODS?

Thank you very much in advance.
Posts: 9,366

Re: SAS to excel Conversion

Posted in reply to deleted_user
When you have a SAS data set, you have several options to get the data to Excel:
1) PROC EXPORT -- using the DBMS option will create either Excel, CSV, DBF or WK4 (Lotus) files if you have SAS/Access for PC File formats.

But, since you asked specifically about ODS, the ODS methods to get a PROC PRINT of your data set (or procedure output) into Excel are:
2) create comma separated file -- ODS CSV, CSVALL -- which create a CSV (comma separated value file) that Excel 97 or higher can open
3) create HTML-based file -- ODS HTML3, ODS CHTML, ODS PHTML, ODS MSOFFICE2K, ODS HTML -- all of these destinations create an HTML file that differing versions of Excel can open. Excel 97 should be able to open HTML files -- except for those created with Microsoft HTML using MSOFFICE2K -- which you need Office 2000 or higher to open.
4) create XML-based (Spreadsheet Markup Language) file -- ODS TAGSETS.EXCELXP creates a SpreadsheetML file that can be opened with Excel 2002/2003 or higher.

Try this:
ods listing close;
ods csv file='comma.csv';
ods tagsets.excelxp file='spreadsheet_ml.xls' style=sasweb;
ods msoffice2k file='ms_html.xls' style=sasweb;
ods html file='ht4.xls' style=sasweb;
ods html3 file='ht3.xls' style=sasweb;
ods phtml file='phtml.xls' style=minimal;
ods chtml file='chtml.xls';

proc print data=sashelp.class;
var name age height weight;

proc freq data=sashelp.class nlevels;
table age height weight sex;

ods _all_ close;
ods listing;

When this job finishes, you will have a series of different files. All the files were given .XLS file extensions -- this is a convenience only -- we could have used their "regular" file extensions of .CSV, .HTML, .XML, etc. However, if you open each file in NOTEPAD, you will see that the files are comma-separated or HTML or XML files. Then, if you open each file with Excel, you will see that the fonts and styles used by ODS have been respected (or not) depending on the different destination used. After you review the output, you can then decide which destination gives you the sheet you want in Excel.

By giving the files a .XLS extension, you can "fool" the Windows registry into launching Excel when you double click on the file name. If you attempt to save each file from within Excel, you will see that Excel knows which type of file it opened (CSV vs HTML, etc) and will attempt to save to that same file extension unless you change the file type.

Note that ALL the output goes into 1 worksheet for every destination EXCEPT the ExcelXP destination -- which automatically makes a sheet for every table created by the procedure(s) within the ODS "sandwich". The PROC PRINT step, inside the ODS sandwich, would be the way to get your data rows into Excel using ODS. But, you also have the added advantage (illustrated here with the PROC FREQ step) of having almost ANY procedure analysis/output go to Excel, as long as it is inside your ODS sandwich. If all you care about is getting data rows into Excel, you would only need a PROC PRINT step inside your ODS "sandwich".

There are a few other methods still available to you with ODS, however, they are more advanced and require that you understand how SAS uses TAGSET Templates -- these methods involve using the SYLK tagset or the TABLEEDITOR tagset. You can read about them at the ODS MARKUP page at:

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation