Missing / Null Values

[Coop]

Hello all. I am using SAS EG 7.1.  I am writing a simple query with different ID numbers but I am receiving unexpected results.

Query 1: proc sql; select * from work.hs where id = 1 and date is null; quit;

Query 2: proc sql; select * from work.hs where id = 2 and date is null; quit;

One of these queries produces the correct results (returns all columns and rows) while the other query returns no results. When verifying data in work.hs, both IDs exist and each ID has a date field that is missing (has a dot). The date column is an actual date and not a string. I tried different things to produce the correct results for both queries. The following change to Query 1 worked but I have no idea why. Again, the date field in work.hs has a dot for each ID...they look exactly the same.

Query 1: proc sql; select * from work.hs where id = 1 and date < 0; quit;

Query 2: proc sql; select * from work.hs where id = 2 and date is null; quit;

I know that SAS has different classifications for missing numeric data but wouldn't I be able to see the difference when looking at the table? In other words, a (.) is a (.), right? Any thoughts? Thanks for your help!

[chrej5am · Accepted solution]

I would try SAS function missing(date).

[Shmuel]

beyond missing function you can use date =. 

NULL  is not a saved varaible to be used as you have done

[Coop]

Thank you for your reply Shmuel.  Unfortunately, the "date =. " syntax produced the same results as my original queries.  Using the missing() function did produce the correct results, however.

[ballardw]

---

@Coop wrote:

Thank you for your reply Shmuel.  Unfortunately, the "date =. " syntax produced the same results as my original queries.  Using the missing() function did produce the correct results, however.

---

The . for missing is for numeric variables. So that indicates that your date may well be character. You could what: if date = ' '  yields.

Character dates are notoriously problematic to work with other than to print, so most of us here would expect date, time or datetime variables to be numeric SAS date, time or datateme values by default.

Missing() is one of the functions that does work with both numeric and character variables which is likely why it worked.

[Coop]

Thank you for your help ballardw.  The date variable is definitely a date and not character data type.  The missing data in this column is indicated by a (.) when I view the table work.hs, which is notation for missing numeric data.  Also, in my original post I noted that the query that correctly pulled the data used "date < 0" and I think this would have caused an error if the column contained character data.  As a final check, I used the DESCRIBE statement in PROC SQL to verify that the column is numeric.

I am happy that missing(date) worked but I still don't understand why.  The missing(date) syntax yields different results than "date is missing" or "date is null".  I am querying an Oracle database so I wonder if it has something to do with the way SAS and Oracle see missing data; maybe they don't see missing data the same way.

[Coop]

Thanks so much!  That worked perfectly.