Joining ON character and numeric variable

[jffeudo86]

Hi,

I have 2 tables I need to join by a variable.  The thing is they have different types.  

	Variable	Type	Len	Format	Informat
Table A	xx	Char	8	$CHAR8.	$CHAR8.
Table B	xx	Num	8	19.	19.

I need the join statement.  I tried below and returned no rows. Is my join correct?  Please only focus on the JOIN clause and not any other part of the code.

proc sql;

select *

from tableA a

inner join tableB b on input(a.xx, 8.) = b.xx;

run;

[ballardw]

---

@jffeudo86 wrote:

Hi,

I have 2 tables I need to join by a variable.  The thing is they have different types.  

	Variable	Type	Len	Format	Informat
Table A	xx	Char	8	$CHAR8.	$CHAR8.
Table B	xx	Num	8	19.	19.

I need the join statement.  I tried below and returned no rows. Is my join correct?  Please only focus on the JOIN clause and not any other part of the code.

proc sql;

select *

from tableA a

inner join tableB b on input(a.xx, 8.) = b.xx;

run;

---

One would suspect that if you have a default format of 19. for a numeric variable that perhaps in needs more than 8 digits to represented which means that your other variable may be missing enough characters to make matches.

I suggest a small experiment just creating values with your input as shown, ignore the other set, and see what values you get. Do they look like the ones in b?

You really should show us some example values that you expect to match.

[Patrick · Accepted solution]

@jffeudo86 

The join as posted should be o.k. so it's either something in the data or your actual join is different from what you show us.

When inspecting the keys in your source data can you identify at least one case where you'd expect a match?

data tableA;
  attrib xx format=$char8. informat=$char8.;
  do xx='1','2','3';
    xx=' '||xx;
    len_a_xx=lengthn(xx);
    output;
  end;
  stop;
run;

data tableB;
  attrib xx format=19. informat=19.;
  do xx=1,3,4;
    output;
  end;
  stop;
run;

proc sql;
  select 
    a.xx as a_xx, 
    len_a_xx,
    b.xx as b_xx
  from 
    tableA a inner join tableB b 
      on input(a.xx, 8.) = b.xx
  ;
run;

[jffeudo86]

Thank you! New to SAS so I just wanted to know that my join is correct, or if not, be given direction on how to do it.