Help using Base SAS procedures

Proc sql with date condition

Reply
N/A
Posts: 1

Proc sql with date condition

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?
Respected Advisor
Posts: 4,173

Re: Proc sql with date condition

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;
Super Contributor
Super Contributor
Posts: 3,174

Re: Proc sql with date condition

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.
Super User
Posts: 5,431

Re: Proc sql with date condition

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
Data never sleeps
Respected Advisor
Posts: 4,173

Re: Proc sql with date condition

> 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.
Ask a Question
Discussion stats
  • 4 replies
  • 319 views
  • 0 likes
  • 4 in conversation