This program uses hash tables to keep the most recent preceding date of each color, which allows the program below to removed any colors that are over 30 days. (Notice that, unlike your example, the 1/12/2016 NEWCOMBO is only RED, since other colors are over 30 days old).
A couple of notes:
The COLORS array keeps all the colors, for use in the CATX function. But note that the array is kept in sorted order, meaning that it starts with blanks, and also NEWCOMBO is a sorted list. That also means when the array is examined for colors eligible for removal, the loop starts at the right-hand end and iterates backwards until a blank color is found.
Therefore
new colors are always put in the first position, since it is expected to be blank ...
... as long as the colors array is larger than the number of colors that may be active at any time.
Regards,
Mark
data have ;
informat date mmddyy10.;
input id $ date color $ expectedCombo $;
format date mmddyy10.;
datalines;
1321 07/10/2015 blue blue
1321 07/11/2015 blue blue
1321 07/12/2015 blue blue
1321 07/13/2015 blue blue
1321 08/18/2015 red blue-red
1321 08/19/2015 blue blue-red
1321 08/20/2015 blue blue-red
1321 08/21/2015 blue blue-red
1321 08/22/2015 blue blue-red
1321 09/15/2015 red blue-red
1321 09/16/2015 blue blue-red
1321 09/17/2015 blue blue-red
1321 09/18/2015 blue blue-red
1321 01/12/2016 red blue-red
1321 01/12/2016 blue blue-red
1321 01/13/2016 blue blue-red
1321 01/14/2016 blue blue-red
1321 01/15/2016 blue blue-red
1321 01/16/2016 blue blue-red
1321 01/17/2016 blue blue-red
1321 01/18/2016 blue blue-red
1321 01/19/2016 green blue-red-green
1321 01/19/2016 blue blue-red-green
1321 01/20/2016 blue blue-red-green
1321 01/21/2016 blue blue-red-green
1321 01/22/2016 blue blue-red-green
1321 01/23/2016 blue blue-red-green
1321 01/24/2016 blue blue-red-green
1321 01/25/2016 blue blue-red-green
;
data want;
if _n_=1 then do;
if 0 then set have;
attrib start format=mmddyy10. length=8;
declare hash history(ordered:'a');
history.definekey('color');
history.definedata('color','start');
history.definedone();
end;
array colors{20} $20 _temporary_;
attrib newcombo length=$400;
retain newcombo;
do until (last.date);
set have;
by id date;
/* Update this color item in the history table */
rc=history.find();
start=date;
rc=history.replace();
/* If it's a new color, add it to beginning of colors array, which will be blank */
if findw(trim(newcombo),trim(color),'-')=0 then colors{1}=color;
/* Use hash table to see if any colors are over 30 days and should be removed*/
/* Start at right-hand end of array because it is sorted (therefore left end */
/* is blank except for the new color) */
current_color=color;
do d=dim(colors) to 1 by -1 while(colors{d}^=' ');
rc=history.find(key:colors{d});
if date-start>30 then colors{d}=' ';
end;
color=current_color;
call sortc(of colors{*});
end;
newcombo=catx('-',of colors{*});
if last.id then do;
rc=history.clear();
call missing(of colors{*});
end;
run;
... View more
