Proc Report Scientific Notation

[BobbyG0627]

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;

[ghosh]

/style={TAGATTR='format:text'};

[BobbyG0627]

thank you, but I mentioned in my post that I tried that, same result unfortunately.

[PaigeMiller]

---

@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 ?

[BobbyG0627]

yes

[WarrenKuhfeld]

Your life will generally be easier if you store IDs that are long strings of numerals as character variables rather than numeric.

[BobbyG0627]

it is stored as character

[Cynthia_sas · Accepted solution]

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

[BobbyG0627]

THANK YOU!!! THAT WORKED!

[ChrisNZ]

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;

[japelin]

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:

[Tom]

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;

[BobbyG0627]

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:###############'};

[Tom]

---

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