topic Re: sas date comparison with charactor date constant in proc sql where clause in SAS Procedures

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

nnl3256 — Thu, 01 Nov 2018 14:53:57 GMT

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;

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

novinosrin — Thu, 01 Nov 2018 15:00:05 GMT

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;

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

PaigeMiller — Thu, 01 Nov 2018 15:15:23 GMT

@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

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

novinosrin — Thu, 01 Nov 2018 15:45:38 GMT

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

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

nnl3256 — Thu, 01 Nov 2018 15:46:15 GMT

Thank you. It wors

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

nnl3256 — Thu, 01 Nov 2018 15:50:11 GMT

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;

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

novinosrin — Thu, 01 Nov 2018 15:51:43 GMT

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