topic Re: Avoid Scientific Notation when creating XLS in ODS and Base Reporting

Avoid Scientific Notation when creating XLS

asimraja — Wed, 09 Sep 2015 00:13:10 GMT

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;

Re: Avoid Scientific Notation when creating XLS

Jagadishkatam — Wed, 09 Sep 2015 01:07:19 GMT

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

Re: Avoid Scientific Notation when creating XLS

ballardw — Wed, 09 Sep 2015 15:24:49 GMT

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.

Re: Avoid Scientific Notation when creating XLS

asimraja — Wed, 09 Sep 2015 15:46:51 GMT

Jagadishkatam,

Thank you for the reply! It worked. 🙂

Asim

Re: Avoid Scientific Notation when creating XLS

asimraja — Wed, 09 Sep 2015 15:47:24 GMT

Balladrw,

tagattr works. Thank you for your help!
Asim