循环遍历 SPSS 宏中的并行列表/数组

问题描述

我想写一个 SPSS 宏来执行三个操作:

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

1. 生成自定义表，
2. 清理输出窗口，
3. 导出表.

如您所知，SPSS 宏工具允许使用两种类型的循环:'numeric' like (!do !i = !x !to !y) 和 'list'/'for each'像 (!do !i !in (!1)).我的目标是创建一个调用如下的宏:col v1 v2/"Sheet A" "Sheet B". 以这种方式工作(使用列表"之类的循环):

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. 获取第一个变量名 (v1)
2. 把它放在ctables宏部分
3. 获取第一个工作表名称(字符串工作表 A")
4. 把它放在output export宏部分
5. 获取第二个变量名
6. ...
7. 获取第二个工作表名称
8. ...

等等.

起初我是这样写的:

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".

输出完全相同，这意味着 SPSS 将/"左侧列表中的每个元素与/"右侧列表中的每个元素交叉，并覆盖 Excel 文件中的先前结果.我的目标是接收这样的宏扩展:

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".

换句话说 - 2 个列表 x 2 个元素但只有两个循环 - 而不是四个.有谁知道如何得到这样的结果?

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

推荐答案

据我所知，在 SPSS 宏中没有正式/直接的方式在并行列表上运行循环，因此您的选择是 - 要么使用 Python，或找到解决方法 - 以下是一些想法:

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.使用数字循环重新创建两个列表
所以你循环数字说 1 到 5，并使用它们来创建变量名称 -v1 到 v5 和工作表 1"到工作表 5":

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.循环遍历一个列表，同时吃掉"第二个列表
SPSS 宏循环一次只遍历一个列表 - 但不是遍历第二个列表，您可以在第一个列表的每次迭代时从第二个列表中取出一个项目并删除它:

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.在单对上定义宏，使用多个宏调用这是最简单的方法，如果每个列表中只有几个项目 - 您可以使用单个变量名称和单个工作表名称定义宏，您将能够通过运行获得相同的结果 -

3. Define the macro on a single pair, use multiple macro callsThis 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" .

而不是 col VARa VARb/"sheet 1" "sheet 2" . 正如你之前尝试过的那样.

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

如果这些列表很长，您可以单独自动运行宏调用(例如使用 write 命令 - 但这是一个单独的问题).

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).

这篇关于循环遍历 SPSS 宏中的并行列表/数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！