Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Formatting of the data during FTP

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1737
Location: NY,USA

PostPosted: Tue Jul 24, 2012 3:15 pm    Post subject: Formatting of the data during FTP
Reply with quote

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.
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8119
Location: East Dubuque, Illinois, USA

PostPosted: Tue Jul 24, 2012 4:56 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1506
Location: Chennai

PostPosted: Tue Jul 24, 2012 4:56 pm    Post subject:
Reply with quote

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:
Code:

DATA WELLS; VAL = '075E00'; RUN;                         
ods Msoffice2k file="WELLS.XML" RECORD_SEPARATOR=NONE;   
proc print data=WELLS;                                   
VAR VAL / style(data)={htmlstyle="mso-number-format:\@"};
run;                                                     
Ods msoffice2k close;                                   


Try it out and let us know if it works out.

Edit: The attribute is not working for ExcelXP :s checking now.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1506
Location: Chennai

PostPosted: Tue Jul 24, 2012 5:53 pm    Post subject:
Reply with quote

This link has the same query and a solution, try it out and let us know if it helped.
http://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
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1737
Location: NY,USA

PostPosted: Mon Jul 30, 2012 4:52 pm    Post subject:
Reply with quote

Thanks All
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 30, 2012 7:21 pm    Post subject:
Reply with quote

Hello,

Does that mean it worked?

If you post your solution, it could help someone with a similar issue later.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1506
Location: Chennai

PostPosted: Mon Jul 30, 2012 7:43 pm    Post subject:
Reply with quote

Ya, If it worked please post the option you used and possibly the Excel version and SAS version.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts copying data without knowing location arunsoods DFSORT/ICETOOL 6 Thu Jul 20, 2017 1:03 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us