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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.