Help using Base SAS procedures

PROC SQL - Joining tables w/ partially matching data.

Reply
N/A
Posts: 0

PROC SQL - Joining tables w/ partially matching data.

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
Super Contributor
Super Contributor
Posts: 365

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

Hello Shivek,

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

Sincerely,
SPR
Regular Contributor
Posts: 151

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

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]
N/A
Posts: 0

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

Thanks.
Will try it out.

Thanks for all the help!
Shivek Message was edited by: Shivek
Super User
Posts: 9,662

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

Another better choice is to use spedis( ) function.


Ksharp
Ask a Question
Discussion stats
  • 4 replies
  • 1095 views
  • 0 likes
  • 4 in conversation