topic Re: PROC SQL - Joining tables w/ partially matching data. in SAS Procedures

PROC SQL - Joining tables w/ partially matching data.

deleted_user — Wed, 23 Mar 2011 14:58:20 GMT

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

Re: PROC SQL - Joining tables w/ partially matching data.

SPR — Wed, 23 Mar 2011 15:05:21 GMT

Hello Shivek,

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

Sincerely,
SPR

Re: PROC SQL - Joining tables w/ partially matching data.

Oleg_L — Wed, 23 Mar 2011 15:09:43 GMT

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]

Re: PROC SQL - Joining tables w/ partially matching data.

deleted_user — Wed, 23 Mar 2011 15:22:19 GMT

Thanks.
Will try it out.

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

Re: PROC SQL - Joining tables w/ partially matching data.

Ksharp — Thu, 24 Mar 2011 03:38:12 GMT

Another better choice is to use spedis( ) function.

Ksharp