Dummy Variable

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

Dummy Variable

Hi Experts,

I need to do the following tasks.

Option 1: If an insiderID has trades in the same months (for example, January) of last two consecutive years, then trade of January in this year will be regular trade and hence the dummy variable 'irregular_trades' will be 0. For example, this insiderID (283990) has trade in January 2006 (03/01/2006). This will be irregular trade and hence 'irregular_trade' dummy will be 1 since this insiderID does not have any trade in January in 2004 and 2005. In the same way, trades in 08/05/2006, 18/09/2006 and 29/09/2006 will also be irregular trades. However, trade in 14/12/2006 will be regular trade since he has trades in December in 2004 and 2005, hence 'irregular_trades' dummy will be 0 for this trade date (14/12/2006). In the same way, trades in 2007 will be regular or irregular trades based on the trades in 2005 and 2006.

Option 2: If an insiderID has trades in the same months (for example, January) of first two consecutive years, then he is a regular trader. The dummy 'irregular_trader' will be 0 and trades of January in all other years will be regular trade and hence the dummy variable 'irregular_trades' will be 0.

I need to do both options separately. Please note that I have many more insiderIDs in the main dataset.

Kind regards,

Abu

insiderID  year    CompanyISIN    tradedate

2839902004US150934883501/07/2004
2839902004US150934883510/12/2004
2839902005US150934883503/01/2005
2839902005US150934883505/04/2005
2839902005US150934883531/05/2005
2839902005US150934883501/07/2005
2839902005US150934883512/12/2005
2839902006US150934883503/01/2006
2839902006US150934883508/05/2006
2839902006US150934883518/09/2006
2839902006US150934883529/09/2006
2839902006US150934883514/12/2006
2839902007US150934883526/03/2007
2839902007US150934883527/03/2007
2839902008US150934883518/03/2008
2839902008US150934883519/03/2008
2839902009US150934883531/07/2009
2839902009US150934883511/09/2009
2839902009US150934883525/09/2009
2839902009US150934883522/10/2009
2839902009US150934883510/11/2009
2839902010US150934883525/03/2010
2839902010US150934883529/06/2010
2839902010US150934883530/06/2010
2839902010US150934883509/08/2010
2839902010US150934883510/08/2010
2839902010US150934883529/09/2010
2839902010US150934883530/09/2010
2839902010US150934883505/11/2010
2839902010US150934883510/12/2010
2839902010US150934883515/12/2010
2839902010US150934883516/12/2010
2839902011US150934883530/12/2010
2839902011US150934883531/12/2010
2839902011US150934883526/01/2011
2839902011US150934883527/01/2011
2839902011US150934883523/02/2011
2839902011US150934883525/03/2011
2839902011US150934883513/05/2011
2839902011US150934883529/07/2011
2839902011US150934883526/08/2011
2839902011US150934883516/09/2011
2839902011US150934883521/09/2011
2839902011US150934883530/09/2011
2839902011US150934883528/10/2011
2839902011US150934883530/11/2011
2839902011US150934883529/12/2011
2839902012US150934883527/01/2012
2839902012US150934883524/02/2012
2839902012US150934883521/03/2012
2839902012US150934883530/03/2012
2839902012US150934883527/04/2012
2839902012US150934883528/06/2012
2839902012US150934883529/06/2012
2839902012US150934883530/07/2012
2839902012US150934883510/08/2012
2839902012US150934883522/08/2012
2839902012US150934883531/08/2012
2839902012US150934883520/09/2012
2839902012US150934883526/09/2012

Accepted Solutions
Solution
‎06-17-2015 10:12 AM
Grand Advisor
Posts: 9,584

Re: Dummy Variable

OK. No problem.

Code: Program

data have;
infile cards truncover expandtabs;
input tradedate : ddmmyy10. year  insiderID   ;
format tradedate  ddmmyy10.;
cards;
11/11/2004 2004 102174
14/11/2005 2005 102174
28/11/2005 2005 102174
02/12/2005 2005 102174
20/11/2006 2006 102174
07/02/2007 2007 102174
21/05/2007 2007 102174
28/02/2008 2008 102174
01/12/2010 2010 102174
15/02/2011 2011 102174
03/03/2011 2011 102174
03/11/2006 2006 137694 
05/11/2010 2010 137694 
10/11/2011 2011 137694 
26/01/2012 2012 137694 
20/11/2012 2012 137694 
;
run;


proc sql;
create table option1 as
select *,
   case   when a.year between (select min(year) from have where insiderID=a.insiderID) and
   (select min(year) from have where insiderID=a.insiderID)+1   then  .
   when (select count(distinct year) from have
   where insiderID=a.insiderID and
   year between a.year-2 and a.year-1 and
   month(tradedate)=month(a.tradedate ) ) gt 1 then 0
   else 1 end as irregular_trades  
  from have as a;




create table first_two_year as
select *,month(tradedate) as month
  from have
   group by insiderID
   having year = min(year) or year=min(year)+1; 

create table first_two_year_month as
select *
  from first_two_year
   group by insiderID,month
   having count(distinct year) gt 1 ;
  
create table option2 as
select *,case
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
  else 1 end as irregular_trader, 

case
  when a.year between (select min(year) from have where insiderID=a.insiderID) and
  (select min(year) from have where insiderID=a.insiderID)+1   then  .
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID and month=month(a.tradedate)
   ) then 0
   else 1 end as irregular_trades
  from have as a;
 
create table option3 as
select *,case
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
  else 1 end as irregular_trader,

case
  when a.year between (select min(year) from have where insiderID=a.insiderID) and
  (select min(year) from have where insiderID=a.insiderID)+1   then  .
  when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
   else 1 end as irregular_trades
  from have as a; 
 
 
quit;

Xia Keshan

View solution in original post


All Replies
Regular Contributor
Posts: 183

Re: Dummy Variable

HI experts,

Please let me know if you need further information to solve this task.

Grand Advisor
Posts: 9,584

Re: Dummy Variable

If I understand what you mean.

Code: Program

data have;
infile cards truncover expandtabs;
input insiderID  year   CompanyISIN  : $40.   tradedate : ddmmyy10.;
format tradedate  ddmmyy10.;
cards;
283990 2004 US1509348835 01/07/2004
283990 2004 US1509348835 10/12/2004
283990 2005 US1509348835 03/01/2005
283990 2005 US1509348835 05/04/2005
283990 2005 US1509348835 31/05/2005
283990 2005 US1509348835 01/07/2005
283990 2005 US1509348835 12/12/2005
283990 2006 US1509348835 03/01/2006
283990 2006 US1509348835 08/05/2006
283990 2006 US1509348835 18/09/2006
283990 2006 US1509348835 29/09/2006
283990 2006 US1509348835 14/12/2006
;
run;
proc sql;
create table option1 as
select *,
   case when (select count(*) from have
   where insiderID=a.insiderID and
   year between a.year-2 and a.year-1 and
   month(tradedate)=month(a.tradedate ) ) gt 1 then 0
   else 1 end as irregular_trades  
  from have as a;
quit;


proc sort data=have(keep=insiderID  year tradedate ) out=temp nodupkey;
by insiderID  year tradedate ;
run;
data first_two_year;
set temp;
by insiderID  year ;
month=month(tradedate);
if first.insiderID then n=0;
if first.year then n+1;
if n lt 3;
drop n tradedate;
run;
proc sql;
create table first_two_year_month as
select *
  from first_two_year
   group by insiderID,month
   having count(*) gt 1;
  
create table option2 as
select *,case when
   exists(
   select * from first_two_year_month where month=month(a.tradedate)
   ) then 0
   else 1 end as irregular_trader
  from have as a;
quit;

Xia Keshan

Regular Contributor
Posts: 183

Re: Dummy Variable

Hi Xia, Thanks a lot for your reply.

Option 1 is not working properly. For example, 18/03/2008 and 19/03/2008 become 0, but should be 1 since there were no trades in March in both of 2006 and 2007 (only there was trade in March 2006). In the same way, 29/09/2010 and 30/09/2010 show 0, but there were no trades in September of both 2008 and 2009 (only there was trade in september 2009). If an insiderID has trades in the same months (for example, March) of last two consecutive years (for example, 2006 and 2007), then trade of March in this year (for example 2008) will be regular trade and hence the dummy variable 'irregular_trades' will be 0, otherwise it will be 1.

Option 2 is working only with the data I provided. When I am running your code for my dataset, it is not working again. All observations of 'irregular_trader' dummy are showing 0. This is because first two years are 2004 and 2005 in the data that I provided. But in my dataset, for an an insiderID, the first two years are like 2006 and 2010 or 2004 and 2006. You need to consider first two consecutive years. If there are no trades in first two consecutive years then 'irregular_trader' will be 1. 'irregular_trade' dummy will also be 1 for all years.

For example, for option 2, you need to create two dummy variables. If an insiderID has trades in the same months (for example, January) of first two consecutive years of his trades (say 2004 and 2005), then he is a regular trader. The dummy 'irregular_trader' will be 0 for that month for all years. Another dummy will be 'irregular_trade'. Trades of that month (say, January) in all other years will be regular trade and hence the dummy variable 'irregular_trades' will be 0, otherwise it will be 1. Please note that dummy 'irregular_trade' of first two consecutive years of his trades (say 2004 and 2005) will be blank.


I need to do another option (option 3). Option 3 is similar to option 2 but there is a difference. In option 3, like option 2, if an insiderID has trades in the same months (for example, January) of first two consecutive years of his trades (say 2004 and 2005), then he is a regular trader. The dummy 'irregular_trader' will be 0 for all years of his trades. Another dummy will be 'irregular_trade'. All trades of all months in all other years (except first two consecutive years of his trades) will be regular trade and hence the dummy variable 'irregular_trades' will be 0, otherwise it will be 1. Please note that dummy 'irregular_trade' of first two consecutive years of his trades (say 2004 and 2005) will be blank.


Grand Advisor
Posts: 9,584

Re: Dummy Variable

And you should post the output .

Maybe you could change this to

month(tradedate)=month(a.tradedate ) ) gt 1 then 0

   else 1 end as irregular_trades

--->

month(tradedate)=month(a.tradedate ) ) gt 1 then 

   else 0  end as irregular_trades 

 

Regular Contributor
Posts: 183

Re: Dummy Variable

Thanks again. But it's not working. The output shows 'irregular_trades' 0 in most of the observations (for option1).

What about option 2 and option 3? I believe that you will be able solve all of these 3 options.

Grand Advisor
Posts: 9,584

Re: Dummy Variable

Show your output .

Regular Contributor
Posts: 183

Re: Dummy Variable

It's a big dataset so I cannot post the output but I am attaching subset of the dataset for your consideration.

I cannot upload the file here, so I sent it to your email (....@qq.com)

Regular Contributor
Posts: 183

Re: Dummy Variable

It's not working. For option1, all the observations of the dummy variable show 1 and for option2, all the observations of the dummy variable show 0.

Grand Advisor
Posts: 9,584

Re: Dummy Variable

I know where is wrong. You have multiple obs for the same month.

Code: Program

data have;
infile cards truncover expandtabs;
input insiderID  year   CompanyISIN  : $40.   tradedate : ddmmyy10.;
format tradedate  ddmmyy10.;
cards;
283990 2004 US1509348835 01/07/2004
283990 2004 US1509348835 10/12/2004
283990 2005 US1509348835 03/01/2005
283990 2005 US1509348835 05/04/2005
283990 2005 US1509348835 31/05/2005
283990 2005 US1509348835 01/07/2005
283990 2005 US1509348835 12/12/2005
283990 2006 US1509348835 03/01/2006
283990 2006 US1509348835 08/05/2006
283990 2006 US1509348835 18/09/2006
283990 2006 US1509348835 29/09/2006
283990 2006 US1509348835 14/12/2006
283990 2007 US1509348835 26/03/2007
283990 2007 US1509348835 27/03/2007
283990 2008 US1509348835 18/03/2008
283990 2008 US1509348835 19/03/2008
283990 2009 US1509348835 31/07/2009
283990 2009 US1509348835 11/09/2009
283990 2009 US1509348835 25/09/2009
283990 2009 US1509348835 22/10/2009
283990 2009 US1509348835 10/11/2009
283990 2010 US1509348835 25/03/2010
283990 2010 US1509348835 29/06/2010
283990 2010 US1509348835 30/06/2010
283990 2010 US1509348835 09/08/2010
283990 2010 US1509348835 10/08/2010
283990 2010 US1509348835 29/09/2010
283990 2010 US1509348835 30/09/2010
283990 2010 US1509348835 05/11/2010
283990 2010 US1509348835 10/12/2010
283990 2010 US1509348835 15/12/2010
283990 2010 US1509348835 16/12/2010
283990 2011 US1509348835 30/12/2010
283990 2011 US1509348835 31/12/2010
283990 2011 US1509348835 26/01/2011
283990 2011 US1509348835 27/01/2011
283990 2011 US1509348835 23/02/2011
283990 2011 US1509348835 25/03/2011
283990 2011 US1509348835 13/05/2011
283990 2011 US1509348835 29/07/2011
283990 2011 US1509348835 26/08/2011
283990 2011 US1509348835 16/09/2011
283990 2011 US1509348835 21/09/2011
283990 2011 US1509348835 30/09/2011
283990 2011 US1509348835 28/10/2011
283990 2011 US1509348835 30/11/2011
283990 2011 US1509348835 29/12/2011
283990 2012 US1509348835 27/01/2012
283990 2012 US1509348835 24/02/2012
283990 2012 US1509348835 21/03/2012
283990 2012 US1509348835 30/03/2012
283990 2012 US1509348835 27/04/2012
283990 2012 US1509348835 28/06/2012
283990 2012 US1509348835 29/06/2012
283990 2012 US1509348835 30/07/2012
283990 2012 US1509348835 10/08/2012
283990 2012 US1509348835 22/08/2012
283990 2012 US1509348835 31/08/2012
283990 2012 US1509348835 20/09/2012
283990 2012 US1509348835 26/09/2012
;
run;
proc sql;
create table option1 as
select *,
   case when (select count(distinct month(tradedate)) from have
   where insiderID=a.insiderID and
   year between a.year-2 and a.year-1 and
   month(tradedate)=month(a.tradedate ) ) gt 1 then 0
   else 1 end as irregular_trades  
  from have as a;
quit;


data x;
set have;
  month=month(tradedate);
  drop tradedate ;
run;
proc sort data=x(keep=insiderID  year month) out=temp nodupkey;
by insiderID  year month;
run;
data first_two_year;
set temp;
by insiderID  year ;
if first.insiderID then n=0;
if first.year then n+1;
if n lt 3;
drop n ;
run;
proc sql;
create table first_two_year_month as
select *
  from first_two_year
   group by insiderID,month
   having count(*) gt 1;
  
create table option2 as
select *,case when
   exists(
   select * from first_two_year_month where month=month(a.tradedate)
   ) then 0
   else 1 end as irregular_trader
  from have as a;
quit;
Regular Contributor
Posts: 183

Re: Dummy Variable

Say, I want to do the three options for this insiderID that I mentioned before.

tradedate year insiderID

11/11/2004 2004 102174

14/11/2005 2005 102174

28/11/2005 2005 102174

02/12/2005 2005 102174

20/11/2006 2006 102174

07/02/2007 2007 102174

21/05/2007 2007 102174

28/02/2008 2008 102174

01/12/2010 2010 102174

15/02/2011 2011 102174

03/03/2011 2011 102174

Output for option1 will be as following. Since this insiderID has trades in November of 2004 and 2005, so trade in November 2006 will be regular trade and hence 'irregular_trade' dummy will be 0. Now trade on 07/02/2007 is irregular trade since he does not have trades in February of both 2005 and 2006, hence 'irregular_trade' dummy will be 1. In the same way, trade on 21/05/2007 will be irregular trade since he does not have trades in May of both 2005 and 2006, hence 'irregular_trade' dummy will be 1. His trade on 28/02/2008 will also be irregular trade since he does not have trades in February of both 2006 and 2007 (has trade in February 2007 only). His trade on 01/12/2010 will also be irregular trade since he does not have trades in December of both 2008 and 2009, hence 'irregular_trade' dummy will be 1. This is rolling approach. Please note that 'irregular_trade' dummy is blank for years 2004 and 2005 since these are his first two consecutive years of trade.

tradedate year insiderID        irregular_trade

11/11/2004 2004 102174      

14/11/2005 2005 102174      

28/11/2005 2005 102174      

02/12/2005 2005 102174     

20/11/2006 2006 102174                   0

07/02/2007 2007 102174                   1

21/05/2007 2007 102174                   1

28/02/2008 2008 102174                   1

01/12/2010 2010 102174                   1

15/02/2011 2011 102174                   1

03/03/2011 2011 102174                   1

Output for option2 will be as follows. His first two years of trades are 2004 and 2005. For this two years, he has trades in November only for both 2004 and 2005. So he is identified as regular trader and hence 'irregular_trader' dummy will be 0. Since his trades in only November will be regular trades and hence 'irregular_trade' dummy will be 0 (like 21/11/2006). Since he does not have any other November trades in other years (except 2006), those will be 1. Only November trades in other years would be 0.

tradedate year insiderID  irregular_trader      irregular_trade

11/11/2004 2004 102174         0

14/11/2005 2005 102174         0

28/11/2005 2005 102174         0

02/12/2005 2005 102174         0

20/11/2006 2006 102174         0                            0

07/02/2007 2007 102174         0                            1

21/05/2007 2007 102174         0                            1

28/02/2008 2008 102174         0                            1

01/12/2010 2010 102174         0                            1

15/02/2011 2011 102174         0                            1

03/03/2011 2011 102174         0                            1

Output for option3 will be as follows. If he has trades in the same month (Novemebr) of two consecutive years (both in 2004 and 2005), he will be identified as a regular trader. So the 'irregular_trader' dummy will be 0. All of his trades in all months of all years (except 2004 and 2005) will be regular trade and hence 'irregular_trade' dummy will be 0. (If he did not have trades in any common month of both years, then he would be identified as irregular trader. 'irregular_trader' dummy would be 1 and 'irregular_trade' dummy would also be 1).

tradedate year insiderID  irregular_trader      irregular_trade

11/11/2004 2004 102174         0

14/11/2005 2005 102174         0

28/11/2005 2005 102174         0

02/12/2005 2005 102174         0

20/11/2006 2006 102174         0                            0

07/02/2007 2007 102174         0                            0

21/05/2007 2007 102174         0                            0

28/02/2008 2008 102174         0                            0

01/12/2010 2010 102174         0                            0

15/02/2011 2011 102174         0                            0

03/03/2011 2011 102174         0                            0

Now let's consider the following insiderID.

tradedate year insiderID

03/11/2006 2006 137694        

05/11/2010 2010 137694                              

10/11/2011 2011 137694                              

26/01/2012 2012 137694                              

20/11/2012 2012 137694        

For this insiderID, Option1 will look as follows. Since he does not have trades in November of both 2006 and 2007, trade on 05/11/2010 is irregular trade hence dummy is 1. Dummy is 0 for 20/11/2012 trade since he has trades on November of both 2010 and 2011.

tradedate year insiderID irregular_trade

03/11/2006 2006 137694        

05/11/2010 2010 137694           1                   

10/11/2011 2011 137694           1                   

26/01/2012 2012 137694           1                   

20/11/2012 2012 137694           0                   

Option2. No trades in the same month of first two consecutive years (i.e. 2006 and 2007). So he is identified as a irregular trader.

tradedate year insiderID irregular_trader     irregular_trade

03/11/2006 2006 137694           1

05/11/2010 2010 137694           1                      1

10/11/2011 2011 137694           1                      1

26/01/2012 2012 137694           1                      1

20/11/2012 2012 137694           1                      1

Option3 will be similar to option2 for this insiderID.

Grand Advisor
Posts: 9,584

Re: Dummy Variable

Try this one .

Code: Program

data have;
infile cards truncover expandtabs;
input tradedate : ddmmyy10. year  insiderID   ;
format tradedate  ddmmyy10.;
cards;
11/11/2004 2004 102174
14/11/2005 2005 102174
28/11/2005 2005 102174
02/12/2005 2005 102174
20/11/2006 2006 102174
07/02/2007 2007 102174
21/05/2007 2007 102174
28/02/2008 2008 102174
01/12/2010 2010 102174
15/02/2011 2011 102174
03/03/2011 2011 102174
03/11/2006 2006 137694 
05/11/2010 2010 137694 
10/11/2011 2011 137694 
26/01/2012 2012 137694 
20/11/2012 2012 137694 
;
run;


proc sql;
create table option1 as
select *,
   case   when a.year between (select min(year) from have where insiderID=a.insiderID) and
   (select min(year) from have where insiderID=a.insiderID)+1   then  .
   when (select count(distinct year) from have
   where insiderID=a.insiderID and
   year between a.year-2 and a.year-1 and
   month(tradedate)=month(a.tradedate ) ) gt 1 then 0
   else 1 end as irregular_trades  
  from have as a;
quit;


data x;
set have;
  month=month(tradedate);
  drop tradedate ;
run;
proc sort data=x(keep=insiderID  year month) out=temp nodupkey;
by insiderID  year month;
run;
data first_two_year;
set temp;
by insiderID  year ;
if first.insiderID then n=0;
if first.year then n+1;
if n lt 3;
drop n ;
run;
proc sql;
create table first_two_year_month as
select *
  from first_two_year
   group by insiderID,month
   having count(*) gt 1 and range(year)=1;
  
create table option2 as
select *,case
  when a.year between (select min(year) from have where insiderID=a.insiderID) and
  (select min(year) from have where insiderID=a.insiderID)+1   then  .
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID and month=month(a.tradedate)
   ) then 0
   else 1 end as irregular_trader
  from have as a;
 
create table option3 as
select *,case
  when a.year between (select min(year) from have where insiderID=a.insiderID) and
  (select min(year) from have where insiderID=a.insiderID)+1   then  .
  when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
   else 1 end as irregular_trader
  from have as a; 
 
 
quit;

Regular Contributor
Posts: 183

Re: Dummy Variable

Thank you Xia. You are great. I will call you as my boss.

However, the 'irregular_trader' dummy in your option2 and option3 will be 'irregular_trade'. Can you also create the 'irregular_trader' dummy as I showed in outputs?

Solution
‎06-17-2015 10:12 AM
Grand Advisor
Posts: 9,584

Re: Dummy Variable

OK. No problem.

Code: Program

data have;
infile cards truncover expandtabs;
input tradedate : ddmmyy10. year  insiderID   ;
format tradedate  ddmmyy10.;
cards;
11/11/2004 2004 102174
14/11/2005 2005 102174
28/11/2005 2005 102174
02/12/2005 2005 102174
20/11/2006 2006 102174
07/02/2007 2007 102174
21/05/2007 2007 102174
28/02/2008 2008 102174
01/12/2010 2010 102174
15/02/2011 2011 102174
03/03/2011 2011 102174
03/11/2006 2006 137694 
05/11/2010 2010 137694 
10/11/2011 2011 137694 
26/01/2012 2012 137694 
20/11/2012 2012 137694 
;
run;


proc sql;
create table option1 as
select *,
   case   when a.year between (select min(year) from have where insiderID=a.insiderID) and
   (select min(year) from have where insiderID=a.insiderID)+1   then  .
   when (select count(distinct year) from have
   where insiderID=a.insiderID and
   year between a.year-2 and a.year-1 and
   month(tradedate)=month(a.tradedate ) ) gt 1 then 0
   else 1 end as irregular_trades  
  from have as a;




create table first_two_year as
select *,month(tradedate) as month
  from have
   group by insiderID
   having year = min(year) or year=min(year)+1; 

create table first_two_year_month as
select *
  from first_two_year
   group by insiderID,month
   having count(distinct year) gt 1 ;
  
create table option2 as
select *,case
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
  else 1 end as irregular_trader, 

case
  when a.year between (select min(year) from have where insiderID=a.insiderID) and
  (select min(year) from have where insiderID=a.insiderID)+1   then  .
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID and month=month(a.tradedate)
   ) then 0
   else 1 end as irregular_trades
  from have as a;
 
create table option3 as
select *,case
when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
  else 1 end as irregular_trader,

case
  when a.year between (select min(year) from have where insiderID=a.insiderID) and
  (select min(year) from have where insiderID=a.insiderID)+1   then  .
  when
   exists(
   select * from first_two_year_month where insiderID=a.insiderID
   ) then 0
   else 1 end as irregular_trades
  from have as a; 
 
 
quit;

Xia Keshan

Regular Contributor
Posts: 183

Re: Dummy Variable

Thank you boss. I am so grateful to you.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 741 views
  • 6 likes
  • 2 in conversation