topic Can't solve missing value error in SAS Programming

Can't solve missing value error

valeriya — Fri, 21 Oct 2016 22:51:10 GMT

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!

Re: Can't solve missing value error

TomKari — Sat, 22 Oct 2016 01:01:53 GMT

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

Re: Can't solve missing value error

Reeza — Sat, 22 Oct 2016 01:33:45 GMT

What variable type is date? And what format?

Re: Can't solve missing value error

Tom — Sat, 22 Oct 2016 01:59:03 GMT

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;

Re: Can't solve missing value error

valeriya — Mon, 24 Oct 2016 14:05:51 GMT

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.

Re: Can't solve missing value error

valeriya — Mon, 24 Oct 2016 14:07:12 GMT

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!

Re: Can't solve missing value error

valeriya — Mon, 24 Oct 2016 14:08:11 GMT

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