BookmarkSubscribeRSS Feed
valeriya
Fluorite | Level 6

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!

6 REPLIES 6
TomKari
Onyx | Level 15

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
Fluorite | Level 6
Thank you for your reply! I didn't get a chance to try the things you listed, but I will do so.
Reeza
Super User

What variable type is date? And what format?

valeriya
Fluorite | Level 6
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
Super User Tom
Super User

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
Fluorite | Level 6
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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2606 views
  • 2 likes
  • 4 in conversation