BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Coop
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
chrej5am
Quartz | Level 8

I would try SAS function missing(date).

View solution in original post

6 REPLIES 6
chrej5am
Quartz | Level 8

I would try SAS function missing(date).

Shmuel
Garnet | Level 18

beyond missing function you can use date =. 

 

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

Coop
Fluorite | Level 6

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
Super User

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

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

Thanks so much!  That worked perfectly.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 26366 views
  • 1 like
  • 4 in conversation