LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (February 2005)Back to main SPSSX-L pageJoin or leave SPSSX-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Mon, 28 Feb 2005 12:26:03 -0500
Reply-To:     Richard Ristow <>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Richard Ristow <>
Subject:      Re: Conditional file reading within macro
Comments: To: Allan Reese AFH CEFAS <>
In-Reply-To:  <95B6B3A55DFDD61180AB0002A5F089EEFABB89@WEYEXPRESS1.corp.ce
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 11:30 AM 2/28/2005, Allan Reese AFH CEFAS wrote:

>I am reading data from Excel spreadsheets. Sometimes the data are not >in the correct place. I thought it would be easy to detect this, but >I've hit a problem which seems to be that a macro !LET is executed as >the macro is defined, not as it is run.

It is executed when the macro is run. The catch is, macros are run at the very start of an SPSS run, before the SPSS program begins. They generate code that becomes part of the SPSS program, but then, "like a shadow in the night', they are gone.

> Here's the relevant code: > >GET DATA /TYPE=XLS /FILE=!FILEIN /SHEET=name !SHEET > /CELLRANGE=range 'A104:BA104' /READNAMES=off . >WRITE / "************************************** " !SHEET V1 V53. >DO IF (V1 NE V53). >- WRITE / "Bad column". >- !LET !TEST1 = 1. >ELSE IF ( (V1 EQ V53) AND (V53 NE 10)). >- WRITE / "Bad row". >- !LET !TEST1 = 2. >ELSE. >- WRITE / "DEBUG - 3" >- !LET !TEST1 = 3. >END IF. >WRITE / !QUOTE(!TEST1). >EXECUTE.

>Bad files cause the WRITE statements to give diagnostics, so I know >the ELSE condition has not been executed, but !TEST1 is still reported >as 3.

Quite right. The "WRITE" statements are ordinary SPSS syntax, and will (as you've seen) work. The !LET statements are processed first, and NOT conditionally. Your code executes the three !LET statements in sequence, ending with

- !LET !TEST1 = 3.

so 3 is the final value.

>Can anyone suggest a work round please?

The current standard work-around to conditionally execute SPSS code that is not part of a transformation program or input program, is to write a file with the code, and then read it back with INCLUDE. I understand that a more direct method is to be included in SPSS 14.

In the meantime, try something like this (untested). You'll see that I've replaced macro variable !TEST1 by regular variable TEST1. This writes and reads file GEN_CODE (which can be the name of a file handle, of course).

GET DATA /TYPE=XLS /FILE=!FILEIN /SHEET=name !SHEET /CELLRANGE=range 'A104:BA104' /READNAMES=off . WRITE / "************************************** " !SHEET V1 V53. DO IF (V1 NE V53). - WRITE / "Bad column". *--!LET !TEST1 = 1. - COMPUTE TEST1 = 1. ELSE IF ( (V1 EQ V53) AND (V53 NE 10)). - WRITE / "Bad row". *--!LET !TEST1 = 2. - COMPUTE TEST1 = 2. ELSE. - WRITE / "DEBUG - 3" *--!LET !TEST1 = 3. - COMPUTE TEST1 = 3. END IF. WRITE / !QUOTE(!TEST1).

DO IF TEST1 = 3. . WRITE . WRITE OUTFILE=GEN_CODE/ "GET DATA /TYPE=XLS /FILE=!FILEIN /SHEET=name !SHEET" /"/CELLRANGE=range 'B5:CT5' /READNAMES=off.". . WRITE OUTFILE=GEN_CODE/ "<other statements in your 'main block'>.". ELSE. . WRITE OUTFILE=GEN_CODE/ "* Skip this one.".!SHEET" END IF.

Kind of clumsy, I admit. But this is the best answer I know.

Back to: Top of message | Previous page | Main SPSSX-L page