topic Re: Proc Report Scientific Notation in SAS Programming

Proc Report Scientific Notation

BobbyG0627 — Sun, 23 Jan 2022 21:51:19 GMT

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;

Re: Proc Report Scientific Notation

ghosh — Sun, 23 Jan 2022 22:02:45 GMT

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

Re: Proc Report Scientific Notation

BobbyG0627 — Sun, 23 Jan 2022 22:59:01 GMT

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

Re: Proc Report Scientific Notation

WarrenKuhfeld — Sun, 23 Jan 2022 23:07:44 GMT

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

Re: Proc Report Scientific Notation

PaigeMiller — Sun, 23 Jan 2022 23:43:04 GMT

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

Re: Proc Report Scientific Notation

BobbyG0627 — Sun, 23 Jan 2022 23:44:24 GMT

yes

Re: Proc Report Scientific Notation

BobbyG0627 — Sun, 23 Jan 2022 23:45:26 GMT

it is stored as character

Re: Proc Report Scientific Notation

ChrisNZ — Mon, 24 Jan 2022 00:37:13 GMT

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;

Re: Proc Report Scientific Notation

japelin — Mon, 24 Jan 2022 00:41:32 GMT

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:

Re: Proc Report Scientific Notation

Cynthia_sas — Mon, 24 Jan 2022 00:52:00 GMT

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

Re: Proc Report Scientific Notation

BobbyG0627 — Mon, 24 Jan 2022 01:38:26 GMT

THANK YOU!!! THAT WORKED!

Re: Proc Report Scientific Notation

Tom — Mon, 24 Jan 2022 05:11:23 GMT

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;

Re: Proc Report Scientific Notation

BobbyG0627 — Mon, 24 Jan 2022 13:48:01 GMT

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

Re: Proc Report Scientific Notation

Tom — Mon, 24 Jan 2022 14:04:25 GMT

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