Can't solve missing value error

[valeriya]

Hello, 

I was wondering if anyone could help me find a solution to this problem.

This is the code I'm trying to execute in SAS 9.3:

proc sql;
create table BE (drop=CUSIP PRC shrout) as
select a.*, b.*, b.me as size
from comp1 as a
LEFT JOIN msex2(drop=RET)as b
on a.cusip=b.cusip
where (a.fyear+1)=year(b.date)and month(b.date) = 6;
run;

And when I run it, I get:

NOTE: Invalid (or missing) arguments to the YEAR function have caused the
function to return a missing value.

NOTE: Invalid (or missing) arguments to the MONTH function have caused the
function to return a missing value.

I've checked the table, I did the summary listing all the available dates and also checked for the missing values. The DATE field in the table has no missing or invalid values. 

I just can't figure out, why it's giving my this error, 

Thank you in advance for anyone's input!

[TomKari]

That's a toughie!

All I can think of is to run these two queries (syntax unchecked).

select min(date) as mindate, max(date) as maxdate from msex2;

These shoud be roughly between the range of -137727 and 6552447. If they are wildly out of that range, they are probably datetime variables or erroneous data.

And the other, which I think you've already run, is

select count(*) from msex2 where date is missing;

Good luck!

    Tom

[valeriya]

Thank you for your reply! I didn't get a chance to try the things you listed, but I will do so.

[Reeza]

What variable type is date? And what format?

[valeriya]

Variable Date is in the date format. I checked that table separately and it's extracting month and a date without a problem. It is only problematic when I add the left join.

[Tom]

It is because you are doing a LEFT JOIN and so some of the results from the join that are passed to the WHERE clause might not have had any contribution from the right had table.  Therefore the value of the DATE variable will be missing.

Why not just add the condition to the ON clause?

proc sql;
  create table BE (drop=CUSIP PRC shrout) as
    select a.*, b.*, b.me as size
    from comp1 as a
    LEFT JOIN msex2(drop=RET)as b
    on a.cusip=b.cusip
    and (a.fyear+1)=year(b.date)and month(b.date) = 6
  ;
quit;

[valeriya]

Thank you very much for your reply. I really was suspecting there is an issue with a Left Join, I just didn't know why it's an issue. I haven't had time to check your solution, but I will do that and will let you know.

Thanks a lot!