BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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

8 REPLIES 8
metallon
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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?

 

 

 

metallon
Pyrite | Level 9
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 😕
metallon
Pyrite | Level 9
Is there a way to influence the format in MS Excel?
Cynthia_sas
SAS Super FREQ
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
metallon
Pyrite | Level 9


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 😕
metallon
Pyrite | Level 9
got it DEFINE KAA_N / 'N' style(column)={tagattr='format:#,##0'};

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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