Convert numeric to character

[ChrisWoo]

Hi guys, would like to ask why i cant convert numeric to character with decimal places of 2 ?

i use the wrong format ? 

Numeric value = 0.250000

Value (character) i want = 0.25

extract from my code:

put(from_numeric_value,20.6)

%let sdis6_decimal_0 = "loanpurpose","original_tenure","principal_repay_term","specific_dfi_financing_details","startup_financing","refinance";
%let sdis6_decimal_2 = "interest_rate","rebate_rate","value_of_asset_purchase","dsr","refinance_value","eir","pod_original";

 data work.AuditLog_Mart_SDIS6 (/*drop=from_numeric_value to_numeric_value*/
           rename=(update_field=Variable)
           compress=yes
          );
  set prccris.ccris_audit_log;
  where sdis_filename = "SDIS6" and position_dt="&position_dt"d;



  /* Note: Transform numeric to string and remove blanks space*/
  if update_field in (&sdis6_decimal_0) then do;
   from_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.0)));
   to_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.0)));
   deci_tag = "0 deci";
  end;

  else if update_field in (&sdis6_decimal_2) then do;
   from_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.6)));
   to_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.6)));
   deci_tag = "2 deci";
  end;

  from_character_value = strip(from_character_value);
  to_character_value = strip(to_character_value);
 run;

[Patrick]

I assume the root cause of the missing values is that your conditions are never true.

if update_field in (&sdis6_decimal_0) then

 You could easily test this by adding another else condition like:

else to_numeric_value_c ='9999';

Given the info you've shared you want your character variables populated wth full precision but without trailing zeros. Format BESTn. does this for you.

I assume you've used ifc() for missings to become blanks. You could create a custom format instead. See below code for further explanation.

proc format;
  value myBest(default=16)
    . = ' '
    other=[best16.]
    ;
run;

data test;
  input from_numeric_value;
  /* your syntax with format best32. */
  to_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,best16.)));
  /* alternative syntax returning the same result */
  to_numeric_value_c2=put(from_numeric_value,myBest.);
  datalines;
0.25
0
.
0.0025
;
run;

proc print data=test;
run;

proc contents data=test;
run;

Also note that if using ifc() you should use a length statement to avoid a wasteful length of the created character variable.

....and just reading what @Kurt_Bremser wrote: Yes, of course, as this is likely for reporting just use the BESTn. or the custom format directly on the numerical variable. And there is also the missing option where you can change the default behaviour of printing missings as a dot.

options missing=' ';

[ChrisWoo]

hi @Patrick, the condition is validated true. At the beginning, i thought of this issue also (condition = false), that's why i create a column with variable name call [deci_tag].

btw, i found the reason why my code never works, that's because i used the wrong variable:

to_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.0)));

supposedly is the [to_numeric_value] instead of [from_numeric_value].

and also thanks for letting me know the system options missing and the length statement for ifc(), i never thought the lenght would be 200. I will go check about it with proc content.

[Kurt_Bremser]

What is the purpose of storing a number as character? If you want specific formats depending on some value for reporting, you can do that in PROC REPORT. For anything other than reporting, keeping the number as such is the way to go.

To get only two decimals, you must say so in the format; use 20.2 instead of 20.6.

[ChrisWoo]

hi, 

My entire program involved 3 data source. one (1) is import from text file and two (2) from DBMS.

At one point, I transpose my dataset after proc compare and all the numeric field changed to character. Then in the end, due to requirement, i need to merge the transposed table with the auditlog dataset to ensure the "before" and "after" value are match. So that's why I need to change the numeric to character in auditlog dataset.

/* 1. Proc compare */

	ods select comparesummary;
	ods output comparesummary = comparesummary;
	Proc compare
		base=work.extract_append_source_SDIS6
		compare=work.extract_mart_SDIS6
		out=work.field_compare_SDIS6 (compress=yes) outbase outcomp outdiff outnoequal ;
		id app_sys_code fi_code master_account_no sub_account_no;
		attrib _all_ label="";
	run;
	ods select all;

/* 2. proc transpose */

	proc transpose 
		data= work.field_compare_SDIS6
		out= work.field_compare_SDIS6_transpose (rename = _name_ = Variable
											     rename = Col1 = Base
											     rename = Col2 = Compare
											     rename = Col3 = Diff
												 compress=yes
												 );
		by app_sys_code fi_code master_account_no sub_account_no ;
		var facility_type -- LUCountrycode;
	run;

/* 3. after series of data cleaning */

	data merge_compare_audit_SDIS6 (compress=yes);
		merge work.Final_Field_Compare_SDIS6 (in=A) 
			  work.AuditLog_Mart_SDIS6_clean (in=B
									 	      keep= app_sys_code fi_code master_account_no sub_account_no variable
												    from_numeric_value_c to_numeric_value_c 
										   		    from_character_value to_character_value update_source
									 	      );
		by app_sys_code fi_code master_account_no sub_account_no variable;

		/* Output all records (full join)*/
		if A or B;

		CCRIS_N_Source = A;
		AuditLog = B;

		/* Matching tag */
		length tag $10.;

		if variable in(&SDIS6_Char_Var) and base = from_character_value and compare = to_character_value then tag = "Match";
		else if variable in(&SDIS6_Num_Var) and base = from_numeric_value_c and compare = to_numeric_value_c then tag = "Match";
		else tag = "To Check";
	run;

Now to think about it again, maybe i can just merge 3 tables together instead of doing the proc compare then transpose.. but I almost complete the program and i get what i want too. 

[Amir · Accepted solution]

Hi @ChrisWoo,

Should the following line of code (seems to appear twice):

to_numeric_value_c = strip(ifc(missing(from_numeric_value),"",put(from_numeric_value,20.6)));

be (test / convert "to_" variable):

to_numeric_value_c = strip(ifc(missing(to_numeric_value),"",put(to_numeric_value,20.6)));

If not, then please clarify which variable in the data set you expect to have the non-zero character value.

Thanks & kind regards,

Amir.

[ChrisWoo]

Thanks bro, you are right, i suppose to use [to_numeric_value] instead of [from_numeric_value].

I was struggling for an hour thinking is it i used the wrong format. I've tried many different format like w.d , bestw. , bestxw.d and many....

btw i also changed the format to 20.2 because i want it to be 2 decimal.

20.6 is just for trial and error.

[ballardw]

SAS variables always have a format for display. The only way you get something such as you reference here:

Hi guys, would like to ask why i cant convert numeric to character with decimal places of 2 ?

i use the wrong format ?

Numeric value = 0.250000

Is if someone assigns a format to force display of the four zeroes in the decimal positions.

An example you can run that will write the results to the Results window:

data _null_;
   file print;
   x=.25;
   put 'default SAS assigned format: ' x=;
   put 'assigned format 20.6: ' x= 20.6;
   put 'assigned format 8.1: ' x= 8.1;
   put 'assigned format 8.2: ' x= 8.2;
   put 'assigned format 8.3: ' x= 8.3;
   put 'assigned format 8.4: ' x= 8.4;
   put 'assigned format exponential: ' x= e8.3;
   put 'assigned format Time11.2: ' x= time11.2;
   put 'assigned format date9: ' x= date9.;
   put 'assigned format currency: ' x= dollar12.2;
run;

So if you only want to see a given number of decimals, use that in a Format. If you don't then don't include them.

Note that the above shows using the same numeric value as a time, date and currency.