creating a dummy dependent on lagged values

[AmirSari]

Hi all,

Below is a sample of my dataset. I want to create a dummy variable for the auditor’s first time audit with the client

Client_id year auditor_id dummy
1 1990 6 1
1 1991 6 0
1 1992 4 1
1 1993 4 0
1 1994 8 1
1 1995 8 0
1 1996 4 0
1 1997 4 0
2 1990 5 1
2 1991 5 0
2 1992 5 0
2 1993 5 0
2 1994 5 0
2 1995 5 0  

dummy takes 1 for the first observation of each Client_id . For other observations dummy takes 1 only if it is the auditor’s first time audit with the client.

Thanks in advance for your help!  

[Astounding · Accepted solution]

It's easy if you sort your data:

proc sort data=have;
by client_id auditor_ud year;
run;

data want;
set have;
by client_id auditor_id;
dummy = first.auditor_id;
run;

If you want to, sort your data again to put it back in its original order.

[AmirSari]

Thank you!

[Ksharp]

data have;
input Client_id year auditor_id dummy	;
cards;
1 1990 6 1
1 1991 6 0
1 1992 4 1
1 1993 4 0
1 1994 8 1
1 1995 8 0
1 1996 4 0
1 1997 4 0
2 1990 5 1
2 1991 5 0
2 1992 5 0
2 1993 5 0
2 1994 5 0
2 1995 5 0  
;
data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h();
  h.definekey('auditor_id');
  h.definedone();
 end;
set have;
by Client_id;
if first.Client_id then h.clear();
if h.check()=0 then want=0;
 else do;want=1; h.add();end;
run;

[AmirSari]

Thanks, Ksharp! Your solution works too!