0

I am attempting to coalesce two data items in PROC SQL and am getting truncation. When I try the same process in a dataset I am also getting truncation, but in a different manner. Consider the following:

data test;
var_a = '111111';
var_b = 12345678;
run;

proc sql;
create table test2 as 
select coalesce(input(var_1, 6.), var2) as var3 format = best14.
from test;
quit;

This produces a numeric field with the the value 111111, which is not what I want. Interestingly if I do it all in one datastep and amend my code as follows:

data test;
format var3 best14.
var_a = '111111';
var_b = 12345678;
var3 = coalesce(input(var_1, 6.), var2);
run;

I get a numeric field output containing 12345678. What I actually want is an SQL step that will give me a numeric field that contains 11111112345678.

Can someone please advise on why this truncation is occurring when using the COALESCE function?

Thanks

gdogg371
  • 3,879
  • 14
  • 63
  • 107

1 Answers1

2

I think you're mis-understanding the coalesce() function. coalesce() returns the first non-missing value from your list of values, so '111111' is correct in this case.

What I think you want is one of the cat functions, cats(). 

proc sql;
  create table test2 as 
  select input(cats(var1,var2),14.) as var3 format = best14.
  from test;
quit;

COALESCE > http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002206368.htm

CATS > http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002256540.htm

Chris J
  • 7,549
  • 2
  • 25
  • 25
  • Indeed, even though the definition of coalesce is to join together! I always thought it was a strange choice of word for the function it performs – Longfish Mar 09 '16 at 13:05
  • it seems i have misunderstood...that function name is extremely misleading i agree! – gdogg371 Mar 09 '16 at 13:18