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;
...
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)')
...
...
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)')
...
The is beatiful! Thanks!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.