How can I read in multiple excel files in SPSS using a macro?

Question

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.

Andy W · Accepted Answer · 2013-02-11 17:36:53Z

2

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.

answered Feb 11, 2013 at 17:36

Andy W

5,1073 gold badges28 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Enucera Over a year ago

Excellent! It has worked. THank you so much. Seeing the syntax written out, I can see it is straight forward. But I was really struggling to get there. THANK YOU!!

Andy W Over a year ago

Good to know Enucera - feel free to check this as it answers your question and upvote. Good luck migrating from SAS to SPSS.

JKP · Accepted Answer · 2013-02-12 13:57:49Z

0

An alternative would be to use Python programmability with a wildcard expression to pick up all the files in a particular location with a specified name pattern. More flexible and avoids the need to struggle with macros. You can find out more by download the Programmaing and Data Management book from the Books and Articles section of the SPSS Community site at www.ibm.com/developerworks/spssdevcentral

answered Feb 12, 2013 at 13:57

JKP

5,41715 silver badges5 bronze badges

1 Comment

Enucera Over a year ago

That sounds good. It looks like we don't have Python. I may see what the cost is and the benefits so I can assess if we can purchase this.

Collectives™ on Stack Overflow

How can I read in multiple excel files in SPSS using a macro?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related