Join two tables with like

[olivier_guitton]

Hello,

I have a problem, i will join two tables with a function like/*. I explain my problem, i have a table, it's my referencial and the other my table with text.

Table Referencial :

two columns : duree / correspondance

12 years / 144

4 years / 48

2 years / 24

8 years / 96

Table Duration :

two column

id / text

25158 / cat 12 years tom

5896 / spot and 12 years

4565 / 8 years anna

New column (my wish) :

id / text / correspondance

25158 / cat 12 years tom / 144

5896 / spot and 12 years / 144

4565 / 8 years anna / 96

I tried this (http://support.sas.com/resources/papers/proceedings12/122-2012.pdf) but it doesn't really work.

PROC SQL ;

CREATE TABLE

Possible_Matches

AS SELECT

*

FROM Name AS n, Birthday

AS

b

WHERE

(

n

.Last =* b.Last_B OR

n

.First =* b.First_B);

QUIT

;

With this code, if, my column text contains a 2, i will have in correspondance all number with a two, i will have : 2 lines, one with 144 (12 years) and 24 (2 years).

(My wish) SAS must find an equal match. If in my text we find 12 years, so we must have 144 not 24, and if we have 1 in the text, 0 match, if we have 122, 0 match, if we have 2 years = 24.

Thank you !

[Loko · Accepted solution]

Hello,

Assuming I get it right , here is a solution:

data ref;
input duree $ & coresp;
datalines;
12 years  144
4 years  48
2 years  24
8 years  96
;
run;

data Duration;
infile datalines truncover;
input id text $25.;
datalines;
25158 cat 12 years tom
5896 spot and 12 years
4565 8 years anna
;
run;

data duration;
length keyvar $ 20 numval $ 5 ;
set duration;
stringtily=substr(text,1,find(text, "years")-1);/*determine from text string until years*/
numval=scan(stringtily,countw(stringtily));/*get number from string determined above - my assumption is that immediately before word years are the numbers*/
keyvar=strip(numval) || " years";/*determine key var*/
run;

proc sql;
select duration.id, duration.text, ref.coresp
from ref inner join duration on duration.keyvar=ref.duree;
quit;

[olivier_guitton]

Thanks ! Perfect ! It's work !!!!!