PROC SQL - Joining tables w/ partially matching data.

[deleted_user]

Hello,
Need some help with PROC SQL - Joining tables w/ partially matching data.
Basically I have two tables which I wanted to join/merge into a new table.
However the data are of partial match i.e. AMATA and AMAT or PTTEP and PTTE
While some matches 100% like STEC and STEC


Table_1
AMATA
STEC
PTTEP

Table_2
AMAT
STEC
PTTE
IVL

I want to join Table_1 & Table_2 into Table_3.
However, the data only partially matches.

Is it possible to Join using "LIKE"
or use wildcards like % when specifying the join criteria?

Thanks,
Shivek

[SPR]

Hello Shivek,

Try to use =* (sounds like) or eqt (equal to truncated strings). Both can be used with character operands only.

Sincerely,
SPR

[Oleg_L]

Hello.
The code below is one of possible ways.

[pre]
DATA Table_1;
infile cards;
input t1 $10.;
cards;
AMATA
STEC
PTTEP
;

data Table_2;
infile cards;
input t2 $10. ;
match=1;
cards;
AMAT
STEC
PTTE
IVL
;

proc sql noprint;
create table t3 as select table_1.t1, table_2.t2, table_2.match
from table_1 left join table_2 on upcase(table_1.t1) contains upcase(trim(table_2.t2));
quit;


[/pre]

[deleted_user]

Thanks.
Will try it out.

Thanks for all the help!
Shivek Message was edited by: Shivek

[Ksharp]

Another better choice is to use spedis( ) function.


Ksharp