SPSS Macro Variables in SQL

I'm back to SPSS instead of SAS, and this was driving me crazy.  In SPSS 20, I was trying to pass a macro variable into my SQL so I could change the SQL each time I called the macro.  It would simply look like this:

Start getbydate macro with myvar (to be assigned when the macro was called)

Select *
from thistable
where date < myvar

End macro

run getbydate myvar = 2013-03-01 00:00:00

That's obviously not proper SPSS syntax, but it gives the general idea of how using macro variables in SQL should work.

Here's the actual SQL, a little changed.


GET DATA
   /TYPE=ODBC
   /CONNECT= Connect info goes here
   /SQL="SELECT * "+
   "FROM stage_history "+
   "Where stage_dte < '2013-03-01 00:00:00' and term = 'Fall' "+
   /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME PointInTime.

Pretty simple.  I want to grab data from the stage_history table for any date before March 1, 2013.  But, I wanted to be able to change that date easily and reuse it throughout code.  So, I should be able to write a macro and assign a variable called 'startdate' and then replace the date in the SQL with !startdate.

The thing is, the SQL has a problem parsing this.  If you put !startdate in single quotes like this:

   "Where stage_dte < '!startdate' and term = 'Fall' "+

If you do that, SPSS thinks you're looking for the text !startdate.  That's no good.

So, you need to start by escaping the SQL command using double quotes around !startdate.

   "Where stage_dte < "!startdate" and term = 'Fall' "+

Now, we have the problem that we need single quotes to actually be part of the SQL.  This is where the !Quote() function comes in. It will place single quotes around the macro variable !startdate.

   "Where stage_dte < "!quote(!startdate)" and term = 'Fall' "+

Now the SQL is correct.  You just have to be sure when calling the macro you place the !startdate value in single quotes (see below).  Otherwise, you'll get an error about crossing numeric and string variables.

Here is the finished product.

DEFINE !getpit (startdate = !tokens(1)).


GET DATA
   /TYPE=ODBC
   /CONNECT= Connect info goes here
   /SQL="SELECT * "+
   "FROM stage_history "+
   "Where stage_dte < "!quote(!startdate)" and term = 'Fall' "+
   /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
DATASET NAME PointInTime.

!ENDDEFINE.

!getpit startdate = '2012-03-01 00:00:00'.

I hope that helps some others out there!