Help using Base SAS procedures

Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

Hi all, 

 

Below is a proc sql Oracle  query I’ve written which pulls various promotion history based on elements in a string var.   I would like very much to use regular expressions as opposed to a bunch of LIKE functions. I’m having trouble figuring out how to do this in the Oracle environment. The section of the query I would like to modify using regular expressions is this:

 

and t1.segment_key not like '%N'

and t1.segment_key not like '%C'

and t1.segment_key not like 'CNM'

and (t2.client_promo_id like 'PD%'

or t2.client_promo_id like 'FD%'

or t2.client_promo_id like 'RD%' 

or t2.client_promo_id like 'PE%'

or t2.client_promo_id like 'FE%'

or t2.client_promo_id like 'RE%'

or t2.client_promo_id like 'RLS%'

or t2.promotion_title like '%RL%')

and t2.promotion_title not like '%RGB%'

and t2.promotion_title not like '%Rugby%'

 

Any assistance will be greatly appreciated. Thanks!

 

 

Full query: 

 

proc sql;
connect to oracle (user=xxx path='xxx');
create table Want as
select  * from connection to oracle (
select    
t1.customer_id, 
count(distinct case when t1.promotion_type=1 then t1.promotion_id end) as dm_ttl,
count(distinct case when t1.promotion_type=2 then t1.promotion_id end) as email_ttl
from sas_promo_history_vw t1 join sas_promotion_vw t2       
 on  t1.promotion_id=t2.promotion_id
where t1.promotion_type in (1,2)
and t2.in_home_date between &start_date. and &End_date.
and t1.segment_key not like '%N' 
and t1.segment_key not like '%C' 
and t1.segment_key not like 'CNM' 
and (t2.client_promo_id like 'PD%' 
or t2.client_promo_id like 'FD%' 
or t2.client_promo_id like 'RD%'  
or t2.client_promo_id like 'PE%' 
or t2.client_promo_id like 'FE%' 
or t2.client_promo_id like 'RE%'
or t2.client_promo_id like 'RLS%'
or t2.promotion_title like '%RL%')
and t2.promotion_title not like '%RGB%' 
and t2.promotion_title not like '%Rugby%'
group by t1.customer_id);
disconnect from oracle;quit;

Accepted Solutions
Solution
‎02-10-2017 08:50 AM
Trusted Advisor
Posts: 1,301

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

...
and not regexp_like (t1.segment_key, '[NC]$')
and t1.segment_key != 'CNM'
and (
  regexp_like (t2.client_promo_id, '^([FR]D|[PFR]E|RLS)')
  or t2.promotion_title like '%RL%' )
and not regexp_like (t2.promotion_title,'(RGB|Rugby)')
...

View solution in original post


All Replies
Solution
‎02-10-2017 08:50 AM
Trusted Advisor
Posts: 1,301

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

...
and not regexp_like (t1.segment_key, '[NC]$')
and t1.segment_key != 'CNM'
and (
  regexp_like (t2.client_promo_id, '^([FR]D|[PFR]E|RLS)')
  or t2.promotion_title like '%RL%' )
and not regexp_like (t2.promotion_title,'(RGB|Rugby)')
...
Frequent Contributor
Posts: 101

Re: Proc sql in Oracle: Using Regular Expressions instead of LIKE Function criteria

The is beatiful! Thanks!!!! 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 190 views
  • 1 like
  • 2 in conversation