format combining BEST and COMMA?

[Alain38]

Hello everyone,

Not a very important question but I was wondering if there were a format or workaround to display values without decimals when they are integers (like BESTw.), and with a period that separates every three digits and a comma that separates the decimal fraction (like COMMAw.d)

Best,

[Tom]

Not that I know of.  You might be able to create one using PROC FCMP so that you can test if the value is an integer and use a different format in those cases.

[Reeza]

Sounds like the french format? I'm actually pretty sure SAS has a format for that.

https://communities.sas.com/t5/SAS-Procedures/Format-numeric-variable-to-seperate-thousands-with-a-s...

https://communities.sas.com/t5/SAS-Programming/Numeric-format-with-decimal-comma/td-p/334697

And commaXw.d format is what you want:
https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#n056hzkipsx...

[Alain38]

@Reeza  A period for three digits and a comma for the decimals is indeed the French format obtained with COMMAXw.d, my mistake in the description sorry. What I was looking for is a nice looking format (such as COMMA or COMMAX) with no decimals displayed when integers (like BEST)

@TomThank you for confirming that there is no format doing this ready for use. I will look how to build it with PROC FCMP. Thank you for the example of this in your link @Reeza

[Reeza]

You can try using a nested format along with PROC FCMP then. Do you want the variable as a character or numeric? If character this is quite easy within a single line, otherwise it's slightly more work.

[Alain38]

Need it numeric... but this is just to improve the display so not vital. I will look into that but if this is really too complicated, I will not do it. Thank you for your answers 🙂

[Tom]

If the range of values is not too large you could just list all of the integers in the range.

data formats ;
  retain fmtname 'mycomma' type 'n' hlo 'f  ' label 'commax8. ';
  do start=-999 to 9999 ;
    output;
  end;
  start=.;
  hlo='fo';
  label='commax8.2';
  output;
  stop;
run;
proc format cntlin=formats;
run;

data test;
  do x=1 to 3,12.5,1234,-300,3456.1;
   output;
  end;
run;
proc print data=test;
 format x mycomma.;
run;

Obs       x

 1            1
 2            2
 3            3
 4        12,50
 5        1.234
 6         -300
 7     3.456,10

[Alain38]

Thank you very much @Tom for your help. Unfortunately the range of values is absolutely huge (that's the market value of each U.S. firm, so there are small companies and some huge firms like Facebook...)

[PeterClemmensen · Accepted solution]

@Alain38, I think this gives you what you want

proc fcmp outlib=work.functions.fun; 
  function fnc(num) $;
    if num=int(num) then r=scan(strip(put(num, commax32.10)), 1, ',');
    else                 r=tranwrd(strip(tranwrd(strip(put(num, commax32.10)), "0", " "))," ","0");
    return (r);
  endsub;
run;

options cmplib=work.functions;    
proc format;
  value fmt (default=32) other=[fnc()];
run;

data test;
   x=1234.567;output; 
   x=-1234.00;output; 
   x=1234567 ;output;
   x=1.23    ;output;   

   format x fmt.;
run;

[Alain38]

@PeterClemmensen  Thank you very much it's working perfectly! Just to be sure to understand the code, I adapted it to the English format if someone needs (personnaly I need both):

proc fcmp outlib=work.functions.fun; 
	function fnc(num) $;
		if num=int(num) then r=scan(strip(put(num, comma32.10)), 1, '.');
		else r=tranwrd(strip(tranwrd(strip(put(num, comma32.10)), "0", " "))," ","0");
		return (r);
	endsub;
run;

[PeterClemmensen]

Glad to help 🙂 Can't test it right not, but it looks right to me.