I usually pull data from data warehouse with the date condition like this....
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?
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.
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.
> 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.