sas date comparison with charactor date constant in proc sql where clause

[nnl3256]

I want to select records from table aa that date1 before or same
as 06-02-2018. The date1 is saved in table aa as SAS date in
format DATETIME22.3: 02JUN2018:00:00:00.000. How can I
compare these two types of date in proc SQL where clause?

Error message when ran the codes
ERROR: Expression using less than or equal (<=) has components that are of different data types.


data aa ;
  length date1 8; 
  input  date1;
  format date1 DATETIME22.3;
cards;
1843516800
1845244800
;;
run;

proc sql noprint; create table bb as select * from aa
where date1 eq "06-02-2018"; quit;

[novinosrin · Accepted solution]

Are you after this by any chance?

data aa ;
  length date1 8; 
  input  date1;
  format date1 DATETIME22.3;
cards;
1843516800
1845244800
;;
run;

proc sql noprint; create table bb as select * from aa
where datepart(date1) eq "02JUN2018"d; quit;

[PaigeMiller]

---

@novinosrin wrote:

Are you after this by any chance?

data aa ;
  length date1 8; 
  input  date1;
  format date1 DATETIME22.3;
cards;
1843516800
1845244800
;;
run;

proc sql noprint; create table bb as select * from aa
where datepart(date1) eq "02JUN2018"d; quit;

---

I have guessed and seen some evidence (but haven't tried to prove) that if you have very large data sets/database tables, you might be better off writing this code differently so that the datepart() function doesn't have to be evaluated on every record. Something like this:

where date1='02JUN2018:00:00:00'dt

[novinosrin]

That's a good point. A couple of years ago, I accidentally did something like that in-database processing with just date without the part and that worked fine. I had a SAS L account back then where I was confirmed by somebody there is a feature in SAS ACCESS that does something like that. I can't remember the real intricacy but your point rings the bell for me to look up on that

[nnl3256]

Thank you. It wors

[nnl3256]

Yes, I do have a large data table. Do you suggest coding like:
proc sql noprint; create table bb as select * from aa
where date1 <= '02JUN2018:00:00:00'dt; quit;

[novinosrin]

Best way to find out is test both. 

See the results for accuracy and performance i.e how fast it executes

 then take your pick

SAS communities is a bufffet