Microsoft Word Space Above a Horizontal Line

It seems a more annoying change to MS Word 2010 is the lack of ease with which one could drop in a horizontal line and move it around.  You COULD simply add a shape through Insert -> Shapes -> Line, but I find that to be more work.

To add a horizontal line now you use the Borders menu.

The problem being that when you do add the line, there is a huge gap between the previous text and the line created.

The only solutions I found online were work-arounds like adding a shape or using a paragraph border.  I HATE work-arounds like that.  It doesn't solve the problem, it just uses something else.  I want to know how to move that line up!

So, the real solution is to select the line, go into the Font menu, click the Advanced tab, and change the Position from Normal to Raised.  Then, change the number of Points to raise the line by.  At a 26 pt font I chose 18 pts.

Voila!

Now you can adjust the font size and the raised size of the line to get exactly the spacing you want.

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!