acct_nbr id trig_cd
12345 C1 NAC
12345 C1 MNT
12345 C2 MNT
12345 C1 MNT
23456 C1 NAC
23456 C2 MNT
23456 C1 MNT
34567 C1 NAC
34567 C2 MNT
34567 C2 MNT
45678 C1 NAC
45678 C2 MNT
45678 C3 MNT
56789 C1 NAC
56789 C2 MNT
56789 C3 MNT
56789 C1 MNT
In the above sample data, if an acct_nbr switches id and the last id is the same as first id where the trig_cd is "NAC" then it is called a switch. and I need to find out how many id's had switched.
for example,acct_nbr 12345 for trig_cd NAC, the initial id is C1. Later it remained C1, switched to C2 and for the last record it was C1 again. Here it is a Switch.
Similarly, acct_nbr 23456 initially it was C1, swictched to C2 and then back to C1, It is a Switch,
Acct_nbr 34567, it was C1,then remained C2 and never got back to C1. Hence Not a Switch,
Acct_nbr 45678 initially C1, Switched to C2 and C3 and never got back to C1,Hence Not a Switch,
Acct_nbr 56789 is also a switch
total number if acct_nbr's that had switched is 3 (12345 and 23456 and 56789)
How to get this?
Here is one way:
data have;
input (acct_nbr id trig_cd) (:$);
cards;
12345 C1 NAC
12345 C1 MNT
12345 C2 MNT
12345 C1 MNT
23456 C1 NAC
23456 C2 MNT
23456 C1 MNT
34567 C1 NAC
34567 C2 MNT
34567 C2 MNT
45678 C1 NAC
45678 C2 MNT
45678 C3 MNT
56789 C1 NAC
56789 C2 MNT
56789 C3 MNT
56789 C1 MNT
;
data want;
do unTIL (last.acct_nbr);
set have END=FINISHED;
by acct_nbr notsorted;
LENGTH _NAC $ 8;
if trig_cd='NAC' THEN _NAC=ID;
IF ID NE LAG(ID) THEN FLAG=SUM(FLAG,1);
IF LAST.ACCT_NBR AND ID=_NAC AND FLAG THEN SWITCH+1;
END;
IF FINISHED THEN OUTPUT;
KEEP SWITCH;
RUN;
Haikuo
/* sets switch flag 0=false, 1=true for each acct_nbr*/ I didn't bother to drop variables from the output.
data want;
set have;
by acct_nbr notsorted;
retain switch firstid;
if first.acct_nbr then do;
/* reset retained variables*/
switch = .;
firstid = id;
if trig_cd ne 'NAC' then switch=0;
end;
if id ne firstid and switch = . then switch = 1;
if last.acct_nbr then do;
if firstid = id then do;
if switch = 1 then switch=1;
else switch= 0;
end;
else switch = 0;
output;
end;
run;
data want;
set have;
by acct_nbr;
retain temp _trig_cd flag;
if first.acct_nbr then do;
temp=id;
_trig_cd=trig_cd;
end;
if temp^=id then flag=1;
if last.acct_nbr then do;
if id=temp and _trig_cd='NAC' and flag=1 then switch=1;
else switch=0;
output;
end;
drop temp _trig_cd flag;
run;
Message was edited by: Shenglin Chen
data have; input (acct_nbr id trig_cd) (:$); cards; 12345 C1 NAC 12345 C1 MNT 12345 C2 MNT 12345 C1 MNT 23456 C1 NAC 23456 C2 MNT 23456 C1 MNT 34567 C1 NAC 34567 C2 MNT 34567 C2 MNT 45678 C1 NAC 45678 C2 MNT 45678 C3 MNT 56789 C1 NAC 56789 C2 MNT 56789 C3 MNT 56789 C1 MNT ; run; data want; equal=0;switch=0;nac=0; do until(last.acct_nbr); set have; by acct_nbr; if first.acct_nbr then do; _id=id; if trig_cd='NAC' then nac=1; end; if acct_nbr=lag(acct_nbr) and id ne lag(id) then switch=1; if last.acct_nbr and _id=id then equal=1; end; if equal and switch and nac then output; run; proc sql; select count(*) from want; quit;
Xia Keshan
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.