topic Re: format combining BEST and COMMA? in SAS Procedures

format combining BEST and COMMA?

Alain38 — Wed, 10 Jul 2019 14:43:39 GMT

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,

Re: format combining BEST and COMMA?

Tom — Wed, 10 Jul 2019 14:57:59 GMT

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.

Re: format combining BEST and COMMA?

Reeza — Wed, 10 Jul 2019 15:08:29 GMT

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-space/td-p/98352

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#n056hzkipsxpcbn11ww9i9i47l0h.htm

Re: format combining BEST and COMMA?

Alain38 — Wed, 10 Jul 2019 15:29:33 GMT

@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

Re: format combining BEST and COMMA?

Reeza — Wed, 10 Jul 2019 15:33:45 GMT

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.

Re: format combining BEST and COMMA?

Alain38 — Wed, 10 Jul 2019 15:39:35 GMT

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 🙂

Re: format combining BEST and COMMA?

Tom — Wed, 10 Jul 2019 18:06:45 GMT

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

Re: format combining BEST and COMMA?

Alain38 — Wed, 10 Jul 2019 18:14:26 GMT

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...)

Re: format combining BEST and COMMA?

PeterClemmensen — Thu, 11 Jul 2019 06:23:21 GMT

@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;

Re: format combining BEST and COMMA?

Alain38 — Thu, 11 Jul 2019 07:52:37 GMT

@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;

Re: format combining BEST and COMMA?

PeterClemmensen — Thu, 11 Jul 2019 08:07:56 GMT

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