proc format text instead of numeric

[Nasser_DRMCP]

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

[RW9 · Accepted solution]

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]

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]

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]

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]

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]

Try NLNUM.  format.


  format montant nlnum32.5;