topic how to merge datasets by using contains statement in SAS Programming

how to merge datasets by using contains statement

knveraraju91 — Tue, 28 May 2019 14:16:05 GMT

data one;
input decode $10.;
datalines;
aaaa 
bbbb
cccc
eeee
;
data cm;
input decode $1-14;
datalines;
aaaa a1b1 ew22
vfdf bbbb b1w2
eewe cccc c2c2
dddd de33 eewe
;

Dear,

I have to create a dataset by merging two data sets by decode variable. The output should contain records from cm data set if the values from Cm contains the values in one dataset.

From the above input, output should not contain "dddd de33 eewe" as this value 'dddd' not in data one.

Please suggest. Thank you

output;

decode
aaaa a1b1 ew22

vfdf bbbb b1w2

eewe cccc c2c2

Re: how to merge datasets by using contains statement

Oligolas — Tue, 28 May 2019 14:25:05 GMT

PROC SQL;
   CREATE TABLE want AS
      SELECT a.*
      FROM CM a
      INNER JOIN one b
      ON index(strip(a.decode),strip(b.decode))
   ;
QUIT;

Re: how to merge datasets by using contains statement

PGStats — Tue, 28 May 2019 15:58:15 GMT

You could use a contains clause

proc sql;
select *
from cm
where exists (select decode from one where cm.decode contains strip(decode));
quit;

But if you are looking for words, it might be better to use FINDW which does specifically that:

proc sql;
select *
from cm
where exists (select * from one where findw(cm.decode, strip(decode)) > 0);
quit;

Re: how to merge datasets by using contains statement

Ksharp — Wed, 29 May 2019 13:38:43 GMT

data one;
input decode $10.;
datalines;
aaaa 
bbbb
cccc
eeee
;
data cm;
input decode $1-14;
datalines;
aaaa a1b1 ew22
vfdf bbbb b1w2
eewe cccc c2c2
dddd de33 eewe
;

proc sql;
select cm.decode
 from one,cm
  where cm.decode contains strip(one.decode);
quit;