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
%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.
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
%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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.