DATA Step, Macro, Functions and more

Retrieve null date from table

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Retrieve null date from table

Hello !!

I want to retrieve null date from a table but getting below error.

Date retrieval will be null or in '00-___-0000' format (dd-mon-yyyy)

proc sql;

select date from tablex

where (date is null or date like '00-___-0000') ;

quit;

Error in log---Invalid date/time/datetime constant '00-___-0000'd.

Can anyone help me.

Advance thanks


Accepted Solutions
Solution
‎12-12-2012 11:28 AM
Super User
Super User
Posts: 7,076

Re: Retrieve null date from table

Are you referencing the Oracle table from SAS using implicit pass through?  For example by using a libname with the ORACLE engine.

If so then SAS will have already converted the invalid dates that you have in your date variable in Oracle into missing values.  You will not be able to tell the difference between a value that Oracle considers null and one in that strange date format with zeros both will stored as SAS missing values.

libname mylib oracle .... ;

proc sql ;

  create table nodates as

    select * from mylib.tablex

    where date is missing

  ;

quit;

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Retrieve null date from table

try:

proc sql;

select date from tablex

where date =. ;/*  or date=' ' if date is character variable*/

quit;

Contributor
Posts: 27

Re: Retrieve null date from table

Thanks Linin.

In the Oracle, when retrieving date with dd-mon-yyyy format (00-000-0000), we are writing using like operator --> date like '00-___-0000'.

Can we use like operator in SAS ?

Super Contributor
Posts: 1,636

Re: Retrieve null date from table

Yes. An example:

proc sql;

select name from sashelp.class

where upcase(name) like "A%";

quit;

                                              Name

                                           

                                             Alfred

                                             Alice

Contributor
Posts: 27

Re: Retrieve null date from table

Hello Linin,

If the dataset are like below, I want to get pid (012 and 013).

data x;

input  pid  dob : ddmmyy10. ;

cards;

011 23-jan-2012

012 00-000-0000

013 .

014 11-mar-2011

;

proc sql;

create table y as

select * from x

where dob=.;

quit;

Super Contributor
Posts: 1,636

Re: Retrieve null date from table

Hi,

try the code below:

data x;
input  pid $3.  dob $12. ;
cards;
011 23-jan-2012
012 00-000-0000
013 .
014 11-mar-2011
;
data temp;
  set x;
  newdob=input(dob,??date11.);
  format newdob date9.;

proc sql;
create table y as
select * from temp
where newdob is missing;
quit;
proc print data=y;run;

Contributor
Posts: 27

Re: Retrieve null date from table

Thanks linin for your nice explanation

Solution
‎12-12-2012 11:28 AM
Super User
Super User
Posts: 7,076

Re: Retrieve null date from table

Are you referencing the Oracle table from SAS using implicit pass through?  For example by using a libname with the ORACLE engine.

If so then SAS will have already converted the invalid dates that you have in your date variable in Oracle into missing values.  You will not be able to tell the difference between a value that Oracle considers null and one in that strange date format with zeros both will stored as SAS missing values.

libname mylib oracle .... ;

proc sql ;

  create table nodates as

    select * from mylib.tablex

    where date is missing

  ;

quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 3584 views
  • 3 likes
  • 3 in conversation