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
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;
try:
proc sql;
select date from tablex
where date =. ;/* or date=' ' if date is character variable*/
quit;
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 ?
Yes. An example:
proc sql;
select name from sashelp.class
where upcase(name) like "A%";
quit;
Name
Alfred
Alice
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;
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;
Thanks linin for your nice explanation
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;
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!
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.