The SAS Output Delivery System and reporting techniques

Character not exporting correctly in ODS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Character not exporting correctly in ODS

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;


Accepted Solutions
Solution
‎03-09-2017 08:52 PM
Valued Guide
Posts: 505

Re: Character not exporting correctly in ODS

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;


View solution in original post


All Replies
Grand Advisor
Posts: 16,397

Re: Character not exporting correctly in ODS

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.  

Solution
‎03-09-2017 08:52 PM
Valued Guide
Posts: 505

Re: Character not exporting correctly in ODS

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;


Post a Question
Discussion Stats
  • 2 replies
  • 151 views
  • 2 likes
  • 3 in conversation