Proc sql with date condition

[JJJJ]

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?

[Patrick]

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;

[sbb]

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.

[LinusH]

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

[Patrick]

> 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.