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 16:30:24 -0000
Reply-To:     Allan Reese AFH CEFAS <>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Allan Reese AFH CEFAS <>
Subject:      Conditional file reading within macro
Content-Type: text/plain; charset="iso-8859-1"

Hi all

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. 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. !IF ( !TEST1 = 3 ) !THEN ***** FALL THROUGH AND PROCESS DATA: (1) READ LANDED / DISCARDED ROW. NEW FILE /* ========== START OF MAIN BLOCK. GET DATA /TYPE=XLS /FILE=!FILEIN /SHEET=name !SHEET /CELLRANGE=range 'B5:CT5' /READNAMES=off .

The WRITE statements indicate that the data are read correctly and found to be right or wrong. With the code as shown, it then proceeds to read the data regardless. With the !LET !TEST1=3 before the checks, it proceeds NOT to read the data. Can anyone suggest a work round please?

The evidence I have is that 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. Good data has A104=BA104=10.

Why am I doing this? The "correct" spreadsheet has the data in bits, and the first problem was to take column identifiers and transpose them to form case identifiers. FLIP does not work with strings, but you can read a row of cells, write them to a file, and read the lines of the file as cases. So the data input works, and I can identify duff files, but currently the duff files still get read and make spurious moaning output.

Allan Reese

*********************************************************************************** This email and any attachments are intended for the named recipient only. Its unauthorised use, distribution, disclosure, storage or copying is not permitted. If you have received it in error, please destroy all copies and notify the sender. In messages of a non-business nature, the views and opinions expressed are the author's own and do not necessarily reflect those of the organisation from which it is sent. All emails may be subject to monitoring. ***********************************************************************************

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