Create New Variable

[twildone]

Hi....I am trying to create the variable Final from the Decision variable. For each ID and Color group, if there is 'Yes' in the group, then Final is 'Yes' for the last row in the group and all other entries for Final is 'No'. Any suggestions on the best way to do this...Thanks

data Have;
    format ID best12. Color $char6. Part $char1. Decision $char3. Final $char3.;
	input ID : best32. Color : $char6. Part : $char1. Decision : $char3. Final : $char3.;
cards;
1 Yellow A Yes No
1 Yellow B No No
1 Yellow C No Yes
2 Blue A No No
2 Blue B Yes No
2 Blue C No Yes
3 Red A No No
3 Red B No No
3 Red C Yes Yes
4 Orange A Yes No
4 Orange B No Yes
5 Green B No No
5 Green C Yes Yes
6 Red A No No
6 Red C No No
;

[PaigeMiller · Accepted solution]

data want;
    set have;
    by id color;
    length final $ 3;
    retain flag;
    if first.color then flag=0;
    if decision='Yes' then flag=1;
    if not last.color or (last.color and flag=0) then final='No';
    else if last.color and flag=1 then final='Yes';
    drop flag;
run;

[twildone]

Thanks Paige!!!