I have some SAS code that applies a Regular Expression to a URL string which pulls out a Category ID. The Variable looks like this URL http://www.mywebsite.com/family/index.jsp?categoryId=61765546&cp=1766205&ab=en_US_MLP_SLOT_1_S1_SHOP http://www.mywebsite.com/shop/index.jsp?categoryId=62593996&AB=en_US_HP_S2_Men_slot_1_S2_ShopNow I apply this code: data Want; set have; category_Id=input(prxchange('s/.+categoryId=(\d+).+/$1/o',-1,URL), 12.);run; I get this: category_Id 61765546 62593996 The data set I have actually has many URL variables – URL_1, URL_2, URL_3….. and so on. It could go up to 80 URL vars or it could be 10. What I need to do is pull the category IDs out of all the URL vars and then transpose them by Campaign_Name. The data looks like this. Campaign_Name URL_1 URL_2 URL_3 URL_4 Summer Campaign http://www.mywebsite.com/family/index.jsp?categoryId=61765546 http://www.mywebsite.com/shop/index.jsp?categoryId=62593996&AB=en_US_HP_S2_Men_slot_1_S2_ShopNow http://www.mywebsite.com/family/index.jsp?categoryId=62593846&cp=1766205&ab=ln_men_cs_thetrend:tropicalprints http://www.mywebsite.com/family/index.jsp?categoryId=62594006&cp=1766205 What I need is this: Campaign_Name category_Id Summer Campaign 61765546 Summer Campaign 62593996 Summer Campaign 62593846 Summer Campaign 62594006 ... View more

Hi, I have two questions in regard to pulling data through Oracle using Proc SQL : I have a query which pulls through Oracle and I wanted to use the INTNX function to make the data selection process easier but unfortunately it won’t work in Oracle.  Is there a work around? intnx('MONTH', &end, -19, 'B') and intnx('MONTH', &end, -1, 'E')   2.  I’m having to always change the date formats to SAS formats after I run a query in Oracle. Is there a way to do this within the first pass? Below is my query. Note the macro var end is in a format Oracle understands. %let end='01Jun2015'; proc sql; connect to oracle (user=usdm password=passdm path='A7P5OLKL'); create table T_Hist  as select * from connection to oracle ( select distinct t.customer_id, t.web_order_id, a.original_purchase_date, t.transaction_date, t.amount from mbs.sas_transaction_vw t   left join mbs.sas_customer_bu_vw a on t.customer_id = a.customer_id and a.business_unit = 3 where original_purchase_date between intnx('MONTH', &end, -19, 'B') and intnx('MONTH', &end, -1, 'E') /*Does not work*/ and t.business_unit = 3 and t.transaction_type = 1 and t.transaction_sub_type = 1 and t.customer_id not in (select distinct customer_id from mbs.sas_customer_vw where regexp_like(nvl(suppress_reasons,'*'),'[EF]'))); disconnect from oracle;quit; /* Change date var*/ data T_Hist(rename=(original_purchase_date_new=original_purchase_date TRANSACTION_DATE_new=Transaction_date)); set T_Hist; original_purchase_date_new = datepart(original_purchase_date); TRANSACTION_DATE_new=datepart(TRANSACTION_DATE); format original_purchase_date_new TRANSACTION_DATE_new date9.; drop original_purchase_date TRANSACTION_DATE; run; Thanks!! ... View more

Hi, I want to bring back all the observations which are greater than or equal to the last transaction date before event_ST or in other words all the observations which are greater than or equal to the Maximum transaction_date that is less than  event_ST This is what I have: CUSTOMER_ID transaction_date 249279 29-May-14 249279 1-Jun-14 249279 22-Aug-14 249279 1-Dec-14 249279 13-Dec-14 363555 29-Jun-14 363555 1-Jul-14 363555 21-Aug-14 363555 22-Dec-14 363555 13-Feb-15 This is what I want CUSTOMER_ID transaction_date 249279 22-Aug-14 249279 1-Dec-14 249279 13-Dec-14 363555 21-Aug-14 363555 22-Dec-14 363555 13-Feb-15 I have been experimenting with syntax combos and I can’t seem to get it right (max(transaction_date) < &event_ST Here is my code so far: %let event_ST='21Aug14'd; proc sql; create table a.have as select distinct customer_id, web_order_id, datepart(transaction_date) as transaction_date format=date9. , max(quantity) as quantity , sum(amount) as amount format dollar10.2 from a.Cust_trans group by customer_id, TRANSACTION_DATE order by customer_id, TRANSACTION_DATE; quit; Any help will be greatly appreciated! ... View more