Creating Output Files with ODS for Use by Excel - Exchanging Data Between SAS and Microsoft Excel - page 123

When a text file with the name that ends in TAB is written with tabs as delimiters and opened with Excel,
any commas embedded in the file are treated as text and placed into the cells. This is because the Excel
workbook expects tabs to separate fields when the file name ends in “.TAB”.
Example 8.8.5 Save Leading Zeroes in Character Fields Sent to Excel
Here is a clever little trick I found that has helped me to preserve leading zeros and force an Excel cell to
hold a character value even if it “looks” numeric. These options are used together to convert numeric
values in a SAS dataset to a test field in an Excel spreadsheet. The simple code below creates the dataset
“by_type”, which will do the trick. The option “PREPEND_EQUALS” will place an equals sign in front of
quoted numeric values, but only when the “QUOTE_BY_TYPE” option is set to “YES” to place numeric
values in quotes. I have found this to be a useful pair of options because Excel treats the value as a text
field. So, it is left justified, and leading zeros are retained. Then, the numeric value is left in quotes in the
CSV file, but is displayed as a number in Excel.
Figure 8.18: Code to Retain Leading Zeros When Sending Character Values to Excel.
ODS TAGSETS.CSVALL BODY='c:\temp\csv\by_type.csv'
OPTIONS(QUOTE_BY_TYPE='Yes' PREPEND_EQUALS='Yes');
DATA by_type;
a = "010";
b = a * 2;
RUN;
PROC PRINT DATA=by_type;
RUN;
ODS TAGSETS.CSVALL CLOSE;
The code in Figure 8.18 produces a *.csv file, and it can be opened by Excel as a worksheet called
“by_type”. It has two character variables and one observation. The PROC PRINT output is sent to the CSV
file using the CSVALL tagset template.
Figure 8.19: CSV Output File Produced by the Code in Figure 8.18.