DATA Step, Macro, Functions and more

Missing / Null Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Missing / Null Values

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!


Accepted Solutions
Solution
‎12-12-2016 11:55 AM
Contributor
Posts: 42

Re: Missing / Null Values

I would try SAS function missing(date).

View solution in original post


All Replies
Solution
‎12-12-2016 11:55 AM
Contributor
Posts: 42

Re: Missing / Null Values

I would try SAS function missing(date).

Trusted Advisor
Posts: 1,360

Re: Missing / Null Values

beyond missing function you can use date =. 

 

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

Occasional Contributor
Posts: 15

Re: Missing / Null Values

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.

Super User
Posts: 10,466

Re: Missing / Null Values


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.

Occasional Contributor
Posts: 15

Re: Missing / Null Values

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.

Occasional Contributor
Posts: 15

Re: Missing / Null Values

Thanks so much!  That worked perfectly.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 506 views
  • 0 likes
  • 4 in conversation