Avoid Scientific Notation when creating XLS

[asimraja]

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;

[Jagadishkatam · Accepted solution]

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

[asimraja]

Jagadishkatam,

Thank you for the reply! It worked. 🙂

Asim

[ballardw]

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.

[asimraja]

Balladrw,

tagattr works. Thank you for your help!
Asim