topic Re: Joining ON character and numeric variable in SAS Programming

Joining ON character and numeric variable

jffeudo86 — Thu, 16 May 2019 21:37:20 GMT

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;

Re: Joining ON character and numeric variable

ballardw — Thu, 16 May 2019 21:47:57 GMT

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

Re: Joining ON character and numeric variable

Patrick — Thu, 16 May 2019 23:12:31 GMT

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

Re: Joining ON character and numeric variable

jffeudo86 — Fri, 17 May 2019 12:15:36 GMT

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.