topic Re: proc format text instead of numeric in SAS Procedures

proc format text instead of numeric

Nasser_DRMCP — Thu, 07 Sep 2017 10:10:25 GMT

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

Re: proc format text instead of numeric

RW9 — Thu, 07 Sep 2017 10:36:14 GMT

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;

Re: proc format text instead of numeric

Nasser_DRMCP — Thu, 07 Sep 2017 11:47:47 GMT

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

Re: proc format text instead of numeric

RW9 — Thu, 07 Sep 2017 11:57:16 GMT

Think we covered this:

https://communities.sas.com/t5/ODS-and-Base-Reporting/TAGSETS-EXCELXP-proc-report-maintain-numerci-format/m-p/393614#M19259

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.

Re: proc format text instead of numeric

Nasser_DRMCP — Thu, 07 Sep 2017 12:05:34 GMT

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

Re: proc format text instead of numeric

RW9 — Thu, 07 Sep 2017 12:21:17 GMT

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.

Re: proc format text instead of numeric

Ksharp — Thu, 07 Sep 2017 13:57:58 GMT

Try NLNUM.  format.


  format montant nlnum32.5;