Export String Data To CSV file with Corrected Format

BNG — Thu, 12 Aug 2010 16:09:22 GMT

Hi,

When exporting some data with string format (like 00021, 00005,...) to Excel or CSV via PROC EXPORT, the output file always converts the described data into numeric format (like 21, 5,...) automatically.

Could you please let me know whether there are some methods to export the described data to Excel or CSV file with corrected format??

Appreciate your advice!

Re: Export String Data To CSV file with Corrected Format

Cynthia_sas — Fri, 13 Aug 2010 03:13:38 GMT

Hi:
When Excel sees a number with leading zeroes coming in from an exported data set or a CSV or HTML file (when that file is opened using Excel), Excel says -- "oh, I must use the general format for that number" and so Excel 'loses' the leading zeroes -- this causes issues with numbers like product ID or ID numbers or zip codes -- all of which could legitimately need to show leading zeroes.

With CSV, you have no choice when you use ODS or PROC EXPORT to control the format that Excel will use when it opens the file -- mostly because the SAS format for leading zeroes is ignored by Excel. So, the solution is to create either an HTML file or an XML file that Excel can render into spreadsheet form. With either HTML-based output or XML-based output (Spreadsheet Markup Language file), you can send a Microsoft format from SAS and ODS to Excel.

Consider the following data:
[pre]
**1) make some data -- SAMPLE is a character variable;
data tkt_alt;
infile datalines;
input sample $ dest $ type $ amt;
return;
datalines;
00010 CHICAGO TEL 100
00222 CHICAGO TEL 200
00330 GENEVA WEB 300
00040 GENEVA WEB 400
00550 LONDON TEL 500
00066 LONDON TEL 600
00777 LONDON WEB 700
00088 PARIS TEL 800
00990 PARIS WEB 900
;
run;
[/pre]

If you want to send the TKT_ALT data to Excel, it would normally not show the leading zeroes for the SAMPLE variable. However, if I make either an HTML file that Excel can open or an XML file that Excel can open, I can send a Microsoft format from SAS to Excel.
[pre]
ods listing close;

** 2) make an HTML file and use HTMLSTYLE to send a MICROSOFT format to the HTML;
ods msoffice2k file="c:\temp\msofmt.xls" style=sasweb;
proc print data=tkt_alt noobs label;
title 'Using HTMLSTYLE to send Microsoft MSO Format';
var sample /
style(column)={HTMLSTYLE="mso-number-format:00000"};
var dest type amt;
run;
ods msoffice2k close;

** 3) make an XML file and use TAGATTR to send a MICROSOFT format to the XML;
ods tagsets.excelxp file="c:\temp\xpfmt.xls" style=sasweb;
proc print data=tkt_alt noobs label;
title 'Using TAGATTR to send Microsoft Format';
var sample /
style(column)={TAGATTR="Format:00000"};
var dest type amt;
run;
ods tagsets.excelxp close;
[/pre]

Do note that when I give the files a ".XLS" extension, I am merely "fooling" the Windows Registry into launching Excel when the file is double-clicked. You can verify what's inside each file by opening the file with Notepad to see either the HTML tags or the XML tags in the files that are created by ODS.

If you are using SAS Enterprise Guide, you will NOT be able to open the TAGSETS.EXCELXP results with Excel from within SAS Enterprise Guide -- you will have to open Excel and do a File --> Open from within Excel in order to open the file. When you get prompted that the contents of the file do not match the file extension -- do not worry, that is just Microsoft making sure that nothing in the file will corrupt your computer. It's OK to click YES to open the file.

Also note that you cannot cut and paste code directly from the forum posting mechanism into SAS or into an EG code node. You will have to cut and paste the code one time from the forum into Word in order to preserve the line breaks and then cut and paste from Word into SAS in order to have the line breaks recognized in the SAS code window. Then you can submit the code and review the results.

cynthia

topic Export String Data To CSV file with Corrected Format in Microsoft Integration with SAS

Export String Data To CSV file with Corrected Format

Re: Export String Data To CSV file with Corrected Format