如何使用宏在SPSS中读取多个Excel文件? [英] How can I read in multiple excel files in SPSS using a macro?

问题描述

我正在尝试编写一个宏以导入15个文件，所有文件的格式都相同.名称格式为许多PSF提取".因此，我可以使用下面的代码读取Nov11的1个文件.我试图找到一种使用宏读取其他14个文件的方法.我似乎无法使其正常工作.我是SPSS的新手-我知道如何在SAS中做到这一点.我还想将创建的数据集设置为monyy.我还将要重命名一些变量为original_name_monyy.

I'm trying to write a Macro to import 15 files, all in the same format. The name format is the "monyy PSF Extract". So I can use the code below to read in the 1 file for Nov11. I've tried to find a way of using the macro to read in also the other 14 files. I can't seem to make it work. I'm new to SPSS - I knew how to do this in SAS. I also want to set the dataset created to be named monyy. I will also want to rename some variables as original_name_monyy.

有人可以帮我吗?这简直让我发疯！

Can someone help me on this please?It is drivign me nuts!

define !XLSFILE() !quote(!con("S:\Credit Risk\Credit Risk\Elisabeth\",!unquote(!eval(!cq)), ".xlsx")) !enddefine.
define !cq(mon = !DEFAULT ("Nov11") !token(1) /name = !DEFAULT ("PSF Extract") !TOKENS(2)) !quo(!con(!unq(!mon),!unq(" "), !unq(!name))) !enddefine.


/* import xlsx file */.
GET DATA
  /TYPE=XLSX
  /FILE=!XLSFILE
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.

EXECUTE.

DATASET NAME  test WINDOW=FRONT.

推荐答案

您可以在宏调用中传递文件名前缀列表，并循环加载文件.以下是我将如何处理此问题.这有点限制，因为您需要传递月份列表，但这是一项相当琐碎的任务.

You can pass a list of file name pre-fixes in a macro call and loop through loading the files. Below is how I would approach this. It is a bit restrictive in that you need to pass the list of months, but it is fairly trivial a task.

*******************************************************.
DEFINE !XLSFILE(location = !TOKENS(1)
                /names = !CMDEND).

!DO !monthfile !IN (!names)

!LET !XLSFILE = !QUOTE(!CONCAT(!UNQUOTE(!location),!monthfile," PSF Extract.xlsx"))

/* import xlsx file */.
GET DATA
  /TYPE=XLSX
  /FILE=!XLSFILE
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
*Name dataset.
dataset name !monthfile.

**PLACE RENAME COMMANDS HERE.
*Example changing [XVAR1] and [XVAR2] to [XVAR1_monyy] and [XVAR2_monyy].
rename variables (XVAR1 = !CONCAT("XVAR1","_",!monthfile))
(XVAR2 = !CONCAT("XVAR2","_",!monthfile)).

!DOEND.

*now do whatever you want with the datasets, eg add files them together.
!ENDDEFINE.
*******************************************************.

*call the macro.
set mprint on.
!XLSFILE location = "S:\Credit Risk\Credit Risk\Elisabeth\"
names = Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11 Nov11 Dec11. 

这篇关于如何使用宏在SPSS中读取多个Excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！