cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
JFM
‎09-01-2015
  • 4 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Calculate the Days Between Two Transactions for 4 Cohorts

by Calcite | Level 5 in SAS Programming
‎08-13-2015 02:07 PM
‎08-13-2015 02:07 PM
I’m trying to calculate the days between two transactions for 4 cohorts. The cohorts defined by all those which purchased on on these dates: '28SEP2014'd, '28DEC2014'd, '29MAR2015'd, '28JUN2015'd And it’s the difference between the last transaction before the transaction date which define the cohorts. Here is an example:   I start with this. CUSTOMER_ID transaction_date 2312544 12-Oct-14 2312544 21-Oct-14 2312544 19-Nov-14 2312544 11-Dec-14 2312544 28-Dec-14 2312544 9-Jan-15 2312544 10-Jan-15 2312544 11-Jan-15 2616131 10-Aug-08 2616131 7-Dec-08 2616131 16-Dec-12 2616131 12-Apr-13 2616131 24-Jul-13 2616131 28-Sep-14 2616131 3-Nov-14 2616131 9-Mar-15 24526667 28-Jun-15 84895398 29-Mar-15 5555666 16-Dec-12 5555666 12-Apr-13 5555666 30-Jul-13 5555666 28-Sep-14 5555666 30-Sep-14 5555666 15-Oct-14 Then I should get this. CUSTOMER_ID transaction_date 2312544 11-Dec-14 2312544 28-Dec-14 2616131 24-Jul-13 2616131 28-Sep-14 5555666 30-Jul-13 5555666 28-Sep-14 Noticed these two observations were dropped from above because these customers had no previous transactions 24526667 28-Jun-15 84895398 29-Mar-15 And then the final  output should look like this: CUSTOMER_ID transaction_date Days Between 2312544 28-Dec-14 17 2616131 28-Sep-14 431 5555666 28-Sep-14 425 Any assistance will be greatly appreciated. Thanks!!! ... View more

Extract a Category ID from a URL

by Calcite | Level 5 in SAS Programming
‎07-20-2015 01:21 PM
‎07-20-2015 01:21 PM
Hi, I need to C. The ID always follows “categoryId=” in the URL string and its always numbers.  I can’t use SCAN because the ID can be in a different place in the URL and is not always the same length . 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 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&AB=en_US_MLP_P_slot_10_S1_ShopNow http://www.mywebsite.com/family/index.jsp?categoryId=56906456 http://www.mywebsite.com/shop/index.jsp?categoryId=62593866&cp=1766205&ab=ln_men_cs_theshirt http://www.mywebsite.com/family/index.jsp?categoryId=62593876&cp=1795710&AB=en_US_MLP__slot_2_S1_ShopNow http://www.mywebsite.com/shop/index.jsp?categoryId=57155616 http://www.mywebsite.com/shop/index.jsp?categoryId=1766618&ab=tn_women_golfandTennis&cp=17666 Again, its always the numbers which follow  "categoryId=" in the string. There may be other IDs in the URL which don't follow category ID so I need to make sure I'm not picking those up.  This is the output I'm looking for: URL categoryId http://www.mywebsite.com/family/index.jsp?categoryId=61765546&cp=1766205&ab=en_US_MLP_SLOT_1_S1_SHOP 61765546 http://www.mywebsite.com/shop/index.jsp?categoryId=62593996&AB=en_US_HP_S2_Men_slot_1_S2_ShopNow 62593996 http://www.mywebsite.com/family/index.jsp?categoryId=62593846&cp=1766205&ab=ln_men_cs_thetrend:tropicalprints 62593846 http://www.mywebsite.com/family/index.jsp?categoryId=62594006&cp=1766205&AB=en_US_MLP_P_slot_10_S1_ShopNow 62594006 http://www.mywebsite.com/family/index.jsp?categoryId=56906456 56906456 http://www.mywebsite.com/shop/index.jsp?categoryId=62593866&cp=1766205&ab=ln_men_cs_theshirt 62593866 http://www.mywebsite.com/family/index.jsp?categoryId=62593876&cp=1795710&AB=en_US_MLP__slot_2_S1_ShopNow 6259387 http://www.mywebsite.com/shop/index.jsp?categoryId=57155616 57155616 http://www.mywebsite.com/shop/index.jsp?categoryId=1766618&ab=tn_women_golfandTennis&cp=1766618 1766618 Any assistance is greatly appreciated. Thanks! ... View more

Re: INTNX for identifying New and Existing Customers

by Calcite | Level 5 in SAS Programming
‎07-14-2015 04:10 PM
‎07-14-2015 04:10 PM
Hi, Forget about the 18 months. I'm just looking to identify New Customers = have an original_purchase_date in the previous month Existing Customers = have an original_purchase_date before previous month Based on a month end date of %let end='30Jun15'd ... View more

INTNX for identifying New and Existing Customers

by Calcite | Level 5 in SAS Programming
‎07-14-2015 02:27 PM
‎07-14-2015 02:27 PM
Hi, I'm need to working with SAS date functions any help is appreciated. I'm trying to set two flags in a data set, one for New Customers and one for Existing. New Customers = have an original_purchase_date in the previous month Existing Customers = have an original_purchase_date before previous month And I want to pull in all activity for the last 18 months as of the Last day in the previous month where the Last day in the previous month is %let end='30Jun15'd;run; This is what I have so far and I know this code is incorrect. proc sql; create table Cust as select *, case when original_purchase_date between intnx('MONTH', &end, -1) and intnx('MONTH', &end) then 1 else 0 end as new, case when original_purchase_date lt intnx('MONTH', &end, -1) then 1 else 0 end as Existing from orders where original_purchase_date between intnx('MONTH', &end, -1) and intnx('MONTH', &end, -1) -18; quit; Again, any help is appreciated. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-01-2015 07:12 AM
Quick links