Raising Flags using arrays or any other logic

[SMohanReddy]

Hi Team..could you please help me in building the logic. I have attached the sample file which is having sample input and the output  i am looking for

[Jagadishkatam]

Could you please explain it further about the 4 new variables to be created depending on the existing variables.

[SMohanReddy]

Hi..In the sample data each row is distinct for a customer..so customer 1 has a product A which is active for 3 months and then it was cancelled so a flag has to be raised as active_cancel = 1 and again he reactivated it so cancel_reactivate = 1. customer 2 with the same product A in the second row first its showing as Unknown and then it was reactivated so unknown_reactivate = 1.

please let me know if you need any details. 

thanks for the support

[Kurt_Bremser · Accepted solution]

%let maxcol=12;

data have;
length col_1-col_&maxcol $10;
input col_1-col_&maxcol;
cards;
Active Active Active Cancel Cancel Reactivate Active Active Active Active Active Active
Unknown Unknown Unknown Reactivate Active Active Active Active Active Cancel Cancel Cancel
Active Active Active Cancel Cancel Cancel Cancel Cancel Cancel Cancel Cancel Cancel
;
run;

data want;
set have;
array cols {*} col_1-col_&maxcol;
active_cancel = .;
cancel_reactivate = .;
unknown_reactivate = .;
*cancel_no_movement = .;
do i = 1 to &maxcol - 1;
  if cols{i} = "Active" and cols{i+1} = "Cancel" then active_cancel = 1;
  if cols{i} = "Cancel" and cols{i+1} = "Reactivate" then cancel_reactivate = 1;
  if cols{i} = "Unknown" and cols{i+1} = "Reactivate" then unknown_reactivate = 1;
end;
drop i;
run;

I omitted the fourth flag on purpose, as the original dataset does not have a corresponding value; I also think that active_cancel should be set in all three observations.

If not, you need to specify the rules; but I guess that from my example the technical handling of the array should be obvious.

[SMohanReddy]

thanks a lot

[RW9]

Yes, of course if its any occurence in that string, and its not too long you could simplfy it to a character search:

data want (drop=tmp);
  set have;
  length tmp $2000;
  array col_{12};
  tmp=cats(of col_{*});
  if index(tmp,"ActiveCancel") then active_cancel=1;
  if index(tmp,"CancelReactivate") then cancel_reactive=1;
  if index(tmp,"UnknownReactivate") then unknown_reactive=1;
run;