topic Retrieve null date from table in SAS Programming

Retrieve null date from table

dash — Wed, 12 Dec 2012 15:43:11 GMT

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

Re: Retrieve null date from table

Linlin — Wed, 12 Dec 2012 16:01:27 GMT

try:

proc sql;

select date from tablex

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

quit;

Re: Retrieve null date from table

dash — Wed, 12 Dec 2012 16:13:12 GMT

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 ?

Re: Retrieve null date from table

Linlin — Wed, 12 Dec 2012 16:19:02 GMT

Yes. An example:

proc sql;

select name from sashelp.class

where upcase(name) like "A%";

quit;

Name

Alfred

Alice

Re: Retrieve null date from table

Tom — Wed, 12 Dec 2012 16:28:50 GMT

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;

Re: Retrieve null date from table

dash — Wed, 12 Dec 2012 16:32:03 GMT

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;

Re: Retrieve null date from table

Linlin — Wed, 12 Dec 2012 16:57:35 GMT

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;

Re: Retrieve null date from table

dash — Wed, 12 Dec 2012 17:00:02 GMT

Thanks linin for your nice explanation