Formatting of the data during FTP

Rohit Umarjikar · Posted: Tue Jul 24, 2012 3:15 pm

We are trying to attach a mianframe dataset information in .XLS format and sending a mail to some third party users. We are achcieving this conversion of PS file to XLS file through SAS utility by using ODS tagsets excelxp statements via XML formatting. While writing the XML code, the alphanumeric data with value 'E' inbetween numbers are treated as numeric and truncation happens. For example if the data is 075E00 in mainframe file, the excel is displaying it as 000075(i.e 75 * 10^0 = 75) which is scientific representation of numbers. We need to report the real data and we dont need such truncation to happen. Tried with lots of formatting overrides available with ODS tagsets commands but nothing worked. Based on the value, the alphanumeric column is interpreting its own data type in the XML code.

Robert Sample · Posted: Tue Jul 24, 2012 4:56 pm

1. Why are you not raising this issue with SAS Institute instead of this forum? SAS is MUCH more likely to be able to help you.

2. Did you try using a plain FORMAT statement instead of monkeying around with the ODS tagsets?

3. Based upon what you posted, there actually is no problem that I can see. 075E00 would be the value 75, which is what it should be. I'm sure there's some problem that you didn't post, but what you posted shows normal and expected behavior.

vasanthz · Posted: Tue Jul 24, 2012 4:56 pm

Hello,
You can change the style attribute of the TAGSET and ask it to treat it as character.
Style attribute:
style(data)={htmlstyle="mso-number-format:\@"};

Example:

vasanthz · Posted: Tue Jul 24, 2012 5:53 pm

This link has the same query and a solution, try it out and let us know if it helped.
listserv.uga.edu/cgi-bin/wa?A2=ind1009b&L=sas-l&O=A&P=2890

For some reason I am unable to test(craving to test) the above link and get it working. I think its because the solution was for Excel 2007 & 2003 and I have Excel 2010 :S

Rohit Umarjikar · Posted: Mon Jul 30, 2012 4:52 pm

Thanks All

dick scherrer · Posted: Mon Jul 30, 2012 7:21 pm

Hello,

Does that mean it worked?

If you post your solution, it could help someone with a similar issue later.

vasanthz · Posted: Mon Jul 30, 2012 7:43 pm

Ya, If it worked please post the option you used and possibly the Excel version and SAS version.