topic Re: Switch in SAS Programming

Switch

SASPhile — Tue, 13 May 2014 20:56:30 GMT

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?

Re: Switch

Haikuo — Tue, 13 May 2014 21:43:30 GMT

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

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

Re: Switch

ballardw — Tue, 13 May 2014 21:59:34 GMT

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

Re: Switch

slchen — Wed, 14 May 2014 01:49:27 GMT

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

Re: Switch

Ksharp — Wed, 14 May 2014 13:09:46 GMT

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