topic Re: Compare two dates in a PROC SQL statement in SAS Programming

Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 17:19:47 GMT

Hi there,

I have a table with dates stocked in a DATE9 format.

I want to set a macro variable to extract data from a specific date.

To convert my date, I use the following code :

%let date1 = 2018-12-31;
%let datefin = %sysfunc(inputn(&date1, yymmdd10.), date9.);

When I write %put &datefin.; it works and shows 31DEC2018.

However, when I try to extract datas with a PROC SQL, I got an error.

Here's the code I'm trying to run.

%let date1 = 2018-12-31;
%let datefin = %sysfunc(inputn(&date1, yymmdd10.), date9.);

PROC SQL;
CREATE TABLE TEST AS
(SELECT *
FROM TABLESOURCE
WHERE DATE_TABLE <= &DATEFIN.);
QUIT;
RUN;

Here's the part of the log showing an error:

35 31DEC2018

___________

ERROR 22-322 : Syntax error, expecting of the following: !, !!, &,........

ERROR 76-322: Syntax error, statement will be ignored.

Any ideas where I am doing something wrong? Any help would be greatly appreciated.

Thanks,

Re: Compare two dates in a PROC SQL statement

novinosrin — Tue, 01 Oct 2019 16:29:28 GMT

Wouldn't you need

 "&DATEFIN."d



PROC SQL;

CREATE TABLE TEST AS

(SELECT *

FROM TABLESOURCE

WHERE DATE_TABLE <= "&DATEFIN."d);

QUIT;
RUN;

Re: Compare two dates in a PROC SQL statement

Reeza — Tue, 01 Oct 2019 16:29:15 GMT

You need quotes around the macro variable when it resolves and a d. Or don't apply a format to it when doing the %SYSFUNC() and it will work fine.

Re: Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 16:32:08 GMT

First, thanks for the quick reply. I tried what you proposed and it doesn't seem to work, since the output dataset is empty. 😕

Re: Compare two dates in a PROC SQL statement

ballardw — Tue, 01 Oct 2019 16:32:22 GMT

Don't format the macro variable. Just leave it as the numeric value of the date: %sysfunc(inputn(&date1, yymmdd10.));

If you use the formatted value you would have to use "&datefin."d for the comparison.

Or instead of bothering to convert yymmdd format text use

%let date1 = "31DEC2018"d;

and use: date_table le &date1.

Re: Compare two dates in a PROC SQL statement

Reeza — Tue, 01 Oct 2019 16:33:13 GMT

Show your work please. I'm certain those approaches will work.

Re: Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 16:37:11 GMT

I tried both of your solutions (@Reeza and @ballardw) and it doesn't work either. I don't understand why.

Is there a way that SAS is displaying the date in date9 format but in fact it could be coded in international format (YYYY-MM-DD) ? Is there a way to check this?

Re: Compare two dates in a PROC SQL statement

Reeza — Tue, 01 Oct 2019 16:38:28 GMT

1. Is that all your code shown? If you're using SQL Pass through for example, the rules are different.
2. Show what you tried, because otherwise this should work and if it's not it's likely because of something we don't know.

Re: Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 16:41:54 GMT

The code in my first post is exactly what I have.

I tried using

%let datefin = "31DEC2018"d;

and in the code I used :

PROC SQL;
CREATE TABLE TEST1 AS
( SELECT *
FROM TABLESOURCE
WHERE DATETABLE le &datefin.
);
QUIT;
RUN;

Still do not works. It's annoying because I try simple things and maybe it's just me :S

Re: Compare two dates in a PROC SQL statement

Reeza — Tue, 01 Oct 2019 16:49:36 GMT

What is the format and type of datetable variable?
Usually before you create a macro you make working code. What works without any macro variables? First figure that out and then convert it to a macro.

Re: Compare two dates in a PROC SQL statement

mkeintz — Tue, 01 Oct 2019 16:51:42 GMT

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.

Re: Compare two dates in a PROC SQL statement

Reeza — Tue, 01 Oct 2019 16:50:58 GMT

2018-21-31

is not a valid date as well.

Re: Compare two dates in a PROC SQL statement

PGStats — Tue, 01 Oct 2019 16:53:24 GMT

Does

PROC SQL;
CREATE TABLE TEST1 AS
SELECT *
FROM TABLESOURCE
WHERE DATETABLE le "31DEC2018"d;
QUIT;

work?

Re: Compare two dates in a PROC SQL statement

Reeza — Tue, 01 Oct 2019 16:53:24 GMT

Examples of both approaches - both work.

%let date1 = 2005-12-01;
%let datefin = %sysfunc(inputn(&date1., yymmdd10.));

proc sql;
create table test1 as
select * 
from sashelp.stocks
where date = &datefin;
quit;


%let date1 = 2005-12-01;
%let datefin = %sysfunc(inputn(&date1, yymmdd10.), date9.);

%put &datefin;

proc sql;
create table test2 as
select * 
from sashelp.stocks
where date = "&datefin."d;
quit;

@jpprovost wrote:

The code in my first post is exactly what I have.

I tried using
%let datefin = "31DEC2018"d;
and in the code I used :
PROC SQL;
CREATE TABLE TEST1 AS
( SELECT *
FROM TABLESOURCE
WHERE DATETABLE le &datefin.
);
QUIT;
RUN;
Still do not works. It's annoying because I try simple things and maybe it's just me :S

Re: Compare two dates in a PROC SQL statement

Tom — Tue, 01 Oct 2019 17:13:28 GMT

@jpprovost wrote:

The code in my first post is exactly what I have.

I tried using
%let datefin = "31DEC2018"d;
and in the code I used :
PROC SQL;
CREATE TABLE TEST1 AS
( SELECT *
FROM TABLESOURCE
WHERE DATETABLE le &datefin.
);
QUIT;
RUN;
Still do not works. It's annoying because I try simple things and maybe it's just me :S

What does that mean? Show the log. Did you get an error or wrong number of observations?

Is DATETABLE really a DATE value? Perhaps it is a DATETIME value? If it is a DATETIME value then unless it is before 6AM on January 1, 1960 then its value is probably greater than any DATE value you might compare it to.

where DATETABLE le "31DEC2018:00:00"dt;

where datepart(DATETABLE) le &datefin.

Re: Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 17:20:50 GMT

I fix the typo, thanks.

Re: Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 17:28:51 GMT

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.

Re: Compare two dates in a PROC SQL statement

jpprovost — Tue, 01 Oct 2019 17:38:05 GMT

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

I will try other thing.

Re: Compare two dates in a PROC SQL statement

mkeintz — Tue, 01 Oct 2019 17:39:10 GMT

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

Re: Compare two dates in a PROC SQL statement

Kurt_Bremser — Tue, 01 Oct 2019 17:40:19 GMT

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.