creation of dummy with multiple observations

[amanjot_42]

Hi guys,

I am trying to create a dummy where the year is equal to the audit year. There are multiple observations for a particular firm.  

Firm ID  Year    Audit_year        Dummy (what I want)
11      1990       .                   0
11      1991       .                   0 
11      1992   1993                    0
11      1993       .                   1
11      1993       .                   1
11      1993       .                   1
11      1994   1995                    0
11      1995       .                   1  
11      1996       .                   0
12      2001       .                   0
12      2002   2003                    0
12      2003      .                    1
12      2004      .                    0
12     2005       .                    0
12     2006       .                    0
12     2007       .                    0
12     2008       .                    0
12     2009       .                    0

In the latter table, audit years are one year ahead of YEAR. Please help with respect to the codes

Regards,
Amanjot

[PeterClemmensen]

data have;
input FirmID Year Audit_year;
datalines;
11 1990 .
11 1991 .
11 1992 1993
11 1993 .
11 1993 .
11 1993 .
11 1994 1995
11 1995 . 
11 1996 .
12 2001 .
12 2002 2003
12 2003 .
12 2004 .
12 2005 .
12 2006 .
12 2007 .
12 2008 .
12 2009 .
;

data want(drop=_:);
   merge have
         have (firstobs=2 keep=FirmID Year rename=(FirmID=_FirmID Year=_Year));
   dummy = FirmID = _FirmID & Audit_year = _Year;
run;

Result:

FirmID Year  Audit_year dummy 
11     1990  .          0 
11     1991  .          0 
11     1992  1993       1 
11     1993  .          0 
11     1993  .          0 
11     1993  .          0 
11     1994  1995       1 
11     1995  .          0 
11     1996  .          0 
12     2001  .          0 
12     2002  2003       1 
12     2003  .          0 
12     2004  .          0 
12     2005  .          0 
12     2006  .          0 
12     2007  .          0 
12     2008  .          0 
12     2009  .          0 

[ed_sas_member]

Hi @amanjot_42 

Here is an attempt to do this:

data want (drop = _Audit_year);
	set have;
	if Audit_year ne . then _Audit_year=0;
	_Audit_year + Audit_year;
	if _Audit_year = Year then Dummy = 1;
	else Dummy = 0;
run;

Output

[Astounding · Accepted solution]

data want;
set have;
by firmID;
if first.firmID or audit_year > . then wanted = audit_year;
dummy = year = wanted > . ;
drop wanted;
retain wanted;
run;