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
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.
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;
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.
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
You need STRIP() for CONTAINS
CASE WHEN t1.NAME CONTAINS STRIP( t2.Origin )
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.