topic PROC SQL greater than not working in SAS Programming

PROC SQL greater than not working

jbrau123 — Thu, 21 Jul 2022 15:49:19 GMT

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

Re: PROC SQL greater than not working

jbrau123 — Thu, 21 Jul 2022 16:00:45 GMT

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

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

Re: PROC SQL greater than not working

Reeza — Thu, 21 Jul 2022 16:06:20 GMT

@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/ta-p/424354

Re: PROC SQL greater than not working

jbrau123 — Thu, 21 Jul 2022 16:11:27 GMT

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

Re: PROC SQL greater than not working

ballardw — Thu, 21 Jul 2022 16:14:16 GMT

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;