The SAS Output Delivery System and reporting techniques

Format, thousand seperator proc report

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Format, thousand seperator proc report

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


Accepted Solutions
Solution
‎11-27-2015 06:19 AM
SAS Super FREQ
Posts: 8,720

Re: Format, thousand seperator proc report

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,500

Re: Format, thousand seperator proc report

Frequent Contributor
Posts: 133

Re: Format, thousand seperator proc report

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.

Super User
Super User
Posts: 6,364

Re: Format, thousand seperator proc report

[ Edited ]

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?

 

 

 

Frequent Contributor
Posts: 133

Re: Format, thousand seperator proc report

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 :/
Frequent Contributor
Posts: 133

Re: Format, thousand seperator proc report

Is there a way to influence the format in MS Excel?
Solution
‎11-27-2015 06:19 AM
SAS Super FREQ
Posts: 8,720

Re: Format, thousand seperator proc report

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
Frequent Contributor
Posts: 133

Re: Format, thousand seperator proc report



Hi,

Thanks for the reply. I read through the paper.
The whole code is a massive chunk so I dont bother you with that Smiley Happy
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 :/
Frequent Contributor
Posts: 133

Re: Format, thousand seperator proc report

got it DEFINE KAA_N / 'N' style(column)={tagattr='format:#,##0'};
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1132 views
  • 3 likes
  • 4 in conversation