Switch

[SASPhile]

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?

[Haikuo]

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

[ballardw]

/* 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;

[slchen]

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

[Ksharp]

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