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
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;
How about:
proc sql;
select * from table1 as a,table2 as b
where a.sku contains strip(b.sku) ;
quit;
Ksharp
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.