The SAS Output Delivery System and reporting techniques

Avoid Scientific Notation when creating XLS

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Avoid Scientific Notation when creating XLS

Hi,

 

I'm using the following code to create an XML based Excel file. One of the fields in the "oas_calc_RDA" dataset is 9 character field containing CUSIPs. I am assigning them a format $9. before creating file.

 

However, when I open this file in Excel, some of the CUSIPs are incorrectly interpreted as scientific notation. Is there a work-around to preserve the character format when this file is opened in Excel?

 

Thank you for your help!

Asim

 

 

ods tagsets.excelxp file="&output_dir./Metrics_&run_id..xls" style=normal;
    ods tagsets.excelxp options(sheet_name="Total");
    PROC PRINT DATA=oas_calc_RDA noobs;
    RUN;

ods tagsets.excelxp close;

 

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Trusted Advisor
Posts: 1,130

Re: Avoid Scientific Notation when creating XLS

[ Edited ]

we could preserve the data format in excel file, by TAGATTR clause

 

Here is the code you could try

 

 

ods tagsets.excelxp file="&output_dir./Metrics_&run_id..xls" style=normal;
    ods tagsets.excelxp options(sheet_name="Total");
    PROC PRINT DATA=oas_calc_RDA noobs;
    var <variable names separated by space>;
    var <variable for which the format has to be preserved> / style={TAGATTR='format:text'};
    RUN;
ods tagsets.excelxp close;

 

 

Please try and let me know if it helped.

 

Thanks,

Jag

Thanks,
Jag

View solution in original post


All Replies
Solution
‎09-25-2015 06:23 AM
Trusted Advisor
Posts: 1,130

Re: Avoid Scientific Notation when creating XLS

[ Edited ]

we could preserve the data format in excel file, by TAGATTR clause

 

Here is the code you could try

 

 

ods tagsets.excelxp file="&output_dir./Metrics_&run_id..xls" style=normal;
    ods tagsets.excelxp options(sheet_name="Total");
    PROC PRINT DATA=oas_calc_RDA noobs;
    var <variable names separated by space>;
    var <variable for which the format has to be preserved> / style={TAGATTR='format:text'};
    RUN;
ods tagsets.excelxp close;

 

 

Please try and let me know if it helped.

 

Thanks,

Jag

Thanks,
Jag
Contributor
Posts: 30

Re: Avoid Scientific Notation when creating XLS

Jagadishkatam,

Thank you for the reply! It worked. Smiley Happy

Asim
Super User
Posts: 10,500

Re: Avoid Scientific Notation when creating XLS

You may also have to use a tagattr to force the result to be considered text. I suspect that if you open your "xls" file, which is XML, in a text editor you will find that the property for that field is being set to numeric.

Contributor
Posts: 30

Re: Avoid Scientific Notation when creating XLS

Balladrw,

tagattr works. Thank you for your help!
Asim
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 934 views
  • 0 likes
  • 3 in conversation