Thank you Tom this is very helpful... I added one more variable (a date variable) to the last inside the CATX, but it shows as number 19389 for example, how can I put is as a date (mmddyy10.)? catx(' ',catx('=','Completed',completed),catx('=','Dropped',dropped), catx('=','Completion Date',compDate)) ... View more

Sorry, RW9, don't know why had the impression you're Roy >< ... View more

Thanks to you guys, Tom is there away to circumvent this error, without inserting same number of variables in the dataset? ERROR: Attempt to insert fewer columns than specified after the INSERT table name. ... View more

Thanks Roy - there is no join required; "Problem" is an empty dataset, that I want to update along the program (using INSERT statement in proc sql) by looking into other dataset and doing some queries ID     Date      Completed      Dropped 1     1/1/2014     yes               yes 2     1/6/2014     yes               no 3     3/6/2014     yes               yes . . . so I just want to query the data above and insert these into an existing data called "problem" that has 3 variables (ID, Date, Note) so that the output would be ID    Date           Note 1     1/1/2014     Completed=yes  Dropped=yes 2     1/6/2014     Completed=yes  Dropped=no 3     3/6/2014     Completed=yes  Dropped=yes hope this explains it better.. ... View more

Thank you guys! ... View more

Thank you guys; this is really helpful. I found stat@sas and Ksharp approach work, but is later is closer a bit to what I needed. Let me add one piece of information to datasets, and please let me k now who would you approach it the best; so have and want data would be like below, with adding an new variable (count) to data1. thanks again. ID    Item  count 1     A   2          1     B   4 1     C   0 2     A  . 2     B   3 2     C   1 Date 2 ID 1 2 3 4 WANT ID     Item  count 1     A   2 1     B   4 1     C   0 2     A   . 2     B   3 2     C   1 3     A   . 3     B   . 3     C   . 4     A   . 4     B   . 4     C   . ... View more

Thank you Peter and Xia very much for taking the time to help with this question! ... View more

Thanks Xia; any idea how to identify and output the (prior) date (trans) when the wanted criterion was met (for example, for the 11/15/2017, also output the prior earliest date when 7 transactions occurred within 6 months)? ... View more

Thanks very much, maybe I'll try to get the prior date in a separate data step. ... View more

Thank you very much Peter! Would you be able to modify to include by client ID and add the (prior) date when the 7 transactions was met? data have; input id  trans mmddyy10.; format trans mmddyy10.; cards; 1 03/25/2007 1 04/15/2007 1 05/13/2007 1 06/10/2007 1 07/08/2007 1 08/12/2007 1 09/16/2007 1 10/14/2007 1 11/11/2007 1 12/08/2007 1 01/01/2008 1 03/02/2008 1 03/30/2008 1 06/02/2008 1 07/21/2008 1 08/20/2008 1 10/15/2008 1 12/10/2008 1 01/11/2009 1 03/15/2009 1 05/17/2009 1 07/05/2009 1 08/23/2009 1 10/18/2009 1 11/15/2009 1 12/13/2009 1 01/10/2010 1 02/28/2010 1 05/16/2010 1 06/13/2010 1 07/04/2010 1 08/01/2010 1 09/27/2010 1 10/25/2010 1 08/29/2010 1 11/15/2010 1 12/18/2010 1 02/12/2011 1 03/23/2011 1 04/25/2011 1 05/21/2011 1 06/18/2011 1 08/22/2011 1 09/17/2011 1 10/22/2011 1 11/26/2011 2 11/09/2007 2 12/13/2007 2 01/05/2008 2 02/08/2008 2 03/07/2008 2 04/05/2008 2 05/30/2008 2 07/20/2008 2 08/15/2008 2 09/19/2008 2 11/10/2008 2 12/12/2008 2 02/13/2009 2 03/30/2009 2 06/01/2009 2 07/20/2009 2 09/21/2009 2 11/13/2009 2 01/15/2010 2 03/29/2010 2 06/18/2010 2 07/16/2010 2 09/03/2010 2 09/25/2010 2 10/29/2010 2 11/20/2010 2 12/11/2010 2 01/14/2011 2 03/18/2011 2 04/22/2011 2 06/03/2011 2 06/30/2011 2 07/28/2011 2 08/25/2011 2 09/29/2011 2 11/19/2011 2 12/16/2011 2 01/13/2012 2 02/09/2012 2 03/02/2012 2 04/05/2012 2 05/03/2012 2 06/28/2012 2 08/10/2012 2 10/19/2012 2 12/29/2012 ; run; ... View more