DATA Step, Macro, Functions and more

Raising Flags using arrays or any other logic

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Raising Flags using arrays or any other logic

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

 


Accepted Solutions
Solution
‎01-25-2016 04:41 AM
Super User
Posts: 6,928

Re: Raising Flags using arrays or any other logic

[ Edited ]
%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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Trusted Advisor
Posts: 1,128

Re: Raising Flags using arrays or any other logic

Could you please explain it further about the 4 new variables to be created depending on the existing variables.
Thanks,
Jag
Occasional Contributor
Posts: 18

Re: Raising Flags using arrays or any other logic

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

Solution
‎01-25-2016 04:41 AM
Super User
Posts: 6,928

Re: Raising Flags using arrays or any other logic

[ Edited ]
%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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 18

Re: Raising Flags using arrays or any other logic

thanks a lot
Super User
Super User
Posts: 7,392

Re: Raising Flags using arrays or any other logic

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 312 views
  • 1 like
  • 4 in conversation