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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.