Use SAS to keep priority variable in column B from Column A (delete duplicate row), but do not delete rows that have other value in column B

Question

I need a SAS code where Column B is run against Column A, need to keep values where column B = “red” If column B value for all equal column A value has no “red” variable, keep “blue” ex:

A       B
ABC12   red
ABC12   blue
ABC12   green
ABC13   green
ABC13   blue
ABC13   blue

after code executed:

A       B
ABC12   red
ABC13   blue

data have;
input A $ B $;
datalines;
ABC12 red
ABC12 blue
ABC12 green
ABC13 green
ABC13 blue
ABC13 blue
;


data want;
set have;
by A;
retain keep;
if first.A then keep = 0;
if B = "red" then do;
    keep = 1;
    output;
end;
else if B = "blue" then keep = keep;
else keep = 0;
if last.A and keep then output;
drop keep;
run;

I'm getting ABC12 red in my output, the ABC13 is not observed. Anyone have any tips?

Answer 1

Try this:

data have;
input A $ B $;
datalines;
ABC12 red
ABC12 blue
ABC12 green
ABC13 green
ABC13 blue
ABC13 blue
ABC14 blue
ABC14 red
ABC14 blue
ABC15 red
ABC15 red
ABC15 green
;
run;
proc print;
run;

data want;
  length keep $ 5;
  drop keep;

  do until(last.A);
    set have;
    by A;
    
    if B='blue' and keep NE 'red' then keep=B;
    if B='red'                    then keep=B;
  end;

  do until(last.A);
    set have;
    by A;
    if keep=B then 
      do;
        output; 
        keep = ""; /* add this to only get 1 row if there are many, e.g.: red red green */
      end;
  end;
run;
proc print;
run;

and read aboud DoW-loop in SAS, for example here.

---

An alternative would be to use POINT=:

data want2;
  set have curobs=curobs;
  by A;

  if first.A then keep = 0;

  if B='blue' and keep<=0 then keep=-curobs;
  if B='red'              then keep= curobs;


  if last.A and keep then 
    do;
      keep=abs(keep);
      set have point=keep;
      output;
    end;
run;
proc print;
run;