Help using Base SAS procedures

"CONTAINS" Join Question

Reply
New Contributor
Posts: 2

"CONTAINS" Join Question

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 

Super User
Posts: 19,850

Re: "CONTAINS" Join Question

Posted in reply to Habs4Life

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;

Super User
Posts: 10,041

Re: "CONTAINS" Join Question

Posted in reply to Habs4Life

How about:

proc sql;

select * from table1 as a,table2 as b

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

quit;

Ksharp

Ask a Question
Discussion stats
  • 2 replies
  • 711 views
  • 0 likes
  • 3 in conversation