DATA Step, Macro, Functions and more

PROC SQL - using CONTAINS and Variables

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

PROC SQL - using CONTAINS and Variables

[ Edited ]

Hi,

 

is it possible to use a variable instead of a string for the CONTAINS operator?

If t1.NAME contains the string of t2.Origin, the variable Type should contain Pickup.

 

 

I need something like this:

 

CASE WHEN t1.NAME CONTAINS t2.Origin THEN 'Pickup' ELSE 'Not Defined' END AS Type

 

t1.NAME looks like this: "Night/Weekend/Holidaysurcharge (Munich)"

t2.Origin = "Munich"

 

I allready tried this, but that doesnt work either:

 

CASE WHEN t1.NAME CONTAINS CAT(t2.Origin) THEN 'Pickup' ELSE 'Not Defined' END AS Type

 

Is this even possible?

 

Best regards

Dirk

 

 

 


Accepted Solutions
Solution
‎03-03-2017 05:34 AM
Super User
Posts: 6,982

Re: PROC SQL - using CONTAINS and Variables

case
  when index(t1.name,strip(t2.origin)) > 0
  then 'Pickup'
  else 'Not Defined'
end as type

if the contains operator does not allow variables.

Always keep in mind that SAS character variables have a defined length and will be padded with blanks, so you need to remove those blanks for certain purposes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Regular Contributor
Posts: 194

Re: PROC SQL - using CONTAINS and Variables

Hello,

 

Can you post example data in the form of a datastep and your code.

Using a variable for the matched substring should not be a problem.

 

data have;
	input str1 $10. str2 $8.;
	datalines;
impossiblepossible
;
run;

proc sql;
	CREATE TABLE want AS
	SELECT *
	FROM have
	WHERE str1 CONTAINS str2;
quit;
Solution
‎03-03-2017 05:34 AM
Super User
Posts: 6,982

Re: PROC SQL - using CONTAINS and Variables

case
  when index(t1.name,strip(t2.origin)) > 0
  then 'Pickup'
  else 'Not Defined'
end as type

if the contains operator does not allow variables.

Always keep in mind that SAS character variables have a defined length and will be padded with blanks, so you need to remove those blanks for certain purposes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 34

Re: PROC SQL - using CONTAINS and Variables

[ Edited ]

Thank you. I did not know this...

If I click into the output, there where no blanks. But they seem to exist.

 

CASE WHEN t1.NAME CONTAINS CAT(TRIM(t1.Origin)) THEN 'Pickup'
WHEN  t1.NAME CONTAINS CAT(TRIM(t1.Destination)) THEN 'Delivery'
ELSE 'Not Defined' END
Super User
Posts: 9,691

Re: PROC SQL - using CONTAINS and Variables

You need STRIP() for CONTAINS

 

CASE WHEN t1.NAME CONTAINS  STRIP( t2.Origin )

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 1628 views
  • 3 likes
  • 4 in conversation