topic Re: Join two tables with like in SAS Programming

Join two tables with like

olivier_guitton — Fri, 05 Dec 2014 07:26:25 GMT

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

WHERE

(

.Last =* b.Last_B OR

.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 !

Re: Join two tables with like

Loko — Fri, 05 Dec 2014 08:28:27 GMT

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;

Re: Join two tables with like

olivier_guitton — Fri, 05 Dec 2014 08:37:56 GMT

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