PROC SQL greater than not working

[jbrau123]

I have this code that works:

proc sql;
create table DATA.DATA2 as
SELECT *
FROM data.DATA
WHERE START < '01JAN2018:00:00:00'dt
;
QUIT;

 Yet, I want greater than instead of lesser than. if I change it to greater than ">" or GT I get no output. I tried using '01JAN2018'd instead and various other twerks, but I am lost. 

Found this post that did not work: https://communities.sas.com/t5/SAS-Programming/PROC-SQL-condition-on-datetime-date/td-p/623110

My START variable has date format DATE9. and length 8.

I hope someone can help.

Kind regards,

Jacob

[jbrau123]

btw, I also tried this, which also did not work:

data DATA.PERS3;
set DATA.PERS2;
where START > '01JAN2018:00:00:00'dt;
run;

[Reeza · Accepted solution]

---

@jbrau123 wrote:

I have this code that works:

proc sql;
create table DATA.DATA2 as
SELECT *
FROM data.DATA
WHERE START < '01JAN2018:00:00:00'dt
;
QUIT;

 Yet, I want greater than instead of lesser than. if I change it to greater than ">" or GT I get no output. I tried using '01JAN2018'd instead and various other twerks, but I am lost. 

Found this post that did not work: https://communities.sas.com/t5/SAS-Programming/PROC-SQL-condition-on-datetime-date/td-p/623110

My START variable has date format DATE9. and length 8.

I hope someone can help.

Kind regards,

Jacob

---

proc sql;
create table DATA.DATA2 as
SELECT *
FROM data.DATA
WHERE START < '01JAN2018'd
;
QUIT;

proc sql;
create table DATA.DATA2 as
SELECT *
FROM data.DATA
WHERE START > '01JAN2018'd
;
QUIT;

If you have a date variable you need to be using a date comparison, you're currently using a datetime comparison. The first set of code may run, but I doubt it's correct if you examine the data. See the code above to compare to a date, not datetime.

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/...

[jbrau123]

Wonderful, thanks! I had quite a lot of trouble with that one

[ballardw]

Dates in SAS use counts of DAYs. Datetime and Time values use counts of SECONDS. So if your START is a date you are comparing to a datetime value and the units are way different.

Apply the DATEPART function to the datetime value for correct comparison with datetime values:

proc sql;
create table DATA.DATA2 as
SELECT *
FROM data.DATA
WHERE START > datepart( '01JAN2018:00:00:00'dt)
;
QUIT;

 OR provide the date directly instead of a datetime.

proc sql;
create table DATA.DATA2 as
SELECT *
FROM data.DATA
WHERE START > '01JAN2018'd
;
QUIT;