PROC SQL Merging error

[Pabster]

Hi all,

I have this code:

proc sql;
create table AltmanV3.Donnee_Avant_F as
select distinct b.*,ID
from AltmanV3.Last_Avail_Bnkrpt as a, AltmanV3.Inactive as b
where b.gvkey=a.gvkey and (b.fyear = (a.Fyear-5)  and (b.fyear <= a.Fyear));
quit;

it works perfectly. The purpose of it is to give me all the matching companies that are bankrupt at a given year (a.Fyear- x (x can be any number)).
Here is the result:

625
626  /* OPTION 1 AVEC FYEAR*/
627  proc sql;
628  create table AltmanV3.Donnee_Avant_F as
629  select distinct b.*,ID
630  from AltmanV3.Last_Avail_Bnkrpt as a, AltmanV3.Inactive as b
631  where b.gvkey=a.gvkey and ((b.fyear = (a.fyear-5) ) and (b.fyear <= a.fyear));
NOTE: Table ALTMANV3.DONNEE_AVANT_F created, with 1128 rows and 18 columns.

632  quit;

But when I do it like this:

proc sql;
create table AltmanV3.Donnee_Avant_F as
select distinct b.*,ID
from AltmanV3.Last_Avail_Bnkrpt as a, AltmanV3.Inactive as b
where b.gvkey=a.gvkey and (b.fyear = (a.DLDTE-5)  and (b.fyear <= a.DLDTE));
quit;

it just doesn`t properly. here is the log screen:

320  proc sql;
321  create table AltmanV3.Donnee_Avant_F as
322  select distinct b.*,ID
323  from AltmanV3.Last_Avail_Bnkrpt as a, AltmanV3.Inactive as b
324  where b.gvkey=a.gvkey and (b.fyear = (a.DLDTE-5)  and (b.fyear <= a.DLDTE));
NOTE: Table ALTMANV3.DONNEE_AVANT_F created, with 0 rows and 18 columns.

325  quit;

This way I am using the companies deletion year from the system to compare. 
Both DLDTE and FYEAR are in Year. format so I dont understand why or how can I make it work

[ErikLund_Jensen]

Hi @Pabster 

Are you sure that DLDTE is a year and not a date value?

[Pabster]

HI @ErikLund_Jensen . I just check and it is actually a date value.

But since I put in in the year. format,shouldn't that do the trick? or how would it be possible?

[ErikLund_Jensen]

Hi @Pabster 

I assume that your data are SAS tables, and in SAS a date value is the number of days since 01jan1960. You can put it with a format and assign these formats to the variable in the data set also, but the format does not change the value, only the way it is displayed.

You can use the SAS year-function to change the value "on the fly" in your comparison, like  xx <= year(DLDTE). This makes the comparison operator to work against the extracted year and not the date value. You could also prepare data by adding an extra variable with the year value using the same function.

[Kurt_Bremser · Accepted solution]

SQL joins (and data step merges) work with raw values, not the formatted ones. Use the year() function to extract the year.

[ErikLund_Jensen]

- as I told you earlier