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;
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;
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;
@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
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: