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;
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
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.