- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have table t1 with 3 columns: Location / Number / Attribute. For each combination of (Location & Number), it has several attributes. Table t1 is shown as below:
Table t2 is shown as below:
We would like to obtain a table t3 if t2.Attribute is same or part of t1.Attribute.
I try the following code:
Proc Sql;
Create Table t3 as select distinct
A.*
from t1 A, t2 B
where A.Attribute contains B.Attribute
order by A.Location, A.Number;
Quit;
or
Proc Sql;
Create Table t3 as select distinct
A.*
from t1 A, t2 B
where find(A.Attribute, B.Attribute) >0
order by A.Location, A.Number;
Quit;
However, table t3 looks like the following:
In t3, for 'Location' = L1 and 'Number' = 1040, 'Attribute' should include '2DOUBLEBEDS' but not, as table t2 has 'DOUBLEBED' as Attribute (row 😎
Is there anything wrong with my code?
Thank you!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @Astounding
As you mentioned there is space after 'DOUBLEBED'.
I changed the code to:
Proc Sql;
Create Table t3 as select distinct
A.*
from t1 A, t2 B
where trim(A.Attribute) contains trim(B.Attribute)
order by A.Location, A.Number;
Quit;
It returns correct table.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Please do not post data in pictures. How shall we recreate your datasets as they are?
Post data in data steps with datalines, and run your code against the resulting datasets for control.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @Kurt_Bremser Will take care next time!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
A.Attribute and B.Attribute almost certainly contain trailing blanks. The FIND function is not removing those.
The TRIM function can remove trailing blanks (some SQL implementations have a different method for trimming trailing blanks), and the STRIP function (if available) removes both leading and trailing blanks. Within FIND, try using trim(A.Attribute) and trim(B.Attribute).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @Astounding
As you mentioned there is space after 'DOUBLEBED'.
I changed the code to:
Proc Sql;
Create Table t3 as select distinct
A.*
from t1 A, t2 B
where trim(A.Attribute) contains trim(B.Attribute)
order by A.Location, A.Number;
Quit;
It returns correct table.
Thanks!