Looping through parallel lists/arrays in an SPSS macro

Question

I would like to write a SPSS macro to perform three operations:

1. generate a custom table,
2. clean the output window,
3. export table.

As you know SPSS macro facility allows to use two types of loops: 'numeric' like (!do !i = !x !to !y) and 'list'/'for each' like (!do !i !in (!1)). My goal is to create a macro with a call as below: col v1 v2 / "Sheet A" "Sheet B". working this way (with a 'list' like loop):

1. Get first variable name (v1)
2. Put it in the ctables macro section
3. Get first sheet name (string "Sheet A")
4. Put it in the output export macro section
5. Get second variable name
6. ...
7. Get second sheet name
8. ...

and so on.

At first I wrote this:

define col (!positional !charend('/')
/!positional !cmdend)

!do !i !in (!1)

output close all.

  ctables
  /table x1 + x2
  by !i [mean f1.2, totals[mean f1.2]].

  output modify
  /select logs headings texts warnings pagetitles outlineheaders notes
  /deleteobject delete = yes.

!doend

!do !i !in (!2)

   output export
    /contents export = visible
    /xlsx documentfile = "E:\path\file.xlsx"
      operation = createsheet
      sheet = !i.

!doend

!enddefine.

*** MACRO CALL.
col v1 v2 / "Sheet A" "Sheet B".

The output was overall incorrect because I got tables only with the second variable. However, in the file I found two sheets with correct names. So, I tried nesting:

define col (!positional !charend('/')
/!positional !cmdend)

!do !i !in (!1)
!do !j !in (!2)

output close all.

  ctables
  /table x1 + x2
  by !i [mean f1.2, totals[mean f1.2]].

  output modify
  /select logs headings texts warnings pagetitles outlineheaders notes
  /deleteobject delete = yes.

   output export
    /contents export = visible
    /xlsx documentfile = "E:\path\file.xlsx"
      operation = createsheet
      sheet = !j.

!doend
!doend

!enddefine.

*** MACRO CALL.
col v1 v2 / "Sheet A" "Sheet B".

The output was exactly the same meaning SPSS crosses each element from the list on the left side of '/' with each element from the list on the right side of '/' and overwrites previous results in Excel file. My goal is to receive macro expansions like this:

* FIRST EXPANSION:
...
  ctables
  /table x1 + x2
  by v1 [mean f1.2, totals[mean f1.2]].
...
   output export
    /contents export = visible
    /xlsx documentfile = "E:\path\file.xlsx"
      operation = createsheet
      sheet = "Sheet A".

* SECOND (LAST) EXPANSION:
...
  ctables
  /table x1 + x2
  by v2 [mean f1.2, totals[mean f1.2]].
...
   output export
    /contents export = visible
    /xlsx documentfile = "E:\path\file.xlsx"
      operation = createsheet
      sheet = "Sheet B".

In other words - 2 lists x 2 elements but only two loops - not four. Does anyone have any idea how to get such result?

Answer 1

To the best of my knowledge there is no formal/direct way to run a loop on parallel lists in SPSS macro, so your options are - either go Python, or find a workaround - here are a few ideas:

1. Recreate both lists using a number loop
so you loop on the numbers say 1 to 5, and use them to create the variable names - v1 to v5 and "sheet 1" to "sheet 5":

!do !i=1 !to 5
.....
  by !concat("v",!i) [mean f1.2, totals[mean f1.2]].
.....
  sheet = !quote(!concat("sheet ",!i))
.....
!doend

You can also add the beginning and\or end numbers to the macro call if they aren't constants.
This of course will work only if your two lists share a common numbering as in the example above. If they don't, you can use one of the following:

2. Loop through one list while "eating through" the second one
The SPSS macro loop only iterates through one list at a time - but instead of iterating through the second list you can take an item and delete it from the second list for every iteration of the first list:

define col (!pos !charend('/') / !pos !cmdend)
!let !arr2=!2
!do !i !in(!1)
    .....
    by !i [mean f1.2, totals[mean f1.2]].
    .....
!let !sheet=!head(!arr2)
!let !arr2=!tail(!arr2)
    .....
    sheet = !sheet
    .....
!doend
!enddefine.

*and the macro call:
col VARa VARb VARc/"sheet 1" "sheet 2" "sheet 3".

3. Define the macro on a single pair, use multiple macro calls This is the simplest way, if you only have a couple of items in each list - You can define the macro with a single variable name and a single sheet name, you'll be able to get the same results by running -

col VARa/ "sheet 1" .
col VARb/ "sheet 2" .

instead of col VARa VARb/ "sheet 1" "sheet 2" . As you tried before.

If these lists are long, you can automate running the macro calls separately (using write command for example - but this is for a separate question).