I think I am so close.  I'm still getting negative numbers .      MSTR_CUSTOMER_ID transaction_date days_passed winback_12 winback_24 winback_36 winback_48 10 19-Oct-13 . N N N N 10 9-Nov-14 140 N N N N 12 29-Nov-14 20 N N N N 12 12-Dec-15 269 N N N N 13 1-Nov-12 -1136 N N N N 13 28-Mar-16 72 N N N N 23 3-Jul-13 -999 N N N N 23 1-Apr-16 106 Y Y Y Y 26 10-Jul-14 -631 N N N N 26 16-Aug-14 37 N N N N 33 30-Jun-13 -412 N N N N 43 7-Jun-13 -23 N N N N 43 14-Jul-15 388 Y Y Y Y 52 10-Nov-13 -611 N N N N 56 3-Jul-13 -130 N N N N 78 14-Dec-12 -201 N N N N 78 2-Oct-16 532 Y Y Y Y 79 14-Aug-15 -415 N N N N 89 14-Aug-14 -365 N N N N 95 4-Mar-14 -163 N N N N 95 5-Mar-14 1 N N N N   my lastest version   data days; set have; by mstr_customer_id ; days_passed = dif(transaction_date); if first.mstr_customer_id then do; winback_12='N'; winback_24='N'; winback_36='N'; winback_48='N'; retain winback_:; end; if first.mstr_customer_id =0 then do; if days_passed < 12 * 30 then do; end; else if days_passed < 24 * 30 then do; winback_48='Y'; winback_36='Y'; winback_24='Y'; winback_12='Y'; end; else if days_passed < 36 * 30 then do; winback_24 = 'Y'; winback_36 = 'Y'; winback_48 = 'Y'; end; else if days_passed < 48 * 30 then do; winback_36 = 'Y'; winback_48 = 'Y'; end; if last.mstr_customer_id; end;run; ... View more

like this?      data days; set dat.T_Hist_PFS; by mstr_customer_id transaction_date; days_passed = dif(transaction_date); if first.mstr_customer_id then do; winback_12='N'; winback_24='N'; winback_36='N'; winback_48='N'; end; retain winback_:; if days_passed < 12 * 30 then do; end; else if days_passed < 24 * 30 then do; winback_48='Y'; winback_36='Y'; winback_24='Y'; winback_12='Y'; end; else if days_passed < 36 * 30 then do; winback_24 = 'Y'; winback_36 = 'Y'; winback_48 = 'Y'; end; end; if last.mstr_customer_id;;run; ... View more

So, I should say, its not resetting by ID.  ... View more

Hi Astounding !!!! Thanks! last question.   The output dosent look right.   It doesnt look like its subsetting by mastr_customer_id and I'm getting negavtive numbers.  did I not modify the code correctly?      data days; set have; retain winback_:; by mstr_customer_id transaction_date; days_passed = dif(transaction_date); if first.mstr_customer_id=0 then do; if days_passed < 12 * 30 then do; end; else if days_passed < 24 * 30 then do; winback_48='Y'; winback_36='Y'; winback_24='Y'; winback_12='Y'; end; else if days_passed < 36 * 30 then do; winback_24 = 'Y'; winback_36 = 'Y'; winback_48 = 'Y'; end; if last.mstr_customer_id; end;run; ... View more

Thanks astonding.  After reading your reply, I think days would make more sense, and yes,  the 12-month category being "Y" also force all the other categories to be "Y”.   I’m a little confused with the syntax.  Any additional assistance is greatly appreciated! ... View more

Thanks!  Do you have a recommendation or example of how to count the number customers that shopped and then didn’t shop again for another 18 months? ... View more

  Unfortunately  that did not seem to work. When I try and open the file, I get an error.     ods listing close; %macro datasets ( LD=, SD=);   options rightmargin=.1in leftmargin=.1in Bottommargin=.1in topmargin=.1in Papersize='legal'; ods tagsets.excelxp file="/sasdata/mine/datasets/Report.xls" style=minimal options( ORIENTATION='portrait' AUTOFIT_HEIGHT='yes' embedded_titles='yes' PAGE_ORDER_ACROSS='yes' sheet_name=&SD.); TITLE JUSTIFY=l FONT='Arial Narrow' HEIGHT=14pt &LD.; proc report data=check nowd style(column)={just=c font_size=11pt font_face= 'Arial Narrow'cellwidth=2.in} style(header)={font_size=13pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD background=CXFFE4E1}; column c_Total_Responders m_Total_Responders Total_Responders_diff c_total_customers m_total_customers total_customers_diff c_Total_Target_Sales m_Total_Target_Sales Total_Target_Sales_diff c_Total_All_Sales m_Total_All_Sales Total_All_Sales_diff; where Customer_Status =1 and short_description=&sd.; run; quit; title; ods listing; %mend ; %datasets (LD="RLE Men's Apparel-Core Model ", SD="RLEMECORA "); %datasets (LD="RLE Women’s Apparel-Core Model ", SD="RLEWOCORA "); %datasets(LD="RLE Men's Apparel-Luxury Model ", SD="RLEMELUXA"); %datasets (LD="RLE Women's Apparel-Luxury Model ", SD="RLEWOLUXA" ); ods tagsets.excelxp close;     ... View more

Thanks.  Question: but if I have a unique file for each, how to a combine them all into one workbook via the macro.   I will ultimately have many more than 4 passes and will take forever to combie into one workbook.  ... View more