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

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!

SPSS macro to automatically remove duplicates and drop variable

SPSS macroSPSS macro for removing duplicates

SPSS has a built in GUI function for removing duplicates however it is lacking some of the key options that I like to use (perform case insensitive dedupe, drop duplicates, drop the flag, etc). I built my own macro which is very easy to use and has all the options I typically need.

The following video demonstrates them in use.

 

Here are the SPSS macros 

*///////////////.
DEFINE !DDN (DupVar !TOKENS (1))
SORT CASES BY !DupVar (A) .
MATCH FILES /FILE = *  /BY !DupVar /FIRST = First.
var label first "Unique and Duplicates".
value label first 0'Duplicates' 1'Unique'.
Freq First /FORMAT=DVALUE.
select if First=1.
match files file= * / drop First.
exe.
!ENDDEFINE.
*///////////////.
/* !DDN DupVar=Number.

*////SPSS macro to remove duplicates on String variable///////////.
DEFINE !DDA (DupVar !TOKENS (1))
String duh (A1000).
SORT CASES BY !DupVar (A) .
compute duh=Lowcase(!DupVar).
MATCH FILES /FILE = *  /BY Duh /FIRST = First.
var label first "Unique and Duplicates".
value label first 0'Duplicates' 1'Unique'.
Freq First /FORMAT=DVALUE.
select if First=1.
match files file= * / drop First duh.
exe.
!ENDDEFINE.
*///////////////.
/* !DDA DupVar=email.

*///////////////.
DEFINE !DDM  (Var1 !TOKENS (1)   / Var2 !Tokens (1) )
String duh1 duh2 (A1000).
Compute duh1=Lowcase(!Var1).
Compute duh2=Lowcase(!Var2).
SORT CASES BY Duh1 (A) Duh2 (A).
MATCH FILES /FILE = * /BY Duh1  Duh2 /FIRST =First.
var label first "Unique and Duplicates".
value label first 0'Duplicates' 1'Unique'.
Freq First /FORMAT=DVALUE.
select if First=1.
match files file= * / drop First Duh1 Duh2.
exe.
EXECUTE.
!ENDDEFINE.
*///////////////.
/* !DDM Var1=Corporate  Var2=gend.


*/////SPSS macro to remove duplicates on two string variables//////////.
DEFINE !DDA2 (DupVar !TOKENS (1))
SORT CASES BY !DupVar.
Compute Unique=1.
if Upcase(!DupVar)=Upcase(Lag(!DupVar)) Unique=0.
Freq Unique.
Select if Unique=1.
Match files file=* / DROP Unique.
exe.
!ENDDEFINE.
*///////////////.
/*!DDA2  DupVar=email. 

 

 

Drastically reduce work needed for Factor Analysis with SPSS Macro

SPSS macroSPSS Macros drastically reduce the time needed to run factor analysis and creating summary variables.  In the video below I demonstrate how you can greatly increase the speed at which you analyze your data as well as creating the new variables with summary factor scores.

By running Factor analysis twice (but changing the output) you can make it much easier to create summary variables once you’re happy with your solution!

Video demonstrating SPSS Macro running Factor Analysis

Here is the SPSS macro for Factor analysis:

 
*///////////////.
DEFINE !Factor (Rotate !TOKENS (1)   / Title !Tokens (1)  / Sav  !Tokens (1)  /Varys !CMDEND)
Include "B:\SPSS\Syntax\Output_Off.SPS".
script "B:\SPSS\Scripts\Empty.sbs".

Include "B:\SPSS\Syntax\Show Names.SPS".
FACTOR  /VARIABLES !Varys  /PRINT ROTATION  /FORMAT SORT  /CRITERIA MINEIGEN(1) ITERATE(25)  /EXTRACTION PC 
 /CRITERIA ITERATE(50)   /METHOD=CORRELATION /ROTATION !Rotate  / !Unquote(!SAV).
Script "B:\Scripts\Parse\Clean viewer.sbs" ("Component Transformation Matrix").
Script "B:\Scripts\Parse\Clean viewer.sbs" ("Component Matrix").
script "B:\Scripts\S2\Wrap Rows.sbs".
*Script "B:\Scripts\Factor Flag.SBS".
Script "B:\Scripts\Flag above .45.SBS".
Script "B:\Scripts\Flag above .65.SBS".
Script  "B:\Scripts\Flag above .80.SBS".
Script "B:\Scripts\S2\Right Align Column.SBS".
Script "B:\Scripts\Parse\Clean viewer.sbs" ("Total Variance Explained").

*/ Second run of SPSS Macro *.
Include "B:\Syntax\Show Labels.SPS".
FACTOR  /VARIABLES !Varys  /PRINT ROTATION  /FORMAT SORT  /CRITERIA MINEIGEN(1) ITERATE(25)  /EXTRACTION PC 
 /CRITERIA ITERATE(50)   /METHOD=CORRELATION  /ROTATION !Rotate.
Script "B:\Scripts\Parse\Clean viewer.sbs" ("Component Transformation Matrix").
Script "B:\Scripts\Parse\Clean viewer.sbs" ("Component Matrix").
script "B:\Scripts\S2\Wrap Rows.sbs".
*Script "B:\Scripts\Factor Flag.SBS".
Script "B:\Scripts\Flag above .45.SBS".
Script "B:\Scripts\Flag above .65.SBS".
Script  "B:\Scripts\Flag above .80.SBS".
Script "B:\Scripts\S2\Right Align Column.SBS".
!ENDDEFINE.
*///////////////.
!Factor  Rotate=Varimax   SAV="SAVE REG(ALL)"      Varys=  to  .*/
/*EQUAMAX     QUARTIMAX  VARIMAX  */.

And here is the other SPSS macro which eases creating of a new factor.  It takes a little getting used to but is well worth the effort if you’re doing a lot of factor analysis.

*////////////////.
DEFINE !CreateFac  (NewFac !TOKENS (1) / Label !TOKENS (1)  / Vars=!CMDEND)
Compute !NewFac=Mean(!HEAD(!Vars) !DO !v !IN (!TAIL(!Vars)) , !v !DOEND ).
Format !NewFac (F5.2).
Var Label !NewFac !Label.
exe.
!ENDDEFINE.
*////////////////.
/*!CreateFac  NewFac=Joe Label="Joe Rocks" Vars=Enc_friend Proud Und_wrk_res Eth_Std Job_aligns MissPurp.

Learning Macros | Raynald’s SPSS Tools

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

Programming with SPSS Syntax and Macros – Tarstud.hu

Instead, a SPSS macro could generate the SPSS Syntax for each interactive graph within a loop, substituting a new rating scale variable name per iteration.