BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Q1983
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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

2 REPLIES 2
Reeza
Super User

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.  

rogerjdeangelis
Barite | Level 11
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;


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 859 views
  • 2 likes
  • 3 in conversation