PROC SQL Dates and Macro Variables

Here's a little quiz: What is the name of a 'column' in a database?

a) column
b) field
c) anything but
d) variable

If you answered D, go to wikipedia and find 'variable' on the SQL page. Go, I'll wait. So after wasting hours searching for variables, I learn that SAS calls columns variables, and the things that represent numbers for flexibility (in algebra you call them variables) are MACRO variables. Good luck googling help for macro variables and avoiding all the pages that discuss both database variables and macros.

I digress. Here's what I learned. The following code:

PROC SQL NOPRINT;
    SELECT permno,fdate format=mmddyy10.
    INTO :permnovar, :eventdate
    FROM libstore.sdc_events
    WHERE count = &i;

    SELECT *
    FROM libstore.crsp_dsf
    WHERE date = &eventdate;
QUIT;

Will return null. Oh the data is in there, if you replace &eventdate; by '01JAN2001'd you will get data. But SAS can't pull a date from one table and use it in the WHERE query in another table. MACRO VARIABLES just don't work that way. Duh.

So the solution? Everything is text. That's my new mantra. Obviously the solution is to use the following code:

PROC SQL NOPRINT;
    SELECT permno,fdate format=date9.
    INTO :permnovar, :eventdate
    FROM libstore.sdc_events
    WHERE count = &i;
QUIT;

%let eventdate_cleaned = "&eventdate"d;

PROC SQL NOPRINT;
    SELECT *
    FROM libstore.crsp_dsf
    WHERE date = &eventdate_cleaned;
QUIT;

The other thing to note is that mmddyy10 is NOT the format to use. Even though it comes out of SDC like that, and CRSP uses something else, PROC SQL uses date9 in its comparison. In fact '01/01/2000'd throws an error in PROC SQL. Don't ask, just accept it.

So the moral of this story is make everything a string. Always. Also use date9 in PROC SQL. And '&eventdate' doesn't work, only double quotes evaluates the macro variable. Ugh, macro variable angers me.