BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Crubal
Quartz | Level 8

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 😎 

 

Is there anything wrong with my code? 

 

Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Crubal
Quartz | Level 8

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

4 REPLIES 4
Kurt_Bremser
Super User

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.

Crubal
Quartz | Level 8

Thanks @Kurt_Bremser  Will take care next time!

Astounding
PROC Star

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

Crubal
Quartz | Level 8

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!

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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