topic Re: "CONTAINS" Join Question in SAS Procedures

"CONTAINS" Join Question

Habs4Life — Thu, 29 Mar 2012 20:34:07 GMT

Simple query...

Table1 has a million SKUs (ie 006631251252)

Table 2 has a unique list of SKUs (663125125)

As in the example above, the SKU in table 2 can be contained in the SKU from table 1. If I do an inner join on the two tables above, there would be no match.

I would like to create a join such that there is a match if Table 2 SKUs are contained in Table 1. I think the CONTAINS function is what needs to be used, but not sure how to do a join using CONTAINS.

Thanks in advance,

James

Re: "CONTAINS" Join Question

Reeza — Thu, 29 Mar 2012 20:40:29 GMT

Your inner join should have matches. If it doesn't because the SKU's are formatted different ie leading zeroes that's a different issue.

You can also try an exists or in

proc sql;

select * from table1

where sku in (select sku from table2);

quit;

or format within the query (can also within the join)

proc sql;

select * from table1

where sku in (select put(sku, z10.) from table2);

quit;

Re: "CONTAINS" Join Question

Ksharp — Fri, 30 Mar 2012 03:18:19 GMT

How about:

proc sql;

select * from table1 as a,table2 as b

where a.sku contains strip(b.sku) ;

quit;

Ksharp