Hi, I have a data set with customer IDs and transaction dates. I need to calculate 5 variables using this data. They are. 1.  Average Days Between Each Transaction 2.  Average Days Between Transactions at 6 month intervals 3.  Average Days Between Transactions at 12 Month intervals 4.  Average Days First and Last Transaction 5.  Average Total Transactions And then I need to aggregate these all together. And then group by year. I start with this data: CUSTOMER_ID transaction_date 440767 13-Jan-07 440767 5-Jan-12 440767 3-Jun-09 440767 17-Jan-12 440767 9-Apr-09 440767 10-Aug-08 1795023 10-Jul-12 1795023 11-Aug-11 1795023 30-Nov-09 1795023 9-Jan-12 1795023 20-Jan-11 1795023 10-Apr-09 1795023 2-Nov-11 1795023 8-Feb-12 1795023 17-Aug-11 1795023 19-Nov-13 3627695 29-May-06 3627695 15-May-03 3627695 23-Mar-15 3627695 18-May-06 here are some examples (they counts don't match the sample data) One output would look like thisAvery Days Between Transaction Counts avr_Days_Betw_Each_trans AvrDaysBetw_trans_sixMonth_Intervals avrDaysBetw_trans_year_Intervals avr_Days between_first_last_trans Total_Trans_Count 10 30 100 1000 50 and one would look like this Year avr_Days_Betw_Each_trans AvrDaysBetw_trans_sixMonth_Intervals avrDaysBetw_trans_year_Intervals avr_Days between_first_last_trans Total_Trans_Count 2012 10 30 100 1000 50 2013 30 60 200 555 85 2014 40 70 300 456 90 Any assistance would be appreciated. Thanks ... View more

Hi all, I'm not very experienced with Macro language so any assistance will be greatly appreciated. I have a series of proc sql statements which pull data for Active, Inactive and Lapsed customers.  I end up with 3 tables. *Customers_Active *Customers_InActive *Customers_Lapsed Active:    0-12M purchaser Inactive: 13-24M purchaser, did not purchase 0-12M (active day range minus 12 months) Lapsed:  25-36M purchaser, did not purchase 0-24M (inactive day range minus 24 months) Again, In order to do this I have 3 separate proc sql statements and I start out by defining 6 macro variables.  I want to apply macro language so  I can uses  just 1 query to produce the 3 tables. %let actstart = '24Feb2013'd ;  %let actend = '22Feb2014'd; %let iactstart = '26Feb2012'd; %let iactend= '23Feb2013'd;  %let lapstart = '27Feb2011'd ; %let lapend = '25Feb2012'd; Here are the three statements /*Active Table*/ proc sql; create table Customers_Active as select household_id, customer_id, web_order_id, transaction_date, sku, quantity, original_price from transaction_vw where transaction_date >= &actstart and transaction_date <= &actend order by customer_id; quit; /*Inactive Table*/ Notice I am not bringing in the customer_id which is already in the Customers_Active table. This tricky when writing the macro. proc sql; create table Customers_Inactive as select household_id, customer_id, web_order_id, transaction_date, sku, quantity, original_price from transaction_vw wheretransaction_date >= & iactstart and transaction_date <= &iactend and customer_id not in (select distinct customer_id from Customers_Active) order by customer_id; quit; /*Lapsed  Table*/ Notice I am not bringing in the customer_id which is not already in the Customers_Active and the Customers_Inactive table. Again, this tricky when writing the macro. proc sql; create table Customers_Lapsed as select household_id, customer_id, web_order_id, transaction_date, sku, quantity, original_price from transaction_vw wheretransaction_date >= & lapstart and transaction_date <= & lapend and customer_id not in (select distinct customer_id from Customers_Active) and customer_id not in (select distinct customer_id from Customers_Inactive) order by customer_id; quit; Again, any help is greatly appreciated. Thanks! ... View more

I need to restructure a data set in order to produce a cross tab output/data set. . The question I'm trying to answer is  what additional products were purchased after initial sign up purchases and what are the product combinations -  So, if  a customer bought prod3 at signup they also eventually bought prod2 and prod4 . The data set I start with looks like this. Notice some of the product purchases dates are the same as the startup date.  ID Signup_dt Prod_type Purchase_Date 2232 4-Jun-14 prod1 4-Jun-14 2232 4-Jun-14 prod2 4-Jun-14 2232 4-Jun-14 prod3 4-Jun-14 2232 4-Jun-14 prod4 2232 4-Jun-14 prod5 4-Aug-14 4545 12-Jun-14 prod1 4545 12-Jun-14 prod2 13-Sep-14 4545 12-Jun-14 prod3 12-Jun-14 4545 12-Jun-14 prod4 12-Nov-14 4545 12-Jun-14 prod5 12-Jun-14 I need to restructure the data set so it looks like this: ID startup_month Poducts Purchase_at_Start_Up prod1 prod2 prod3 prod4 prod5 2232 June prod1 1 1 2232 June prod2 1 1 2232 June prod3 1 1 2232 June prod4 2232 June prod5 4545 June prod1 4545 June prod2 4545 June prod3 1 1 1 4545 June prod4 4545 June prod5 1 1 1 And then a summary data set which gives the totals/counts  of the product combinations Startup_month Poducts Purchase_Start_up_count prod1_dt prod2_dt prod3_dt prod4_dt prod5_dt June prod1 1 1 June prod2 1 1 June prod3 2 1 2 June prod4 June prod5 1 1 1 I'm having trouble visualizing how to program for this. I'm been using different combinations of proc transpose and proc freq but  I can't come close to the desired output.  Any help is greatly appreciated. ... View more

Hi-- I'm putting together a new advertising creative campaign test  - the creatives I'm using have never been tested before and the company has never down and creative test campaign.  Normally, I would I would use proc power and base one proportion off a historical response rate and one proportion off an anticipated lift. So, like this proc power;   TwoSampleFreq   Test=Fisher   Alpha =0.01 0.05 0.1   Sides = 1     GroupProportions = ( p1. p2.)   Power =.9   Npergroup  =.; The issue that I have is that I have no historical benchmark  to base the sample size calculation off of; so,  no prior response rate based on creatives how the creative performed in the past. What I will know is what the total universe of people I can pull a sample from  will be. How should the sample size(s) be calculated when the creative test campaign is new?  This is what my test cells look like: Customers Tactic Sample Size (n=) A. Hold Out Group No Tactic ? B. Control Email Only ? C. Test 1 Direct Mail ? D. Test 2 Email + Direct Mail ? Total (n=) ? Any feedback/advice is greatly appreciated. Thanks!  ... View more