BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
amanjot_42
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20
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
Meteorite | Level 14

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;

 

OutputOutput

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 499 views
  • 0 likes
  • 4 in conversation