BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Obsidian | Level 7
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
Not applicable
Thanks.
Will try it out.

Thanks for all the help!
Shivek Message was edited by: Shivek
Ksharp
Super User
Another better choice is to use spedis( ) function.


Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3189 views
  • 0 likes
  • 4 in conversation