SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

sas contains function cannot work properly

Accepted Solution Solved
Reply
Regular Contributor
Posts: 154
Accepted Solution

sas contains function cannot work properly

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:

t1.PNG

 

Table t2 is shown as below:

t2.PNG

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:

t3.PNG

 

In t3, for 'Location' = L1 and 'Number' = 1040, 'Attribute' should include '2DOUBLEBEDS' but not, as table t2 has 'DOUBLEBED' as Attribute (row 8) 

 

Is there anything wrong with my code? 

 

Thank you!

 


Accepted Solutions
Solution
‎03-22-2018 05:36 PM
Regular Contributor
Posts: 154

Re: sas contains function cannot work properly

Posted in reply to Astounding

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.

t4.PNG

 

Thanks!

View solution in original post


All Replies
Super User
Posts: 9,855

Re: sas contains function cannot work properly

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 154

Re: sas contains function cannot work properly

Posted in reply to KurtBremser

Thanks @KurtBremser  Will take care next time!

Super User
Posts: 6,622

Re: sas contains function cannot work properly

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).

Solution
‎03-22-2018 05:36 PM
Regular Contributor
Posts: 154

Re: sas contains function cannot work properly

Posted in reply to Astounding

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.

t4.PNG

 

Thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 199 views
  • 2 likes
  • 3 in conversation