topic Re: left join variables type in SAS Programming

left join variables type

Mirou — Tue, 14 May 2019 10:23:44 GMT

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;

Re: left join variables type

Kurt_Bremser — Tue, 14 May 2019 10:43:02 GMT

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.

Re: left join variables type

Mirou — Tue, 14 May 2019 10:53:08 GMT

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

Re: left join variables type

Kurt_Bremser — Tue, 14 May 2019 11:40:27 GMT

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.

Re: left join variables type

Tom — Tue, 14 May 2019 12:59:00 GMT

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

Re: left join variables type

ballardw — Tue, 14 May 2019 15:50:04 GMT

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