Compare two dates in a PROC SQL statement

[jpprovost]

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,

[novinosrin]

Wouldn't you need

 "&DATEFIN."d

PROC SQL;

CREATE TABLE TEST AS

(SELECT *

FROM TABLESOURCE

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

QUIT;
RUN;

[jpprovost]

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

[Reeza]

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

[Reeza]

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.

[ballardw]

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.

[jpprovost]

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?

[Reeza]

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.

[jpprovost]

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

[Reeza]

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.

[Reeza]

2018-21-31

is not a valid date as well. 

[jpprovost]

I fix the typo, thanks. 

[PGStats]

Does

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

work?

[Reeza]

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

---

[Tom]

---

@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;

or

where datepart(DATETABLE) le &datefin.