BookmarkSubscribeRSS Feed
Habs4Life
Calcite | Level 5

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 

2 REPLIES 2
Reeza
Super User

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;

Ksharp
Super User

How about:

proc sql;

select * from table1 as a,table2 as b

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

quit;

Ksharp

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2155 views
  • 0 likes
  • 3 in conversation