Hello    I have a starting table as below where I have a rank for each ID on each month.  ID Data Rank  a 31/12/2020 1 a 31/01/2021 1 a 28/02/2021 1 a 31/03/2021 1 a 30/04/2021 1 a 31/05/2021 1 a 30/06/2021 1 a 31/07/2021 3 a 31/08/2021 1 a 30/09/2021 1 a 31/10/2021 1 a 30/11/2021 1 a 31/12/2021 1 b 31/12/2020 1 b 31/01/2021 1 b 31/05/2021 1 b 30/06/2021 1 b 31/07/2021 3 b 31/08/2021 1 b 30/09/2021 1 b 31/10/2021 1 b 30/11/2021 1 b 31/12/2021 1 c 31/12/2020 1 c 31/01/2021 1 c 28/02/2021 1 c 31/03/2021 1 c 30/04/2021 1 c 31/07/2021 1 c 31/08/2021 1 c 30/09/2021 1 c 31/10/2021 1 c 30/11/2021 1 c 31/12/2021 1   I would like to have add a new Value (Max Rank) having the below conditions (Either Proc SQL or SAS Base): 1. If in the next 12 months (E.g. for 31 December 2020 it should check until 31 December 2021) Max Rank is higher than 2 then show Max rank for each ID.  2. If in the next 12 months Max rank is 2 or lower  and there is no missing month (so the ID should be present in all consecutive months (From 31 December 2020 to 31 December 2021) then show Max Rank for each ID.  3. If in the next 12 months Max rank is 2 or lower but there at least one missing month (From 31 December 2020 to 31 December 2021 the ID is not present in one of the months) then Max Rank should be empty for each ID.  A summary of want table is presented below:   ID Data Rank  Max Rank a 31/12/2020 1 3 a 31/01/2021 1 3 a 28/02/2021 1 3 a 31/03/2021 1 3 a 30/04/2021 1 3 a 31/05/2021 1 3 a 30/06/2021 1 3 a 31/07/2021 3 3 a 31/08/2021 1 1 a 30/09/2021 1 1 a 31/10/2021 1 1 a 30/11/2021 1 1 a 31/12/2021 1 1 b 31/12/2020 1 3 b 31/01/2021 1 3 b 31/05/2021 1 3 b 30/06/2021 1 3 b 31/07/2021 3 3 b 31/08/2021 1 1 b 30/09/2021 1 1 b 31/10/2021 1 1 b 30/11/2021 1 1 b 31/12/2021 1 1 c 31/12/2020 1   c 31/01/2021 1   c 28/02/2021 1   c 31/03/2021 1   c 30/04/2021 1   c 31/07/2021 1   c 31/08/2021 1   c 30/09/2021 1   c 31/10/2021 1   c 30/11/2021 1   c 31/12/2021 1     Thank you in advance for your help and apologize if I have skipped anything.  ... View more

I have a large data set which i need to agregate based on the below conditions: 1 The data i have: data have; input Client $ transaction $ date1 MMDDYY10. date2 MMDDYY10. value1 value2; cards; id1 a 05/10/2010 05/10/2010 100 200 id1 a 05/10/2010 05/10/2010 200 200 id1 b 05/10/2010 05/22/2013 200 300 id1 c 05/10/2010 08/15/2016 300 100 id1 a 03/10/2017 03/10/2017 700 200 id1 b 03/10/2017 05/22/2018 200 700 ; RUN;   2. the data i want is as per below: DATA WANT; INPUT Client $ date1 MMDDYY10. Sum1 Sum2; cards; id1 05/10/2010 300 100 id1 03/10/2017 700 700 ; run;   Basically Sum1 is the sum of value 1 where date1=min(date2) and Sum2 is the sum of value2 where date1=max(date2).    the solution can be in sas or sql.     Any help would be highly appreciated.  ... View more

I have a large dataset where for each unique ID I have different start dates and end dates of a given event. Initial data looks like below: ID_number Start_Date End_Date XXX 30APR2019 31OCT2019 XXX 31AUG2018 28SEP2018 XXX 28FEB2018 30MAR2018 XXX 30NOV2017 29DEC2017 XXX 28APR2017 31MAY2017 XXX 31OCT2016 30NOV2016 XXX 30JUN2016 29JUL2016 XXX 31DEC2013 29AUG2014 XXX 30SEP2013 27NOV2013 XXX 31JAN2013 27FEB2013 XXX 31MAY2012 31JUL2012 XXX 26APR2011 30SEP2011 I want to achieve the below end results: ID_number Start_Date End_Date New_Start_date New_End_Date XXX 30APR2019 31OCT2019 30JUN2016 31OCT2019 XXX 31AUG2018 28SEP2018 30JUN2016 31OCT2019 XXX 28FEB2018 30MAR2018 30JUN2016 31OCT2019 XXX 30NOV2017 29DEC2017 30JUN2016 31OCT2019 XXX 28APR2017 31MAY2017 30JUN2016 31OCT2019 XXX 31OCT2016 30NOV2016 30JUN2016 31OCT2019 XXX 30JUN2016 29JUL2016 30JUN2016 31OCT2019 XXX 31DEC2013 29AUG2014 26APR2011 29AUG2014 XXX 30SEP2013 27NOV2013 26APR2011 29AUG2014 XXX 31JAN2013 27FEB2013 26APR2011 29AUG2014 XXX 31MAY2012 31JUL2012 26APR2011 29AUG2014 XXX 26APR2011 30SEP2011 26APR2011 29AUG2014 The logic of the "New_start_date" and "New_End_Date" variables is as below: If less than one year has passed between Start_date and previous End_date, then New_start_date should be equal to the minimum of Start_date for the given ID and New_End_Date should be equal to the maximum of End_Date for the given ID Proc sql can be used as well Any help would be highly appreciated. Thank you in advance. Proc sql can be used as well ... View more

I have a data set structured like below.    ID Date Status x 1/31/2017 0 x 1/31/2017 0 x 2/28/2017 1 y 1/31/2017 0 y 2/28/2017 1 z 1/31/2017 0 z 1/31/2017 0   I want to get the maximum value of column status 1 year from actual date. to do this i am using proc sql but it isn't functioning as it should (i am quite new to this).    what i want to get is: ID Date Status Status after 1 year 1 12/31/2016 0 0 1 1/31/2017 0 1 1 1/31/2018 1     what i get: ID Date Status Status after 1 year 1 12/31/2016 0 1 1 1/31/2017 0 1 1 1/31/2018 1 1   The code i  have used: Proc sql; Select Customer ID, Date, Status, Max (Status) as Status after 1 year from db t1 where Date between t1.Date and t1.date + 365 group by Customer ID, Date   thank you in advance. is there any way to do this without proc sql? if not could you please provide any hint on how to proceed? I am using sas enterprise guide 7.1 ... View more