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

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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.

View solution in original post

4 REPLIES 4
gamotte
Rhodochrosite | Level 12

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;
Kurt_Bremser
Super User
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.

dirks
Quartz | Level 8

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
Ksharp
Super User

You need STRIP() for CONTAINS

 

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

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 41261 views
  • 4 likes
  • 4 in conversation