I'll explain with this example. Here's sample data along with a simple table file:

KEY1 YEAR NUM1 NUM2
A 2007 5 10
A 2008 2 10
B 2007 5 10
B 2008 2 3

TABLE FILE MYTABLE
SUM NUM1
NUM2
COMPUTE DIFF/I2 = NUM2 - NUM1;

BY KEY1
BY YEAR
END

Simple enough..here's the results:

KEY1 YEAR NUM1 NUM2 DIFF
A 2007 5 10 5
A 2008 2 10 8
B 2007 5 10 5
B 2008 2 3 1


Now the hard part. I want to put a WHERE clause on the computed field. For example, where the difference is greater than 4 (WHERE TOTAL DIFF GT 4).
BUT, I want the condition to apply to 2008 only.
If the condition is not met, I don't want the KEY to show up for any year.

Basically, if the difference is equal or less than 4 for 2008, I don't want 2007 or 2008 to show up.
If the condition is met, I want both years.

If I apply that logic to the data above, the result would be:

KEY1 YEAR NUM1 NUM2 DIFF
A 2007 5 10 5
A 2008 2 10 8

Because KEY1 B for year 2008 had a difference of 1, I didn't want that key to show up in my results.

I have no idea how I can do this.

This message has been edited. Last edited by: Kerry,

Hello jif26,

There is probably a shorter and easier approach to what you need but how about this:

  
SET HOLDLIST = PRINTONLY
SET ASNAMES = ON
-*
-* Create sample data -> HMYDATA
DEFINE FILE CAR
REC_CNT/I2 WITH CAR = REC_CNT + 1;
KEY1/A1 WITH CAR= IF REC_CNT FROM 1 TO 2 THEN 'A' ELSE 'B';
YEAR/I4 WITH CAR= IF REC_CNT EQ 1 OR 3 THEN 2007 ELSE 2008;
NUM1/I4 WITH CAR= IF REC_CNT EQ 1 OR 3 THEN 5 ELSE 2;
NUM2/I4 WITH CAR= IF REC_CNT EQ 4 THEN 3 ELSE 10;
END
TABLE FILE CAR
PRINT 
        REC_CNT
        KEY1
        YEAR
        NUM1
        NUM2
WHERE REC_CNT LE 4
ON TABLE HOLD AS HMYDATA
END
-*
-* Determine differences by KEY for 2008
TABLE FILE HMYDATA
SUM
COMPUTE BASE_KEY/A1 = MAX.KEY1;
COMPUTE BASE_DIFF/I4 = NUM2 - NUM1;
BY KEY1 NOPRINT
BY YEAR AS 'BASE_YEAR'
WHERE YEAR EQ 2008
ON TABLE HOLD AS HGOODKEY FORMAT FOCUS INDEX BASE_KEY
END
-*
-* Print detail records whose KEY has a difference greater than 4
JOIN CLEAR *
JOIN KEY1 IN HMYDATA
  TO ALL BASE_KEY IN HGOODKEY
  AS J0
END
-*
TABLE FILE HMYDATA
PRINT
        KEY1
        YEAR
        NUM1
        NUM2
COMPUTE DIFF/I4 = NUM2 - NUM1;
WHERE BASE_DIFF GT 4
END

I'm basically calculating the differences for the year 2008 by each key (A and B) joining this results later the source data to get only the keys that match the condition.

Cheers,
- Neftali.

Couldn't I do something more simple by holding data for 2007 and another hold for 2008 (where diff > 4) and then concatenate both hold files but only take 2007 where a 2008 row exists ?

TABLE FILE MYTABLE
SUM NUM1
NUM2
COMPUTE DIFF/I2 = NUM2 - NUM1;

BY KEY1
BY YEAR
ON TABLE HOLD1
END

TABLE FILE HOLD1
SUM NUM1
NUM2
BY KEY1
BY YEAR
WHERE YEAR EQ 2007
ON TABLE HOLD AS OLDYEAR
END

TABLE FILE HOLD1
SUM NUM1
NUM2
BY KEY1
BY YEAR
WHERE YEAR EQ 2008
WHERE DIFF GT 4
ON TABLE HOLD AS CURRYEAR
END

Then concatenate years from CURRYEAR to OLDYEAR only where KEY1 exists on CURRYEAR ?

Would a MATCH or MORE phrase work here ?

I think Neftali was on the right track but here is a simpler version.

TABLE FILE YRNUM
SUM 
COMPUTE DIFF1/I4=NUM2-NUM1;
BY KEY1
WHERE YEAR EQ 2008
WHERE TOTAL DIFF1 GT 4
ON TABLE HOLD AS DIFF1 FORMAT ALPHA
END
JOIN KEY1 IN DIFF1 TO ALL KEY1 IN YRNUM AS J1
TABLE FILE DIFF1
PRINT NUM1 NUM2
BY KEY1
BY YEAR
END

If you put the keys in one file where the diff is greater than 4 for 2008, then you can use that file as the host of a join. If you do an inner join, you will get all of the records for a KEY1 where the diff is greater but you will not get the records if it is not.

Let us know what you think.

How about something like this:

TABLE FILE MYTABLE
SUM NUM1 NUM2
COMPUTE DIFF/I2 = IF YEAR EQ 2008 THEN (NUM2 - NUM1) ELSE LAST DIFF ;
BY KEY1
BY HIGHEST YEAR
WHERE TOTAL DIFF GT 4 ;
END

Thanks. I guess I had sort of the same idea. This is what I did before I saw your post:

TABLE FILE MYTABLE
SUM NUM1
NUM2
COMPUTE DIFF/I2 = NUM2 - NUM1;

BY KEY1
BY YEAR
ON TABLE HOLD1
END

TABLE FILE HOLD1
BY KEY1
WHERE YEAR EQ 2008
WHERE DIFF GT 4
ON TABLE HOLD AS HOLD2
END

MATCH FILE HOLD1
SUM NUM1
NUM2
DIFF
BY KEY1
BY YEAR
RUN
FILE HOLD2
BY KEY1
AFTER MATCH HOLD OLD-AND-NEW
END

Well, mine is shorter. And you have a syntax error on your first hold statement. But other than that ,it works just fine.

A caveat would be that MATCH won't always work for a one to many whereas JOIN always will.