topic Re: ODS Number output in ODS and Base Reporting

ODS Number output

Q1983 — Mon, 10 Apr 2017 21:29:55 GMT

*sum='Total MSRP Dollars$' *f=comma16.2 /*f=dollar16.2*/

This is a snippet of code in my ODS code. I want to display $25,000.00 as 25,000

I tried variations of comma16.0, comma6 and number6 however what would work in this instance?

Re: ODS Number output

ballardw — Mon, 10 Apr 2017 22:07:04 GMT

What does it display when you use comma16.0 ??? Make sure when using Comma6. that you have the period at the end of the format name.

Are you getting any messages in the log?

Re: ODS Number output

Reeza — Mon, 10 Apr 2017 22:35:27 GMT

A comma format should work fine, but this is PROC TABULATE code, not an ODS issue.

How does ODS come into play? Or is not showing properly in a specific ODS destination?

Re: ODS Number output

Cynthia_sas — Tue, 11 Apr 2017 00:44:09 GMT

Also, with TABULATE and a format in the TABLE statement, you need to be aware of both format precedence and possibly style precedence. It would be more useful for you to show ALL your code and ALL your ODS statements because, for example, you could have your format on the wrong item or you could have your format in the wrong dimension and need the FORMAT_PRECEDENCE option.

cynthia

Re: ODS Number output

Q1983 — Tue, 11 Apr 2017 14:08:14 GMT

Lets try this again with some data

data cars(keep= make type weight cnt);

set sashelp.cars;

if make in ('Acura','BMW');

cnt=1;

run;

/*Acura SUV 4451 1

Acura Sedan 2778 1

Acura Sedan 3230 1

Acura Sedan 3575 1

Acura Sedan 3880 1

Acura Sedan 3893 1

Acura Sports 3153 1

BMW SUV 4023 1

BMW SUV 4824 1

BMW Sedan 3219 1

BMW Sedan 3197 1

BMW Sedan 3560 1

BMW Sedan 3461 1*/

OPTIONS orientation=landscape papersize="letter" missing=' ';

options nocenter ls=145 ps=60 orientation=landscape nomprint nosymbolgen

FMTSEARCH=(datamart) nosource2 compress=yes reuse=yes;

%let ReportName = Proc_tab_Example_;

%let ReportOut = &ReportName&pbd_date_key.;

%LET VERSION = V1;

%Let FNote1=Confidential and Proprietary Information.;

%put REPORTNAME=&ReportName;

%put REPORTOUT=&ReportOut;

ods listing close;

options missing=' ' topmargin=0.30in bottommargin=0.01in rightmargin=0.25in leftmargin=0.25in;

ODS TAGSETS.ExcelXP file="&ReportOut..xml" Path="&OutDir" style=&dors_style;

%CoverSheet(&ReportName,&pbd_date_key,&Version);

ODS TAGSETS.ExcelXP

options(sheet_interval='none'

default_column_width='9'

sheet_name="exampl"

center_horizontal="no"

frozen_headers = "Yes"

Orientation='Landscape'

embedded_titles='Yes'

fittopage="No"

blackandwhite="No"

Embedded_Footnotes='Yes'

absolute_column_width='9'

autofit_height="Yes");

%NoAccountLogic(cars,&ReportName);

Proc tabulate data=cars order= data format=10. S=[cellwidth=80];

Class make type;

Var weight cnt;

Table make=' ' all={label='Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]],

type*(cnt=''*sum='Count of Units' cnt=''*colpctn='% of Units'*f=number8.2

weight=''*sum='weight' *f=comma9.0 weight='' *colpctSum='% of weight'*f=number8.2 )

all={ label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]]

;

label colpctn = '% of Units';

keylabel n='';

run;

ods listing close;

options missing=' ' mlogic mprint;

TITLE; FOOTNOTE;

%InsertCode(proc_tab_example.sas);

ODS TAGSETS.ExcelXP close;

ods listing;

quit;

The percentage columns work perfectly as I used f=number8.2 however the weight column does not format as desired. I want something like 17,356 and 46,660

% of Units	weight	% of weight
27.78	17356	27.11
72.22	46660	72.89
100	64016	100

Re: ODS Number output

Cynthia_sas — Tue, 11 Apr 2017 14:20:52 GMT

Hi: Nobody can run your code because you do not provide the macro programs that are sprinkled throughout the program.
cynthia

Re: ODS Number output

Cynthia_sas — Tue, 11 Apr 2017 14:43:51 GMT

Did you see this error in the LOG?

ERROR: The format NUMBER was not found or could not be loaded.

So if you look at the HTML version of your output, you will see that SAS sent the comma format because you can see it used in the HTML output. However Excel ignores the comma (and the decimal places) as shown here:

You will need to investigate the use of the TAGATTR style attribute (which has had a LOT of previous postings here in the forum) to get an example of how to modify your TABLE statement so that you can send Excel a format that it can respect.

Here's a paper that illustrates the use of TAGATTR: https://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia