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;
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;
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.
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;
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.