Compare two dates in a PROC SQL statement

[jpprovost]

The type is DATE and the format is DATE9.

I really think that the DATE9 format is not liked by PROC SQL... Is there a way to diagnose this out?

I don't know where I can make an error.

The dataset contains a DATE value in DATE9 format.

I use a criteria which is exactly in a DATE9 format.

I compare those two together and it doesn't work.

I will continue seeking for a solution.

[mkeintz]

It doesn't matter to SQL (or any SAS DATA step or proc) what the format of a date variable is, because the internal numeric value of the variable is the same regardless of format.   You problem is apparently how to use a macrovar to express a date literal value for use in PROC SQL.  That is why I recommend:

%let date1=31DEC2018;

proc sql ....;

   ... where date_table <="&date1"d;

regards,

Mark

[Tom]

So first see what values you actually have in the data.

For example you could pull out the unformatted min and max values and compare them to today's date as a DATE (number of days) and a DATETIME (number of seconds).

proc sql noprint;
select min(datetable) format=best32.
    , max(datetable) format=best32.
  into :min_date trimmed , :max_date trimmed
from tablesource
;
quit;
%put Date range = &min_date to &max_date ;
%put DATE     = %sysfunc(today());
%put DATETIME = %sysfunc(datetime());

[jpprovost]

---

@Tom wrote:

So first see what values you actually have in the data.

For example you could pull out the unformatted min and max values and compare them to today's date as a DATE (number of days) and a DATETIME (number of seconds).

proc sql noprint;
select min(datetable) format=best32.
    , max(datetable) format=best32.
  into :min_date trimmed , :max_date trimmed
from tablesource
;
quit;
%put Date range = &min_date to &max_date ;
%put DATE     = %sysfunc(today());
%put DATETIME = %sysfunc(datetime());

---

I tried it and here's the output from the log :

Date range = 21556 to 21822
DATE = 21823
DATETIME = 1885557787.48419

Sorry for the delay, it took 10 minutes to compute this.

[Tom · Accepted solution]

So it sounds like your original code is working.  You do NOT have any date values that are from before 2019.

43    data test;
44     input date;
45     put date= date9. +1 date= comma7.;
46    cards;

date=07JAN2019  date=21,556
date=30SEP2019  date=21,822

[jpprovost]

Everything I tried in this thread would work if this was not the fact that the date I'm looking for was not into the dataset. I will learn from it (aka maxim #3) for sure.

Thanks everyone. You are a great community for sure.

[Kurt_Bremser]

Maxim 3: Know Your Data.

Run proc contents on tablesource and look at the type and format of date_table. From that you can infer its content.

[jpprovost]

---

@Kurt_Bremser wrote:

Maxim 3: Know Your Data.

Run proc contents on tablesource and look at the type and format of date_table. From that you can infer its content.

---

Hi @Kurt_Bremser,

Thanks for the reply and help. I try my best to "know my data", trust me 😉

However, I run a PROC CONTENTS on my dataset and the date is stocked as :

Type = Num

Len = 8

Format = DATE9.

Informat = DATE9.

Thanks,

[Kurt_Bremser]

---

@jpprovost wrote:

---

@Kurt_Bremser wrote:

Maxim 3: Know Your Data.

Run proc contents on tablesource and look at the type and format of date_table. From that you can infer its content.

---

Hi @Kurt_Bremser,

Thanks for the reply and help. I try my best to "know my data", trust me 😉

However, I run a PROC CONTENTS on my dataset and the date is stocked as :

Type = Num

Len = 8

Format = DATE9.

Informat = DATE9.

Thanks,

---

So the next step of "Know Your Data" is to get a grip of the contents, which you did by running @Tom's code, and that gave you the answer. Characterization of data is important when one runs into such issues.

[jpprovost]

I totally agree with you. Thank you for your time and help. Since I'm relatively new with SAS, I'm very grateful that such a great community like this exist.

Thanks again!

[mkeintz]

I would recommend

%let date1=31dec2019; 

and inside your program just use:

WHERE DATE_TABLE <= "&date1"d ;

Using a macro ready-made for date-literal usage, you have no need for %sysfunc.

[jpprovost]

Still do not works. I think we have a problem with the field in our dataset.

I will try other thing.

[Reeza]

---

@jpprovost wrote:

Still do not works. I think we have a problem with the field in our dataset.

I will try other thing.

---

My money is on your variable being a datetime not a date variable. If that's the case, change your WHERE condition to use DATEPART()

where datepart(dateVar) = macroVariableWhatever