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!

Error - CFForm, CFDiv, ColdFusion.Navigate, AJAX, and input="button"

I was killing myself trying to figure out what was going wrong here.  I had a cfform inside a cfdiv that was refreshing the entire page instead of refreshing through AJAX.  The idea was that a button was going to be used for the submit action, which would call a js function to use cfnavigte to refresh the cfdiv.  It went something like this.

<cfdiv id="refreshdiv">
     <cfform name="refreshform">
          <input type="hidden" value="#thatvalue#" name="valuetoupdate">
          <input type="button" src="images/mybutton.jpg" onClick="refreshfunction('refreshform')" />
     </cfform>
</cfdiv>

Javascript:

function refreshfunction(form) {
coldfusion.navigate(http://....../update.cfm,'refreshdiv',null,null,Post,form);
};

In any other case, that would submit the form and cause an AJAX refresh of just the div leaving any content before or after unaffected.  The example though refreshes the entire page.

I finally pieced this apart to realize that type="button" is the culprit.  I switched to a regular img tag instead of the input tag and it worked fine.  Maybe that's common knowledge, but I had no idea.  What a PITA.

SAS Error "Width specified for format F is invalid."

I came across the SAS error "ERROR: Width specified for format F is invalid." As that provides absolutely no hint as to what the actual problem is, it took me forever to figure it out.

What I've determined is it's a result of importing a file using proc import. Apparently there is some variable SAS doesn't like in that file (if you know exactly why, I'm all ears).

The solution is to write a "format _all_;" statement in a data step for the file before anything else is done to it. Something like this:

proc import datafile="your file" out=mydataset replace;
run;

data mydataset;
set mydataset;
format _all_;
run;

I hope it helps!

Inspiration

There are so many things I come across where the answers on the internet are either incredibly difficult to find or misleading. Sometimes the answer isn't there at all. So, here is my attempt to share some knowledge with the world. It will be random, from ColdFusion to automotive repair to photography and probably lots more, but hopefully it will be that one answer someone needs.

My inspiration: http://xkcd.com/979/