topic Character not exporting correctly in ODS in ODS and Base Reporting

Character not exporting correctly in ODS

Q1983 — Thu, 09 Mar 2017 22:11:51 GMT

data test;

set test1;

run:

sample data

ln_no agency_number /*note the data type is a character even though it displays as a numeric*/

111

112 222222222222 /*In ODS it displays like this 2.22222E+11 */

113 12-225-11556

When I use ODS output if the number exceeds 10 characters it changes to a hexidecimal number. See above. Remember its already a character yet truncates and produces the E+ a number in ODS. How can I have it produce the actual number? This seems to occur only when the field exceeds 10 char

ODS TAGSETS.ExcelXP

options(sheet_interval='none'

default_column_width='15'

sheet_name="Detail"

center_horizontal="no"

frozen_headers = "yes"

Orientation='Landscape'

embedded_titles='Yes'

fittopage="No"

blackandwhite="No"

Embedded_Footnotes='Yes'

absolute_column_width='15'

autofit_height="Yes");

PROC REPORT DATA= test headskip split='*' wrap nowd

style(report)=[background=black cellspacing=10 just=center font_size=11pt font_face="Calibri" bordercolor=black borderwidth=1]

style(column)=[background=white font_size=10pt bordercolor=black borderwidth=1]

;

COLUMNS LN_NBR agency_number

;

FOOTNOTE1 justify=Left Height=8pt "&FNote1";

FOOTNOTE2 justify=Left Height=8pt "&ReportName";

run;

ods listing close;

options missing=' ' mlogic mprint;

TITLE; FOOTNOTE;

Re: Character not exporting correctly in ODS

Reeza — Thu, 09 Mar 2017 22:19:27 GMT

1. If you click on the cell do you see the underlying value or the value in scientific notation.

2. Look at TAGATTR if you want to force a format on the value and make sure the SAS format is best12 or something large enough to display all the digits.

Re: Character not exporting correctly in ODS

rogerjdeangelis — Thu, 09 Mar 2017 23:12:54 GMT

As noted by others this appears to fix it

define agency_number / style={tagattr='format:##############;'};


data test;
input ln_no agency_number $15.;
cards4;
111 111111111111
112 222222222222
113 12-225-11556
;;;;
run;quit;

ODS TAGSETS.ExcelXP
file="d:/xls/numfmt.xml"
options(sheet_interval='none'
default_column_width='15'
sheet_name="Detail"
center_horizontal="no"
frozen_headers = "yes"
Orientation='Landscape'
embedded_titles='Yes'
fittopage="No"
blackandwhite="No"
Embedded_Footnotes='Yes'
absolute_column_width='15'
autofit_height="Yes");

PROC REPORT DATA= test headskip split='*' wrap nowd
style(report)=[background=black cellspacing=10 just=center font_size=11pt font_face="Calibri" bordercolor=black borderwidth=1]
style(column)=[background=white font_size=10pt bordercolor=black borderwidth=1]

;
COLUMNS LN_No agency_number
;

define agency_number / style={tagattr='format:##############;'};

FOOTNOTE1 justify=Left Height=8pt "&FNote1";
FOOTNOTE2 justify=Left Height=8pt "&ReportName";

run;
ods listing close;
options missing=' ' mlogic mprint;
TITLE; FOOTNOTE;
ods tagsets.ExcelXP close;