DATA Step, Macro, Functions and more

Can't solve missing value error

Reply
New Contributor
Posts: 4

Can't solve missing value error

[ Edited ]

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!

PROC Star
Posts: 1,167

Re: Can't solve missing value error

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

New Contributor
Posts: 4

Re: Can't solve missing value error

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

Re: Can't solve missing value error

What variable type is date? And what format?

New Contributor
Posts: 4

Re: Can't solve missing value error

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.
Super User
Super User
Posts: 7,060

Re: Can't solve missing value error

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;
New Contributor
Posts: 4

Re: Can't solve missing value error

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!
Ask a Question
Discussion stats
  • 6 replies
  • 443 views
  • 2 likes
  • 4 in conversation