Help using Base SAS procedures

proc format text instead of numeric

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

proc format text instead of numeric

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

 


Accepted Solutions
Solution
‎09-07-2017 07:43 AM
Super User
Super User
Posts: 9,599

Re: proc format text instead of numeric

Posted in reply to Nasser_alfea

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


All Replies
Solution
‎09-07-2017 07:43 AM
Super User
Super User
Posts: 9,599

Re: proc format text instead of numeric

Posted in reply to Nasser_alfea

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

Re: proc format text instead of numeric

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

 

Super User
Super User
Posts: 9,599

Re: proc format text instead of numeric

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 117

Re: proc format text instead of numeric

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  

 

 

 

Super User
Super User
Posts: 9,599

Re: proc format text instead of numeric

Posted in reply to Nasser_alfea

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.

 

Super User
Posts: 10,770

Re: proc format text instead of numeric

Posted in reply to Nasser_alfea
Try NLNUM.  format.


  format montant nlnum32.5;



☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 319 views
  • 2 likes
  • 3 in conversation