topic Re: Format, thousand seperator proc report in ODS and Base Reporting

Format, thousand seperator proc report

metallon — Mon, 16 Nov 2015 15:55:38 GMT

Hello SAS Experts,

I have been looking through many tutorials and whitepages but how to get a number without decimal points to show a thousand seperator I have not figured out so far.

Example 2346 should look like 2.456 (dot seperated)

31543 should look like 31.543

I tried this:

DEFINE EB_P / 'B' format=7.2;
DEFINE SB_N / 'N' format=nlnum20.;

but it did not help.

Can someone point me in the right direction?

Thanks

Re: Format, thousand seperator proc report

PaigeMiller — Mon, 16 Nov 2015 16:22:47 GMT

Use the COMMAX format.

http://support.sas.com/documentation/cdl/en/ds2ref/68052/HTML/default/viewer.htm#n0mxv8qaydylmxn16vpt9nlgw1dp.htm

Re: Format, thousand seperator proc report

metallon — Tue, 17 Nov 2015 13:07:42 GMT

Hi PaigeMiller,

Thanks for your hint!

That was a great help.

I have to admit, there are some column which I have where there is no decimal number(s).

The numbers are just whole numbers like 2343 or 56992. Which format do I appl

to "just" create a thousand seperator? I tried commax10. but that moved the decimal seperator and did not eliminate it.

Re: Format, thousand seperator proc report

Tom — Tue, 17 Nov 2015 14:40:30 GMT

The NLNUM format honors your national language setting. The COMMAX should just do the reverse of the COMMA format. You can test.

data _null_;
  length value 8 format $32 display $20 ;
  do value=2346, 31543 ;
    do format='COMMA20.','NLNUM20.','COMMAX20.';
      display = putn(value,format);
      put value format display ;
    end;
  end;
run;

2346 COMMA20. 2,346
2346 NLNUM20. 2,346
2346 COMMAX20. 2.346
31543 COMMA20. 31,543
31543 NLNUM20. 31,543
31543 COMMAX20. 31.543

How are you viewing the results? If you wrote it to an EXCEL file then perhaps Excel has changed the format, or attached it's own default format to the numeric values?

Re: Format, thousand seperator proc report

metallon — Tue, 17 Nov 2015 15:37:07 GMT

Hi,
Indeed, you are right. It is the MS Excel export that messes things up.
In SAS EG (Results tab) I can see the correct numbers 2.560 but in MS Excel it becomes 2,56 😕

Re: Format, thousand seperator proc report

metallon — Thu, 26 Nov 2015 17:49:57 GMT

Is there a way to influence the format in MS Excel?

Re: Format, thousand seperator proc report

Cynthia_sas — Thu, 26 Nov 2015 17:56:27 GMT

Hi:
You do not show all your code. The answer depends on HOW you are getting your output into Excel. With PROC EXPORT or the LIBNAME engine, you do not have much control over the format in Excel. However, with ODS -- such as ODS MSOFFICE2K or ODS TAGSETS.EXCELXP, then you can influence the format that SAS sends to Excel by actually sending an Excel format from SAS to Excel via a style attribute override. See this paper:
http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia

Re: Format, thousand seperator proc report

metallon — Fri, 27 Nov 2015 09:19:58 GMT

Hi,

Thanks for the reply. I read through the paper.
The whole code is a massive chunk so I dont bother you with that 🙂
I am using ODS Tagset /XML and applied

DEFINE KAAN / 'N' style(column)={tagattr='format:#.##0'};

I picked the format using MS excel and it formatted a number like
2000 as 2.000, 500 as 500 and 23444 as 23.444.
All great but when I generate the xml/excel with SAS the number turns out
to be 2000,0 and 500,0 😕

Re: Format, thousand seperator proc report

metallon — Fri, 27 Nov 2015 11:20:01 GMT

got it DEFINE KAA_N / 'N' style(column)={tagattr='format:#,##0'};