topic Format issue in ODS and Base Reporting

Format issue

JasonNC — Thu, 06 Oct 2016 20:16:33 GMT

Hi I am getting below format issue

proc format
picture dollrfmt (round)
low - < 0 = '00,000)' (mult=.001 prefix='($')
0 - high = '00,000' (mult=.001 prefix='$');
run;

The number are represented below in 000 format as i want to represent the amounts in 000 's format but i am getting decimals which i don't want

$23,100.00

$5,182.00

$3,325.00

$2,070.00

$18.00

I am expecting the results in below format

$23,100

$5,183

$3,326

$2,070

I am using ods excelp xp tagsets and proc report,I am applying the format in proc report.

Re: Format issue

ballardw — Thu, 06 Oct 2016 20:28:32 GMT

Excel doesn't always honor the SAS format unless you use TAGATTR to specify the override appearance once it gets to Excel in the proc report code.

I suspect that if you look at the cell in Excel for the number format you may actualy see it is set to a dollar format which will default to 2 decimals in Excel.

Re: Format issue

jimbarbour — Fri, 07 Oct 2016 21:51:33 GMT

I think the first line below should probably do the trick in terms of a TAGATTR. The second line below is an example from a PROC REPORT. Different format, but it should give you an idea of what it would look like in a PROC REPORT.

[TAGATTR="FORMAT:$#,##0"]

DEFINE	TIP_PIN_N 	/	DISPLAY		STYLE(COLUMN)	=	[BACKGROUND=WhiteSmoke	TAGATTR="FORMAT:##########"	FONT=("Calibri",8pt) ];