PROC SQL Left Join with multiple conditions

[sjm]

Hi All,

I am trying to match the accounting variables (cash) of firms with monetary policy announcements that occur twice a year (in April and October).

The accounting variable (cash) is in the BVAL file (excerpted below) and comprise fiscal year-end data for each firm. The firm's ID is given by GVKEY,  the cash variable is denoted CASH, the fiscal year end of the firm  is denoted FYE, and the date that corresponds to the fiscal year end  is denoted DATADATE.  These are annual data.

The monetary policy data are in the REG file (excerpted below for three firms) and comprise twice-yearly data on each firm’s reaction to the two monetary policy announcements. REG has information on the announcement month  denoted EVTDATE , the FYE, and the stock's return denoted CAR, for that announcement date.

The rule used to match the data is as follows. If the policy month is APRIL and if the value of FYE of the firm is between 1 and 3, then the correct FYE to match the APRIL observation is the current firm year's DATADATE. However, if the policy month is APRIL and the value of FYE is between 4 and 12, then the correct FYE to match the APRIL observation is the previous firm year's DATADATE.

So, for example, if the policy month is April 2010 and if the firm’s FYE is  3  (i.e. March), then the correct FYE to match the April 2010 observation is the firm’s current year's DATADATE (i.e. March 2010) .  However if the firm’s FYE is 6 (i.e. June), then the correct FYE to match the April 2010 observation is the firm’s previous year's DATADATE (i.e. June 2009). 

Similarly, when the policy month is October 2015 and the firm’s FYE is 9 (i.e. September), then the correct FYE to match the October 2015 observation is the firm’s current DATADATE (i.e. September 2015). If the firm’s FYE is  10, then the correct FYE to match the October 2015 observation is the firm’s previous year DATADATE (i.e. September 2014). 

The PROC SQL code that I have written below was able to match the data for the REG (policy information) with BVAL ( accounting information).  The problem, however, is that the code results in the original EVTDATE being replaced with the  DATADATE  from the BVAL file, creating multiple observations of EVTDATE in the FINAL dataset (see below). There should only be one EVTDATE-GVKEY observation.  

I would appreciate help with this code so that when the matching takes place, the original EVTDATE is retained with the correct values of CASH.  Parts of the two datasets and the PROC SQL code used is given below:

proc sql;

create table Final as

select REG.*

,y.cash

,y.debt

,case when reg.April=1 and fyrc=1 then year(x.evtdate)=year(y.datadate)

      when reg.April=1 and fyrc=2 then year(x.evtdate)=year(y.datadate)

      when reg.April=1 and fyrc=3 then year(x.evtdate)=year(y.datadate)

      when reg.April=1 and fyrc=4 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=5 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=6 then year(x.evtdate)=year(y.datadate)+1     

      when reg.April=1 and fyrc=7 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=8 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=9 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=10 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=11 then year(x.evtdate)=year(y.datadate)+1

      when reg.April=1 and fyrc=12 then year(x.evtdate)=year(y.datadate)+1

      when reg.October=1 and fyrc=1 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=2 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=3 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=4 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=5 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=6 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=7 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=8 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=9 then year(x.evtdate)=year(y.datadate)

      when reg.October=1 and fyrc=10 then year(x.evtdate)=year(y.datadate)+1

      when reg.October=1 and fyrc=11 then year(x.evtdate)=year(y.datadate)+1

      when reg.October=1 and fyrc=12 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=1 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=2 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=3 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=4 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=5 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=6 then year(x.evtdate)=year(y.datadate)+1     

      when reg.January=1 and fyrc=7 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=8 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=9 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=10 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=11 then year(x.evtdate)=year(y.datadate)+1

      when reg.January=1 and fyrc=12 then year(x.evtdate)=year(y.datadate)+1

      when reg.July=1 and fyrc=1 then year(x.evtdate)=year(y.datadate)

      when reg.July=1 and fyrc=2 then year(x.evtdate)=year(y.datadate)

      when reg.July=1 and fyrc=3 then year(x.evtdate)=year(y.datadate)

      when reg.July=1 and fyrc=4 then year(x.evtdate)=year(y.datadate)

      when reg.July=1 and fyrc=5 then year(x.evtdate)=year(y.datadate)

      when reg.July=1 and fyrc=6 then year(x.evtdate)=year(y.datadate)

      when reg.July=1 and fyrc=7 then year(x.evtdate)=year(y.datadate)+1

      when reg.July=1 and fyrc=8 then year(x.evtdate)=year(y.datadate)+1

      when reg.July=1 and fyrc=9 then year(x.evtdate)=year(y.datadate)+1

      when reg.July=1 and fyrc=10 then year(x.evtdate)=year(y.datadate)+1

      when reg.July=1 and fyrc=11 then year(x.evtdate)=year(y.datadate)+1

      when reg.July=1 and fyrc=12 then year(x.evtdate)=year(y.datadate)+1

      else year(x.evtdate)=.

      end as fiscaldate                 

      from REG as X

                    left join

                     BVAL as Y

       on x.GVKEY=y.GVKEY;

quit;

data Final;

set Final;

       if fiscaldate=1;

keep gvkey firmname evtdate fyrc car1 shock gdp inf cash debt;

run;

PART OF THE BVAL FILE:

GVKEY	DATADATE	FYE	CASH
018472	31/3/2005	3	0.121862
018472	31/3/2006	3	0.302073
018472	31/3/2007	3	0.332595
018472	31/3/2008	3	0.389622
018472	30/6/2009	6	0.427514
018472	30/6/2010	6	0.354008
018472	30/6/2011	6	0.307319
018472	30/6/2012	6	0.484339
018472	30/6/2013	6	0.249954
018472	30/6/2014	6	0.436894
018472	30/6/2015	6	0.269154
018472	30/6/2016	6	0.251688
018472	30/6/2017	6	0.233228
018472	30/6/2018	6	0.100979
018472	30/6/2019	6	0.089995
019774	31/12/2005	12	0.168226
019774	31/12/2006	12	0.154627
019774	31/12/2007	12	0.148806
019774	31/12/2008	12	0.283562
019774	31/12/2009	12	0.278393
019774	31/12/2010	12	0.320237
019774	31/12/2011	12	0.254877
019774	31/12/2012	12	0.159862
019774	31/12/2013	12	0.164017
019774	31/12/2014	12	0.096611
019774	31/12/2015	12	0.080564
019774	31/12/2016	12	0.083207
019774	31/12/2017	12	0.115515
019774	31/12/2018	12	0.082458
019774	31/12/2019	12	0.074832
019799	31/12/2005	12	0.028588
019799	31/12/2006	12	0.027229
019799	31/12/2007	12	0.065472
019799	31/12/2008	12	0.04328
019799	31/12/2009	12	0.03759
019799	31/12/2010	12	0.03869
019799	31/12/2011	12	0.034154
019799	31/12/2012	12	0.026654
019799	31/12/2013	12	0.027635
019799	31/12/2014	12	0.023704
019799	31/12/2015	12	0.023697
019799	31/12/2016	12	0.026069
019799	31/12/2017	12	0.041606
019799	31/12/2018	12	0.032589
019799	31/12/2019	12	0.034588
025773	30/6/2005	6	0.173782
025773	30/6/2006	6	0.257635
025773	30/6/2007	6	0.34643
025773	30/6/2008	6	0.544599
025773	30/6/2009	6	0.551362
025773	30/6/2010	6	0.531672
025773	30/6/2011	6	0.476478
025773	30/6/2012	6	0.560992
025773	30/6/2013	6	0.670615
025773	30/6/2014	6	0.641849
025773	30/6/2015	6	0.625517
025773	30/6/2016	6	0.666338
025773	30/6/2017	6	0.662455
025773	30/6/2018	6	0.784832
025773	30/6/2019	6	0.782643

PART OF THE REG FILE:

GVKEY	EVTDATE	CAR	FYE
018472	11/4/2006	0.015483	12
018472	10/10/2006	0.0035	12
018472	10/4/2007	-0.01148	12
018472	10/10/2007	-0.02411	12
018472	10/4/2008	-0.00548	12
018472	10/10/2008	-0.0446	12
018472	14/4/2009	0.01682	12
018472	12/10/2009	0.012073	12
018472	14/4/2010	0.017708	12
018472	14/10/2010	-0.00912	12
018472	14/4/2011	0.012445	12
018472	14/10/2011	0.029151	12
018472	13/4/2012	0.003448	12
018472	12/10/2012	-0.0176	12
018472	12/4/2013	-0.00878	12
019774	14/10/2013	-0.00907	12
019774	14/4/2014	0.008597	12
019774	14/10/2014	-0.00018	12
019774	28/1/2015	0.004851	12
019774	14/4/2015	0.021583	12
019774	14/10/2015	0.020571	12
019774	14/4/2016	-0.01592	12
019774	14/10/2016	-0.00454	12
019774	13/4/2017	0.000865	12
019774	13/10/2017	0.017432	12
019774	13/4/2018	0.005659	12
019774	12/10/2018	0.027236	12
019774	12/4/2019	0.000105	12
019774	11/4/2006	-0.00319	12
019774	10/10/2006	0.024586	12
019799	10/4/2007	0.005828	12
019799	10/10/2007	-0.02128	12
019799	10/4/2008	0.004028	12
019799	10/10/2008	-0.05432	12
019799	14/4/2009	0.041943	12
019799	12/10/2009	0.001908	12
019799	14/4/2010	0.019772	12
019799	14/10/2010	-0.00011	12
019799	14/4/2011	0.009352	12
019799	14/10/2011	0.008611	12
019799	13/4/2012	0.002158	12
019799	12/10/2012	0.008137	12
019799	12/4/2013	-0.00178	12
019799	14/10/2013	-0.00567	12
019799	14/4/2014	-0.00049	12
025773	14/10/2014	-8.3E-05	12
025773	28/1/2015	0.030357	12
025773	14/4/2015	0.022399	12
025773	14/10/2015	0.009413	12
025773	14/4/2016	0.003972	12
025773	14/10/2016	0.015067	12
025773	13/4/2017	0.008711	12
025773	13/10/2017	0.022056	12
025773	13/4/2018	0.015204	12
025773	12/10/2018	0.019765	12
025773	12/4/2019	0.001196	12
025773	11/4/2006	-0.02718	6
025773	10/10/2006	-0.01089	6
025773	10/4/2007	-0.00118	6
025773	10/10/2007	0.013714	6
GVKEY	10/4/2008	-0.00119	6
018472	10/10/2008	0.002755	6
018472	14/4/2009	0.016955	6
018472	12/10/2009	-0.00936	6
018472	14/4/2010	0.031941	6
018472	14/10/2010	-0.01707	6
018472	14/4/2011	-0.00863	6
018472	14/10/2011	0.014206	6
018472	13/4/2012	-0.00669	6
018472	12/10/2012	0.003216	6
018472	12/4/2013	-0.01238	6
018472	14/10/2013	-0.029	6
018472	14/4/2014	-0.02549	6
018472	14/10/2014	0.004225	6
018472	28/1/2015	0.000702	6
018472	14/4/2015	0.017486	6
019774	14/10/2015	0.002553	6
019774	14/4/2016	-0.00529	6
019774	14/10/2016	-0.013	6
019774	13/4/2017	-0.0326	6
019774	13/10/2017	0.021136	6
019774	13/4/2018	-0.02808	6
019774	12/10/2018	0.017178	6
019774	12/4/2019	-0.01986	6

[Shmuel]

You can shorten your conditions code by:

case
  when reg.April=1 and  1 le fyrc le 3 then year(x.evtdate)=year(y.datadate)
  when reg.April=1 and  4 le fyrc le 12 then year(x.evtdate)=year(y.datadate)+1
  when reg.October=1 and 1 le fyrc le 9  then year(x.evtdate)=year(y.datadate)
  when reg.October=1 and 10 le fyrc le 12  then year(x.evtdate)=year(y.datadate)+1
  when reg.January=1  then year(x.evtdate)=year(y.datadate)+1
  when reg.July=1 and 1 le fyrc le 6 then year(x.evtdate)=year(y.datadate)
  when reg.July=1 and 7 le fyrc le 12 then year(x.evtdate)=year(y.datadate)+1 
  else year(x.evdate)=.
  end as fiscaldate
from REG as x left join 
     BVAL as y
   on x.GVKEY = y.GVKEY;
quit;

  you defined the problem as:

    "The problem, however, is that the code results in the

     original EVTDATE being   replaced with the  DATADATE  from the BVAL file"   

   this is then result of:

      case <condition> then year(x.evtdate)=year(y.datadate) 

  I think you meant/need to replace the "then" in all those lines to "and" ?

  when reg.April=1 and  1 le fyrc le 3 and year(x.evtdate)=year(y.datadate)
  when reg.April=1 and  4 le fyrc le 12 and year(x.evtdate)=year(y.datadate)+1
  when reg.October=1 and 1 le fyrc le 9  and year(x.evtdate)=year(y.datadate)
  when reg.October=1 and 10 le fyrc le 12  and year(x.evtdate)=year(y.datadate)+1
  when reg.January=1  and year(x.evtdate)=year(y.datadate)+1
  when reg.July=1 and 1 le fyrc le 6 and year(x.evtdate)=year(y.datadate)
  when reg.July=1 and 7 le fyrc le 12 and year(x.evtdate)=year(y.datadate)+1 

[sjm]

Thanks. I see how using "then" could cause this issue. I tried replacing "then" with "and" but got the following synthax error:

Would appreciate your advice on how to correct this.  Thanks again!

[sjm]

Also, thanks for your suggestion on how to shorten the condition. Making the changes now.

[sjm]

Below is the shortened code with the syntax error.  

[Shmuel]

picture is not readable. please post using the  icon.

beyond, the correct syntax of using WHEN is

   WHEN <any condition> THEN <do something>

and in your case did you mean THEN fiscalyear=1; ?

[sjm]

Dear Shmuel,

I hope this is screenshot and the code is clearer. Thanks for your help.

proc sql;
	create table mergefunda as
	select reg.*
		   ,y.cash
		   ,y.lev
		   ,case when reg.April=1 and 1 le fyrc 3 and year(x.evtdate)=year(y.datadate)
				 when reg.April=1 and 4 le fyrc 12 and year(x.evtdate)=year(y.datadate)+1
				 when reg.October=1 and 1 le fyrc 9 and year(x.evtdate)=year(y.datadate)
				 when reg.October=1 and 10 le fyrc 12 and year(x.evtdate)=year(y.datadate)+1
				 when reg.January=1 and year(x.evtdate)=year(y.datadate)+1
				 when reg.July=1 and 1 le fyrc 6 and year(x.evtdate)=year(y.datadate)
				 when reg.July=1 and 7 le fyrc 12 and year(x.evtdate)=year(y.datadate)+1
				 else year(x.evtdate)=.
			end as fiscaldate			
	from reg as X
			left join
		 	BVal4 as Y
	on x.gvkey=y.gvkey;
quit; 

Log File

[Shmuel]

I'm reading again your post and I'm not sure I understand what you need:

all your WHEN statements were originally in the form of 

     when <condition> then year(x.evtdate)= {year(y.datadate) or year(y.datadate)+1}

and this was the reason to EVTDATE being replaced with DATADATE.

Did you mean that in some cases the matching (i.e. - comparing) should be

year(x.evtdate)=year(y.datadate)  and in other cases

year(x.evtdate)=year(y.datadate)+1 ?

then you need replace the WHEN into AND but still need supply what to do in each case.

Are you trying to assign a value to a new variable called  fiscaldate ?

if positive then try:

 when reg.April=1 and  1 le fyrc le 3 and year(x.evtdate)=year(y.datadate) then <value>
  when reg.April=1 and  4 le fyrc le 12 and year(x.evtdate)=year(y.datadate)+1 then <value>
  when reg.October=1 and 1 le fyrc le 9  and year(x.evtdate)=year(y.datadate)  then <value>
  when reg.October=1 and 10 le fyrc le 12  and year(x.evtdate)=year(y.datadate)+1  then <value>
  when reg.January=1  and year(x.evtdate)=year(y.datadate)+1  then <value>
  when reg.July=1 and 1 le fyrc le 6 and year(x.evtdate)=year(y.datadate)  then <value>
  when reg.July=1 and 7 le fyrc le 12 and year(x.evtdate)=year(y.datadate)+1  then <value>
else <.>
end as fiscaldate

replace each <value> with the name of the appropriate variable or expression.

[sjm]

I actually want to merge the cash and debt variables from the BVAL file with the correct EVTDATE  in the REG file, when the FYRC condition is met.

So, for FIRM 1, if EVTDATE is (11 APRIL 2010) and if Firm 1's FYRC is 3, then I want to merge values of CASH and DEBT (from BVAL) with the same year as  EVTDATE i.e. 2010 to the EVTDATE  of 11 April 2010.  These are the values of CASH and DEBT with a DATADATE of 2010 in the BVAL file. 

However, for another firm that has an FYRC of 9 on 11 APRIL 2010, I need to merge values of CASH and DEBT (from BVAL) from the previous year (i.e. 2009) instead. These are the values of CASH and DEBT with a DATADATE of 2009 in the BVAL file.

I do not need to assign a value to a new variable "Fiscaldate".  I thought it was necessary as part of CASE to end with an "END AS..." statement.   

Sorry for the confusion. I hope this is clearer. Thank you for your kind help!

[Shmuel]

That makes life much easier.

Do it in two steps then concatenate the results:

1) where years are equel

2) where years are not equal

proc sql;
create table temp1  as
   select REG.*
           ,y.cash
           ,y.debt
from REG as X    left join   BVAL as Y
  on x.GVKEY=y.GVKEY and 
    ( reg.April=1 and  1 le fyrc le 3 and year(x.evtdate)=year(y.datadate)
     or reg.October=1 and 1 le fyrc le 9  and year(x.evtdate)=year(y.datadate)
     or reg.July=1 and 1 le fyrc le 6 and year(x.evtdate)=year(y.datadate)
    );
create table temp2  as
   select REG.*
           ,y.cash
           ,y.debt
from REG as X    left join   BVAL as Y
 on x.GVKEY=y.GVKEY and 
    (reg.April=1 and  4 le fyrc le 12 and year(x.evtdate)=year(y.datadate)+1
      or reg.October=1 and 10 le fyrc le 12  and year(x.evtdate)=year(y.datadate)+1
      or  reg.January=1  and year(x.evtdate)=year(y.datadate)+1
      or reg.July=1 and 7 le fyrc le 12 and year(x.evtdate)=year(y.datadate)+1 
     );
quit;
data final;
  set temp1 temp2;
run;

[ChrisNZ]

Similar but more readable to me:

		   ,case when reg.APRIL=1 and FYRC between 1 and 3  and year(x.EVTDATE)=year(y.DATADATE)
				 when reg.APRIL=1 and FYRC between 4 and 12 and year(x.EVTDATE)=year(y.DATADATE)+1

[sjm]

Dear ChrisNZ,

Thanks very much for your suggestion!

[Shmuel · Accepted solution]

By the way, you can consolidate the two steps and @ChrisNZ 's syntax suggestion into one step:

proc sql;
create table temp1  as
   select REG.*
        ,y.cash
        ,y.debt
from REG as X    left join   BVAL as Y
  on x.GVKEY=y.GVKEY and 
    (   reg.April=1 and  fyrc between 1 and 3 and year(x.evtdate) = year(y.datadate)
     or reg.October=1 and fyrc between 1 and 9  and year(x.evtdate) = year(y.datadate)
     or reg.July=1 and fyrc between 1 and 6 and year(x.evtdate) = year(y.datadate) 
     or reg.April=1 and fyrc between 4 and 12 and year(x.evtdate) = year(y.datadate)+1
     or reg.October=1 and fyrc between 10 and12  and year(x.evtdate) = year(y.datadate)+1
     or reg.January=1  and year(x.evtdate )= year(y.datadate)+1
     or reg.July=1 and fyrc between 7 and 12 and year(x.evtdate) = year(y.datadate)+1 
   );
quit;

[sjm]

Dear Shmuel,

The updated code that you sent, works perfectly!  Thanks very much.

Really appreciate all your help!