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: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 26260 views
  • 1 like
  • 4 in conversation