SPSS CTables via macro

Question

I am new to SPSS macros. I intend on automating CTables production. In the ctables everything remains the same except the variable.

My command is:

   CTABLES
   /VLABELS VARIABLES=usevar anzahl gesamt F0passthrough DISPLAY=LABEL
   /TABLE usevar [C][COLPCT.VALIDN '' PCT40.1] + anzahl [C][COUNT '' F40.0] BY gesamt + F0passthrough
   /SLABELS POSITION=ROW
   /CATEGORIES VARIABLES=usevar anzahl ORDER=A KEY=VALUE EMPTY=INCLUDE
   /CATEGORIES VARIABLES=gesamt F0passthrough ORDER=A KEY=VALUE EMPTY=EXCLUDE.
   filter off. 

usevar is the variable I aim on exchanging with the macro. (my variables for example are F5 F6 F7)

so i tried:

DEFINE !usevar()
F1 F5
!ENDDEFINE.

   CTABLES
   /VLABELS VARIABLES=usevar anzahl gesamt F0passthrough DISPLAY=LABEL
   /TABLE usevar [C][COLPCT.VALIDN '' PCT40.1] + anzahl [C][COUNT '' F40.0] BY gesamt + F0passthrough
   /SLABELS POSITION=ROW
   /CATEGORIES VARIABLES=usevar anzahl ORDER=A KEY=VALUE EMPTY=INCLUDE
   /CATEGORIES VARIABLES=gesamt F0passthrough ORDER=A KEY=VALUE EMPTY=EXCLUDE.
   filter off. 

Any help is much appreciated - did not provide sample data. Just need a hint in the right direction.

Answer 1

First of all, if you define the macro with the name "!usevar", you have to use the same name in the syntax - "usevar" won't do.

Anyway I suggest a different approach to the macro:

define !MyCtabMacro (!pos=!cmdend)
   CTABLES
   /VLABELS VARIABLES=!1 anzahl gesamt F0passthrough DISPLAY=LABEL
   /TABLE !1 [C][COLPCT.VALIDN '' PCT40.1] + anzahl [C][COUNT '' F40.0] BY gesamt + F0passthrough
   /SLABELS POSITION=ROW
   /CATEGORIES VARIABLES=!1 anzahl ORDER=A KEY=VALUE EMPTY=INCLUDE
   /CATEGORIES VARIABLES=gesamt F0passthrough ORDER=A KEY=VALUE EMPTY=EXCLUDE.
   filter off. 
!enddefine.

Now you can call your macro to create a table for each of your variables, for example:

!MyCtabMacro F5.
!MyCtabMacro F6.

If you are going to do this for many variables, you can let the macro loop through them:

define !MyCtabMacro (!pos=!cmdend)
!do !onevar !in(!1)
   CTABLES
   /VLABELS VARIABLES=!onevar anzahl gesamt F0passthrough DISPLAY=LABEL
   /TABLE !onevar [C][COLPCT.VALIDN '' PCT40.1] + anzahl [C][COUNT '' F40.0] BY gesamt + F0passthrough
   /SLABELS POSITION=ROW
   /CATEGORIES VARIABLES=!onevar anzahl ORDER=A KEY=VALUE EMPTY=INCLUDE
   /CATEGORIES VARIABLES=gesamt F0passthrough ORDER=A KEY=VALUE EMPTY=EXCLUDE.
   filter off. 
!doend
!enddefine.

Now to call the macro:

!MyCtabMacro F5 F6 F7 F8 F9.

Note: for a macro loop you can't use "F5 to F9", you have to list all the variables separately as in my example.

Answer 2

The documentation guide for DEFINE / ENDDEFINE can be a bit scary at first and so to understand all it's features it's best to play around with examples at first.

I share three examples below, which should give you some indication of where you might be going wrong:

GET FILE="C:\Program Files\IBM\SPSS\Statistics\24\Samples\English\Employee data.sav".

/* Example1: Using macro as a global string substitution for variable names */.
DEFINE !MyMac1 () educ jobcat !ENDDEFINE.
FREQ !MyMac1.


/* Example2: Having command in the body of macro with variable input as an argument */.
/* Result: Notice only single FREQ command is run with two variables */.

DEFINE !MyMac2 (VARS=!CMDEND).
SET MPRINT ON.
FREQ !VARS.
SET MPRINT OFF.
!ENDDEFINE.
!MyMac2 vars=educ jobcat.


/* Example3: Having command in the body of macro with variable input as an argument */
/* but looping over each variable */.
/* Result: Notice two separate FREQ commands are run */.
/* with one variable each, i.e. looped for each variable */.

DEFINE !MyMac3 (VARS=!CMDEND).
SET MPRINT ON.
!DO !I !IN (!VARS)
FREQ !i.
!DOEND
SET MPRINT OFF.
!ENDDEFINE.
!MyMac3 vars=educ jobcat.

The features of DEFINE/ENDDEFINE can be used in various ways. Once you build some knowledge of them all, you'll soon develop a particular style of how you prefer to code your macros. If you are learning SPSS macros for the first time and have some knowledge (or interest in Python) then I would encourage you NOT to start a journey of learning SPSS macros but instead learn python as this type of macro building is much more efficient (and fun!) to code in with Python (amongst many other benefits).