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
SQL joins (and data step merges) work with raw values, not the formatted ones. Use the year() function to extract the year.
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?
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.
SQL joins (and data step merges) work with raw values, not the formatted ones. Use the year() function to extract the year.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.