Retrieve null date from table

[dash]

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

[Linlin]

try:

proc sql;

select date from tablex

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

quit;

[dash]

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 ?

[Linlin]

Yes. An example:

proc sql;

select name from sashelp.class

where upcase(name) like "A%";

quit;

                                              Name

                                             Alfred

                                             Alice

[dash]

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;

[Linlin]

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;

[dash]

Thanks linin for your nice explanation

[Tom · Accepted solution]

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;