topic Proc Report exporting to excel displays negative numbers as text in ODS and Base Reporting

Proc Report exporting to excel displays negative numbers as text

LisaSAS — Fri, 08 Dec 2017 22:22:32 GMT

I'm trying to do an export to an excel file with formatting using Proc Report. My problem is once I open my excel file, the negative numbers are stored as text. I want to be able to have the negative numbers display as numbers and not text fields.

ods _all_ close;

ods listing close;

ods tagsets.ExcelXP path = 'file path'

file = "name of excel spreadsheet.xls" style=MINIMAL;

ods tagsets.ExcelXP

options(sheet_name= "Totals");

PROC REPORT DATA=WORK.DataFile

STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]

STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]

STYLE(HEADER) = [BACKGROUND=LIGHT BLUE FOREGROUND=WHITE BOLD FONT_SIZE=2];

COLUMNS ….listing of my columns to display in the file;

DEFINE Field Name from WORK.DataFile / ‘name to display in excel’

style(column)={tagattr="format:$#,###;$-#,###"};

RUN;quit;

Re: Proc Report exporting to excel displays negative numbers as text

Cynthia_sas — Sat, 09 Dec 2017 15:30:16 GMT

Hi:

Since you did not post ALL your code or any data for folks to test with, it is hard to comment. When I use some fake data from SASHELP.CLASS and add some negative numbers, I do not observe the behavior you report. I am running SAS 9.4 M3.

Here's what I see:

Highlighting with red and purple was how I called attention to both columns. As you see, I have both positive and negative numbers in each column. It seems to me that the negative numbers are represented correctly and are NOT showing the negative numbers as text. In fact, if I inspect the format in Excel for one of the negative cells, I see that my Custom format appears:

Here's the code I tested with. I fixed a few issues in your code. BOLD should be specified as fontweight=bold in the style override. The background of minimal is white, so you didn't need that. LIGHTBLUE as a color specification should not have a space.

data class;
  set sashelp.class;
  neg1 = 11;
  neg2 = 22;
  if age = 12 then neg1 = height*-11.1;
  else if age = 11 then neg2 = weight * -13.3;
run;

ods _all_ close;
 
 ods tagsets.ExcelXP path = 'c:\temp' 
                     file = "testneg.xml" style=MINIMAL 
                     options(sheet_name= "Totals");

     PROC REPORT DATA=class
           STYLE(HEADER) = [BACKGROUND=LIGHTBLUE FOREGROUND=black fontweight=BOLD ];
		   where age in (11,12);

           COLUMNS age name neg1 neg2;
           define age / order;
		   define name / display;
           DEFINE neg1 / 'Negative 1'
             style(column)={color=red fontweight=bold tagattr="format:$#,###;$-#,###"};
		   define neg2 / 'Negative 2'
             style(column)={color=purple fontweight=bold tagattr="format:$#,###;$-#,###"};
     RUN; 
ods tagsets.excelxp close;

Hope this helps. As I said, I am running SAS 9.4. The ExcelXP tagset I'm using as found in my log is:

Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015).

cynthia

Re: Proc Report exporting to excel displays negative numbers as text

LisaSAS — Mon, 11 Dec 2017 15:35:17 GMT

Cynthia,

Thank you for your quick response. I did try your code with my data and it still did not work. I then went back to look at the way I was pulling in the data. When I'm creating my work table to use for my reporting, I was formatting my field (see below). I took the formatting off and then my code worked!

/*sum(MyField) FORMAT=DOLLAR12. as MyField*/

sum(MyField) as MyField