topic Re: Proc sql with date condition in SAS Procedures

Proc sql with date condition

JJJJ — Sat, 01 Nov 2008 22:11:40 GMT

I usually pull data from data warehouse with the date condition like this....
Proc sql,
.
.
.
where file_date='2005-05-31';

Now I'm trying to repeated calling the sql with different date by using a macro and passing the file_date, what format of the date should I use? Should I create a character string like '2005-06-30' or a SAS date format like '30Jun05'd?

Re: Proc sql with date condition

Patrick — Sun, 02 Nov 2008 04:18:57 GMT

The macro variable will resolve to a string - Proc SQL will see this string.
Therefore pass exactly the needed string to the macro variable as if you would write it directly in the SQL statement.

%let mydate='2005-05-31';
Proc sql,
.
.
.
where file_date=&mydate;

Re: Proc sql with date condition

sbb — Sun, 02 Nov 2008 15:07:00 GMT

Why burden the caller with having to code quote marks? Suggest you remove them from the macro variable assignment and code them in the WHERE. Also, the SAS data variable's format will determine whether the WHERE statement must specify a character-string or a SAS date literal. Lastly, the INPUT function can also be used in the WHERE, rather than forcing the caller to use SAS date-literal format of ddmmmyyyy.

Scott Barry
SBBWorks, Inc.

Re: Proc sql with date condition

LinusH — Mon, 03 Nov 2008 08:02:46 GMT

Just one note. Using functions in WHERE statement is not recommended due to performance issues. By using functions there will be no WHERE optimization (=no index usage). Starting from 9.2, queries using functions on constants (i.e. macro variables) can be optimized.

/Linus

Re: Proc sql with date condition

Patrick — Mon, 03 Nov 2008 08:16:37 GMT

> Why burden the caller with having to code quote marks?

Because according to the example it could be pass-through SQL - and Oracle for example doesn't like double quotes.
On the other hand the example shows very clear that the value has to be passed as character string.