• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

Automate Excel graphs with AutoHotKey and SPSS; Automating pretty charts!

Automate Excel graphsSquishy Ball 2After 20 years of doing multivariate statistics, doing analytics is very quick and easy for me.  Putting together the report which clients will think is “pretty” and convey the results is what really takes the time!  I have a ton of macros in SPSS which greatly simplify the front-end however I’ve always preferred Excel graphs to SPSS (because they don’t look like they were drawn with crayons).

Automate Excel graphs

In the below video I demonstrate how you how you can use AutoHotKey to easily automate Excel graphs that you’ll be proud to put your name on!

AutoHotkey Bottle 3

Syntax for changing width of SPSS string variable

SPSS string variableUse Syntax to change the with of SPSS string variable

String variables in SPSS are still a pain to deal with and merging data files together with a key variable that is a string is NOT fun in SPSS.  The string variable needs to be the same width in each data file and needs to be sorted the same (ascending-descending).  I can’t tell you the number of times I’d curse the old format!

My below macro makes it a breeze to change the width of your SPSS string variables.  The process is pretty straight forward.

Years ago I established some defaults for variables that were frequently in my data sets like: email, First name, Last name, company name, Job title, Job Role, etc.  Surprisingly setting the width to wider than they are doesn’t actually take up any extra file size so, as a rule of thumb, I recommend doubling what you typically see (otherwise you’ll end up with truncated data)

Here is the macro I use which automates the process of changing the SPSS string text width.  🙂

 
*///resize variable////////////.
DEFINE !CNGWDTH (NAME !TOKENS (1) /WIDTH !TOKENS (1))
STRING NEWVAR (!WIDTH).
compute newvar=!name.
EXE.
DELETE VARIABLES !NAME.
RENAME VARIABLES NEWVAR=!NAME.
!ENDDEFINE.
*///////////////.

!CNGWDTH Name=email   Width=A65.

BTW- if you were reading this with the goal of changing the column width of variables in the data editor (think of Excel and how you resize the columns) the above macro has nothing to do with it.  Below is the code you’d use to change email to 30 wide; Country to 20 wide; and the rest to 6 wide.

 VARIABLE WIDTH ALL (6)  email (30) country (20).

Converting Yes-No Strings to Numerics with an SPSS Macro

SPSS macro

Often I have strings in my SPSS data set which have values like: Y,Yes,yes,No,n, etc. and I want them to be a numeric value so I can take their mean. This SPSS macro makes converting them a breeze!  You might need to adapt it to your data set but the principle can be applied for many uses.

The below video demonstrates it usage.  Definitely saves a lot of time if o

SPSS macro

These SPSS macros should regularly categorize the string variables correctly.

 
*/////////////////////.
DEFINE !con2Num (vnames=!CMDEND)
!DO !vname !IN (!vnames)
If INDEX(Upcase(!Vname),Upcase("Y"))>0 Temp1234= 1 .
If INDEX(Upcase(!Vname),Upcase("N"))>0 Temp1234= 0 .
FORMAT temp1234(F3.0).
Val Labels Temp1234 0"No" 1"Yes".
Var level Temp1234 (Scale).
MATCH FILES FILE=* /DROP=!vname .
RENAME VARIABLE (temp1234=!vname).
!DOEND
exe.
!ENDDEFINE.
*/////////////////////.
!con2Num vnames=var1 var2 var3 .

*/////////////////////.
DEFINE !con2NumNA (vnames=!CMDEND)
!DO !vname !IN (!vnames)
If INDEX(Upcase(!Vname),Upcase("N"))>0 Temp1234= 0 .
If INDEX(Upcase(!Vname),Upcase("Y"))>0 Temp1234= 1 .
If INDEX(Upcase(!Vname),Upcase("N/A"))>0 OR INDEX(Upcase(!Vname),Upcase("NA"))>0 Temp1234= 2 .
FORMAT temp1234(F4.0).
Var level Temp1234 (Nominal).
Val Labels Temp1234 0"No" 1"Yes" 2"NA".
MATCH FILES FILE=* /DROP=!vname .
RENAME VARIABLE (temp1234=!vname).
!DOEND
exe.
!ENDDEFINE.
*/////////////////////.

!con2NumNA vnames=var1 var2 var3 .

Here are some convenient references to learn more.

Learning SPSS Macros | Raynald’s SPSS Tools

This page is a simple introduction. There are several fully (line- by-line) commented examples of macro

Transform Data with SPSS Macros saves an amazing amount of time!

transform data

Transform data

In order to correctly perform most multivariate techniques, your data needs to be normally distributed.

Having to transform (log, square root, etc.)  used to be a daily thing for me. It would take some doing to create new variables for the dozens or so that I’d need to transform.  Using macros I’ve written in SPSS makes it a breeze!  I’ve built SPSS macros that will convert a list of variables (and add a pre-fix to their names) for Logs, Natural logs, Square roots, etc.  I also built one that performs all of them (just to get a bit crazy)

 

Here are the SPSS macros that I wrote

 
*//////transform data//////////.
DEFINE !clog10  ( Vars=!CMDEND)
Set Error off.
!DO !I !IN (!Vars).
Compute !Concat(!I,"_l") =lg10(!I+1).
Var Label  !Concat(!I,"_l") !Concat(!i," (l)").
Format !Concat(!I,"_l") (F4.1).
Var width !Concat(!I,"_l") (5).
!DOEND. 
Set Error on.
exe.
!ENDDEFINE.
/*!clog10  Vars=arm atm.

DEFINE !clog10D  ( Vars=!CMDEND)
Set Error off.
!DO !I !IN (!Vars).
Compute !Concat(!I,"_l") =lg10(!I+1).
Var Label  !Concat(!I,"_l") !Concat(!i," (l)").
Format !Concat(!I,"_l") (F4.1).
Var width !Concat(!I,"_l") (5).
!DOEND. 
Match files file=* / DROP !Vars  . 
Set Error on.
exe.
!ENDDEFINE.
/*!clog10D  Vars=arm atm.

DEFINE !Ulog10  ( Vars=!CMDEND)
!DO !I !IN (!Vars).
Compute !Concat(!I,"_ul10") =10**(!I)-1.
Var Label  !Concat(!I,"_ul10") !Concat(!i," (ul10)").
Format !Concat(!I,"_ul10") (F8.0).
Var width !Concat(!I,"_ul10") (9).
!DOEND. 
exe.
!ENDDEFINE.

DEFINE !cLogN  ( Vars=!CMDEND)
!DO !I !IN (!Vars).
Compute !Concat(!I,"_ln") =ln(!I+1).
Var Label  !Concat(!I,"_ln") !Concat(!i," (ln)").
Format !Concat(!I,"_ln") (F4.1).
Var width !Concat(!I,"_ln") (5).
!DOEND. 
exe.
!ENDDEFINE.

Define !ULogN  ( Vars=!CMDEND)
!DO !I !IN (!vars).
Compute !Concat(!I,"_uln") =EXP(!I)-1.
Var label  !Concat(!I,"_uln") !Concat(!i," (uln)").
Format !Concat(!I,"_uln") (F4.1).
Var width !Concat(!I,"_uln") (5).
!DOEND. 
exe.
!ENDDEFINE.

DEFINE !csqrt  (Vars=!CMDEND)
!DO !I !IN (!vars).
if !I=0  !Concat(!I,"_s") =0.
If !I<>0 !Concat(!I,"_s") =sqrt(!I).
Var label  !Concat(!I,"_s") !Concat(!i," (s)").
Format !Concat(!I,"_s") (F4.1).
Var width !Concat(!I,"_s") (5).
!DOEND. 
exe.
!ENDDEFINE.
*////////////////.
/*!cSqrt  Vars=arm atm.

*////////////////.
DEFINE !Usqrt  (Vars=!CMDEND)
!DO !I !IN (!Vars).
If !I=0  !Concat(!I,"_Us") =0.
If !I <>0 !Concat(!I,"_Us") =(!I*!I).
Var Label  !Concat(!I,"_Us") !Concat(!i," (s)").
Format !Concat(!I,"_Us") (F4.1).
Var width !Concat(!I,"_Us") (5).
!DOEND. 
exe.
!ENDDEFINE.
*////////////////.
/*!Usqrt  Vars=arm atm.


*////perform all transform data ////////////.
DEFINE !cALL  ( Vars=!CMDEND)
!DO !I !IN (!Vars).
Compute !Concat(!I,"_l") =lg10(!I+1).
Compute !Concat(!I,"_ln") =ln(!I+1).
Compute !Concat(!I,"_s") =sqrt(!I+1).
Var Label  !Concat(!I,"_l") !Concat(!i," (l)") / !Concat(!I,"_ln") !Concat(!i," (ln)") / !Concat(!I,"_s") !Concat(!i," (s)").
Format !Concat(!I,"_ln") !Concat(!I,"_l") !Concat(!I,"_s") (F4.1).
Var width !Concat(!I,"_ln") !Concat(!I,"_l") !Concat(!I,"_s")(5).
If !Concat(!I,"_s")=1 !Concat(!I,"_s")=0.
!DOEND. 
exe.
!ENDDEFINE.
*////////////////.
/*!cALL  Vars=arm atm.

Trust me you’ll never want to transform data without them again!

  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey