I have tried several different ways to prevent excel from showing a field as scientific notation in my outputs.
the latest of the code is below using format:#
I have tried format: text, I tried different widths.
In Excel, the output always defaults to "General". If I change it manually to Numeric with no decimals it shows the correct number but programmatically I can not get this work.
This is what I get in Excel and below that is the code I last used:
PROC REPORT DATA = work.output nowd headline headskip split='*'
style(header)=[font_face='Arial' font_size=9pt font_weight=bold background=lightgrey foreground=black just=c tagattr='wrap:yes']
style(column)=[font_face='Arial' font_size=9pt just=l tagattr='wrap:no'];
Define TrackingNumber / display "Internal Tracking ID" style={TAGATTR="format:#" width=1.5in};
RUN;
Hi:
Unfortunately, even when you define a variable as character in SAS, when Excel gets the value, it uses it's own default settings to show the number in the cell. Most of the time, it makes the column width too narrow for long strings and it decides on a numeric format, even for Character strings, that's why using TAGATTR should work for you. Instead of sending a text format, I find it best with Excel to use code like this:
data makebig;
length charvar $15 ;
set sashelp.class;
where age = 14;
x = age * height * weight*1713211972;
charvar = put(x,$15.);
run;
ods excel file='c:\temp\tagattr_xl.xlsx';
proc print data=makebig;
var name age sex height weight;
var charvar x /
style(data)={tagattr='format:###############'};
format x 15.;
run;
proc report data=makebig;
column name age sex height weight charvar x;
define charvar /
style(column)={tagattr='format:###############'};
define x / f=15.
style(column)={tagattr='format:###############'};
run;
ods excel close;
When I open the output in Excel, I get the number without scientific notation in both cells (charvar and x).
Cynthia
/style={TAGATTR='format:text'};
thank you, but I mentioned in my post that I tried that, same result unfortunately.
@BobbyG0627 wrote:
thank you, but I mentioned in my post that I tried that, same result unfortunately.
From re-reading your original post, you tried something different. Did you really try the suggestion from @ghosh ?
yes
Your life will generally be easier if you store IDs that are long strings of numerals as character variables rather than numeric.
it is stored as character
Hi:
Unfortunately, even when you define a variable as character in SAS, when Excel gets the value, it uses it's own default settings to show the number in the cell. Most of the time, it makes the column width too narrow for long strings and it decides on a numeric format, even for Character strings, that's why using TAGATTR should work for you. Instead of sending a text format, I find it best with Excel to use code like this:
data makebig;
length charvar $15 ;
set sashelp.class;
where age = 14;
x = age * height * weight*1713211972;
charvar = put(x,$15.);
run;
ods excel file='c:\temp\tagattr_xl.xlsx';
proc print data=makebig;
var name age sex height weight;
var charvar x /
style(data)={tagattr='format:###############'};
format x 15.;
run;
proc report data=makebig;
column name age sex height weight charvar x;
define charvar /
style(column)={tagattr='format:###############'};
define x / f=15.
style(column)={tagattr='format:###############'};
run;
ods excel close;
When I open the output in Excel, I get the number without scientific notation in both cells (charvar and x).
Cynthia
THANK YOU!!! THAT WORKED!
It works fine for me. What do you do differently?
data OUTPUT;
TRACKINGNUMBER='123456789012345';
run;
ods excel file="&wdir\t.xlsx";
proc report data = OUTPUT nowd headline headskip split='*'
style(header)=[font_face='Arial' font_size=9pt font_weight=bold background=lightgrey foreground=black just=c tagattr='wrap:yes']
style(column)=[font_face='Arial' font_size=9pt just=l tagattr='wrap:no'];
define TRACKINGNUMBER / display "Internal Tracking ID" style={tagattr="format:#" width=1.5in};
run;
ods excel close;
How do you output the data to Excel?
In my environment, I can output as follows without any problem by ODS EXCEL.
data output;
TrackingNumber='201913070139400';
run;
ods excel;
/* this is your code */
PROC REPORT DATA = work.output nowd headline headskip split='*'
style(header)=[font_face='Arial' font_size=9pt font_weight=bold background=lightgrey foreground=black just=c tagattr='wrap:yes']
style(column)=[font_face='Arial' font_size=9pt just=l tagattr='wrap:no'];
Define TrackingNumber / display "Internal Tracking ID" style={TAGATTR="format:#" width=1.5in};
RUN;
ods excel close;
results:
HOW did you try to tell EXCEL to treat the cell(s) as character strings?
Following the advice from this SAS article to set the TYPE worked for me.
data test;
x='201913070139400';
run;
ods excel file='c:\downloads\digits.xlsx';
proc report data=test;
define x / style(column)={tagattr="type:String"};
run;
ods excel close;
this worked for me with what appears to be the same result as yours in the define
Define TrackingNumber / display "MCO Internal Tracking ID"
f=$15. style(column)={tagattr='format:###############'};
@BobbyG0627 wrote:
this worked for me with what appears to be the same result as yours in the define
Define TrackingNumber / display "MCO Internal Tracking ID"
f=$15. style(column)={tagattr='format:###############'};
But that defines the cell(s) in Excel as NUMBERS not STRINGS. Excel uses the same floating point representation of numbers as SAS does and so will have the same problem with storing long strings of digits as numbers precisely.
The little green triangle is how Excel shows that the cell with a string the could be converted to a number is actually being stored as a string.
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.