BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10
proc format library = fodat.cat_formats ;
picture frmt_num_spac_comma
0 - 9999999 = '000 000 000,00000' (decsep=',' dig3sep=' ') ;
run ;


data encours ;
input montant ;
datalines ; 
123456.12345 
;

data encours2 ;
set encours ;
montant2 = put(montant,frmt_num_spac_comma.);
run ;

Hello,

 

I have the amount 123456,12345  and I would like to get 123 456,123456. (space thousand separator)

so I appply a format (by creating an amount2) that I created. the display is correct but the format of the amount2 is character and I would like numeric. How can I fix this ?

thanks a lot in adance.


Nasser

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If you want to keep a number and display a formatted value then you apply the format to the number rather than converting to character, something like:

proc format library=fodat.cat_formats;
  picture frmt_num_spac_comma
    0-9999999='000 000 000,00000' (decsep=',' dig3sep=' ');
run ;


data encours;
  input montant;
  format montant frmt_num_spac_comma.;
datalines; 
123456.12345 
;
run;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If you want to keep a number and display a formatted value then you apply the format to the number rather than converting to character, something like:

proc format library=fodat.cat_formats;
  picture frmt_num_spac_comma
    0-9999999='000 000 000,00000' (decsep=',' dig3sep=' ');
run ;


data encours;
  input montant;
  format montant frmt_num_spac_comma.;
datalines; 
123456.12345 
;
run;
Nasser_DRMCP
Lapis Lazuli | Level 10

thanks a lot RW9.

may I ask an added question.

I try to export the amount into excel via ODS and proc report.

I specified this define montant / '' format = frmt_num_spac_comma. ;

but in excel I get a character column. what should I specify into this line above to get a numeric.

thanks a lot

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Think we covered this:

https://communities.sas.com/t5/ODS-and-Base-Reporting/TAGSETS-EXCELXP-proc-report-maintain-numerci-f...

Its an Excel issue, Excel reads the tagset created by SAS and processes it.  If you have the format in Excel and it is in Normal.dot - this is the base file Excel uses, then you should be able to apply it.

Nasser_DRMCP
Lapis Lazuli | Level 10

thanks but I created the format # ##0,00000 in excel and by doing 

style(column)={tagattr='format:# ##0,00000'}

I get 225 353,74 (only two deci)  

 

I don't succeed to get in excel a number format like  225 353,73804  

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

That format doesn't seem to work in Excel.  Also note you need to save it in your normal.dot file.  Anyways, this is outside the scope of a SAS forum.  Best check an Office forum for setting up and using custom formats across files.

 

Ksharp
Super User
Try NLNUM.  format.


  format montant nlnum32.5;



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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1248 views
  • 2 likes
  • 3 in conversation