BookmarkSubscribeRSS Feed
JJJJ
Calcite | Level 5
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?
4 REPLIES 4
Patrick
Opal | Level 21
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Tourmaline | Level 20
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
Patrick
Opal | Level 21
> 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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1386 views
  • 0 likes
  • 4 in conversation