topic Re: How to change commas to periods for numbers with decimals in SAS Programming

How to change commas to periods for numbers with decimals

ty_chv — Thu, 05 Dec 2019 17:38:41 GMT

Hello all,

I have a data set with a temperature variable in Celsius. For some values the decimal point is a comma instead, so I'll need to convert them to a period to run other codes. I could do a simple "if Temp = 37,0 then Temp = 37.0" but it's a large database I'd like to automate this at the start.

Sample data set below:

Data Work.Have;
  Input PatientId $ AgeMo Temp $;
    Datalines;
	0001 11 37.9
	0002 9  38.7
	0003 44 38,1
	0004 30 37.7
	0005 19 37,5
	;
Run;

Thanks in advance

Re: How to change commas to periods for numbers with decimals

Tom — Thu, 05 Dec 2019 17:51:22 GMT

Do you have other text in that field? Do you ever have values large enough that they also have separators between thousands and hundreds?

If not then just replace the comma with period.

temp=translate(temp,'.',',');

If you also have values like 1.234,56 then you could test if the last one is a comma and only then reverse them.

if char(temp,length(temp)-2)=',' then temp=translate(temp,',.','.,');

Re: How to change commas to periods for numbers with decimals

ed_sas_member — Thu, 05 Dec 2019 17:56:38 GMT

Data Work.Have;
  Input PatientId $ AgeMo Temp $;
  Temp_n = input(translate(Temp,",","."), comma8.1);
  drop Temp;
  rename Temp_n = Temp;
    Datalines;
	0001 11 37.9
	0002 9  38.7
	0003 44 38,1
	0004 30 37.7
	0005 19 37,5
	;
Run;

Re: How to change commas to periods for numbers with decimals

ty_chv — Thu, 05 Dec 2019 18:40:46 GMT

Thanks for the reply, this fixed the comma issue but now there's a new one. Variables without any decimals are assigned a period (40 -> 4.0). I didn't include those without decimals before, below is an updated code

Data Work.Have;
  Input PatientId $ AgeMo Temp $;
  Temp_n = input(translate(Temp,",","."), comma8.1);
  Drop Temp;
  Rename Temp_n = Temp;
    Datalines;
	0001 11 37.9
	0002 9  38.7
	0003 44 38,1
	0004 30 37.7
	0005 19 37,5
	0006 20 40
	0007 10 39
	;
Run;

Which gives this output

Obs PatientId AgeMo Temp1234567

0001	11	37.9
0002	9	38.7
0003	44	38.1
0004	30	37.7
0005	19	37.5
0006	20	4.0
0007	10	3.9

Re: How to change commas to periods for numbers with decimals

Tom — Thu, 05 Dec 2019 18:44:55 GMT

DO NOT INCLUDE DECIMAL WIDTH IN AN INFORMAT!!!!
DO NOT INCLUDE DECIMAL WIDTH IN AN INFORMAT!!!!
DO NOT INCLUDE DECIMAL WIDTH IN AN INFORMAT!!!!
That will divide any values without decimal points (exact integers) by the specified power of 10.

Re: How to change commas to periods for numbers with decimals

ty_chv — Thu, 05 Dec 2019 18:46:07 GMT

Thanks for the reply. No values as in the second case so I went with the first solution. Now have a new issue for numbers with no decimals though

Re: How to change commas to periods for numbers with decimals

ty_chv — Thu, 05 Dec 2019 18:53:47 GMT

New issue was caused by adding the input statement below. Used exactly as the first one and it worked.

Thanks!

Re: How to change commas to periods for numbers with decimals

Tom — Thu, 05 Dec 2019 18:55:24 GMT

You do not want to include decimal width in the informat specification, unless you want SAS to divide text without the decimal point by that power of ten. Do not translate the periods to commas (unless perhaps your SAS session is one where that is what the COMMA informat wants).

data want;
  input PatientId $ AgeMo Temp $;
  Temp_n = input(translate(Temp,".",","), comma8.);
  drop Temp;
  rename Temp_n = Temp;
datalines;
0001 11 37.9
0002 9  38.7
0003 44 38,1
0004 30 37.7
0005 19 37,5
0006 20 40
0007 10 39
;

       Patient    Age
Obs      Id        Mo    Temp

 1      0001       11    37.9
 2      0002        9    38.7
 3      0003       44    38.1
 4      0004       30    37.7
 5      0005       19    37.5
 6      0006       20    40.0
 7      0007       10    39.0