topic Re: Changing currency format when output to Excel from Stored Process in Developers

Changing currency format when output to Excel from Stored Process

deleted_user — Fri, 15 Dec 2006 19:24:05 GMT

I am using the following to output the results of a stored process to Excel.

data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header('Content-disposition',"attachment; filename=name.xls");
run;
%let _odsdest=tagsets.msoffice2k;
%let _odsstyle=sasweb;

My problem is that the output create a Traffic light report, but if a negative $ value is returned, the font is red. However, the background for that cell will also be red because of the traffic light rules. I need to be able to reset the format in Excel to make sure that the font color is black.

What do I need to do to send the desired font color to Excel?

Thanks for any clues.
KeithS

Re: Changing currency format when output to Excel from Stored Process

Cynthia_sas — Fri, 15 Dec 2006 21:52:56 GMT

Are you using PROC PRINT, PROC REPORT or PROC TABULATE for setting your traffic lighting?? User-defined format?? or CALL DEFINE??

The reason I ask is that, when I run this code in SAS (not as a stored process, just as a regular step), I get a red background color and black font in Excel.

You may want to contact Tech Support and have them look over your entire program and suggest how you can change your code to achieve what you want. It seems odd to me that your traffic lighting would make both the foreground and background color red.

cynthia

My foreground color is black when I submit this code:[pre]

data class;
set sashelp.class;
if age gt 14 then age = age * -1;
run;

proc format;
value agebk 1-high = 'white'
low-0 = 'red';
run;

ods msoffice2k file='trafic.xls' style=sasweb;
proc report data=class nowd;
column name age height;
define name /order;
define age /display style(column)={background=agebk.};
define height /display;
run;
ods _all_ close;[/pre]

Re: Changing currency format when output to Excel from Stored Process

deleted_user — Mon, 18 Dec 2006 14:01:34 GMT

Thank you for the response.

My code is producing HTML with a DATA _NULL_. The background in the is set based on values in the data. The Stored Process is run via the Information Delivery Portal.

Once again, thanks for the response and I will check with SAS support.

KeithS