topic Re: PROC SQL - using CONTAINS and Variables in SAS Programming

PROC SQL - using CONTAINS and Variables

dirks — Fri, 03 Mar 2017 10:39:44 GMT

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

Re: PROC SQL - using CONTAINS and Variables

gamotte — Fri, 03 Mar 2017 09:59:52 GMT

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;

Re: PROC SQL - using CONTAINS and Variables

Kurt_Bremser — Fri, 03 Mar 2017 10:00:09 GMT

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.

Re: PROC SQL - using CONTAINS and Variables

Ksharp — Fri, 03 Mar 2017 10:32:46 GMT

You need STRIP() for CONTAINS

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

Re: PROC SQL - using CONTAINS and Variables

dirks — Fri, 03 Mar 2017 10:38:54 GMT

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