left join variables type

[Mirou]

Hi,

I need to join two sas tables using left join,  the problem is that 

N1 type is 12$. and N2 is a numeric 15. 

i try to convert N2 to a character 15$. using a new variable N3 =put(N2,15$.)

but sas couldn't find a matches between the two tables, that was confirmed by the full join, 

could you help me with that,

thanks,

proc SQL;
create table C as select
A.*,B.*
from  A left join B
on A.N1 = b.N2
;quit;

[Kurt_Bremser]

You cannot fit 15 digits in a character variable with only 12 bytes. If you actually have numbers with 15 digits, these can never match.

Please supply examples for your character and numeric values.

[Mirou]

Actually N1 and N2 are codes to many  products, i need to join the two tables to recover  all the information related to the same product

example code N1: 111256897,111256875,111256999

N2:111256875,115252455

[Kurt_Bremser]

Then you need to convert one of the columns so it fits the other. See this example:

data n1;
input code :$12. a $;
datalines;
111256897 a
111256875 b
111256999 c
;
run;

data n2;
input code :12. b $;
datalines;
111256875 d
115252455 e
;
run;

proc sql;
create table want as
select coalesce(n1.code,left(put(n2.code,12.))) as code, n1.a, n2.b
from n1 full join n2
on (n1.code = left(put(n2.code,12.)));
quit;

proc print data=want noobs;
run;

Result:

  code       a    b

111256875    b    d
111256897    a     
111256999    c     
115252455         e

You can see that the one match is found.

[Tom]

---

@Mirou wrote:

Actually N1 and N2 are codes to many  products, i need to join the two tables to recover  all the information related to the same product

example code N1: 111256897,111256875,111256999

N2:111256875,115252455

---

If they are codes then they should be character strings. You will not be taking the mean of a code.

You can use the PUT() function to convert numbers into character strings.  Make sure not to introduce leading spaces.

charcode = put(numcode,12.-L);

But you also need to make sure that your codes do not supposed to include leading zeros.  In that case you could use the Z format.

charcode = put(numcode,z12.);

Note that you might need to go back to the source data for the dataset that has created the codes as numbers and re-read the source files as characters to fix some of the issues that could have been caused by reading the codes as numbers.

[ballardw · Accepted solution]

---

@Mirou wrote:

Hi,

I need to join two sas tables using left join,  the problem is that 

N1 type is 12$. and N2 is a numeric 15. 

i try to convert N2 to a character 15$. using a new variable N3 =put(N2,15$.)

but sas couldn't find a matches between the two tables, that was confirmed by the full join, 

could you help me with that,

thanks,

proc SQL;
create table C as select
A.*,B.*
from  A left join B
on A.N1 = b.N2
;quit;

---

A couple of issues: Create character value from numeric using a numeric format with the Put statement: N3 =put(N2,15.)

Second is any value that is less than 15 digits will have leading blanks. So if you try to match '12345' with '          12345' you do not get a match.

There  are two ways to fix that. One is to use the -L option in the PUT function to left align the value so '12345       ' is created or to use

the Strip or Left functions to align the value.

data junk;
   x = 12345;
   y = put(x,f15.);
   z = put(x,f15. -L);
   u = strip(put(x,f15.));
run;

Use one the forms for Z or U above.