Hello,

I am sorting a file by a policy number and sorting them by the highest date. The only problem is i need the filter the groups out that dont have a record wiith the current year.

What I have tried is filtering out these by use the last command in the if statement. The result is I come up with the groups with 2006 but it leaves out the rest of the records from that group I want. So I have used the match command to try to bring back in those records and it almost worked but now I am missing some of the groups. Here is my code:


000070 MATCH FILE HOLDMMV
000071 SUM SYMBOL
000072 BY POLICYNO
000073 RUN
000074 FILE HOLDMMU
000075 PRINT CLAIMNUMBE
000076 EFFECTDATE
000077 ACCIDENTDE
000078 CLAIMSTATU
000079 TOTALLOSS
000080 TOTALRECOV
000081 SLOSSRESE
000082 LOSSEXAMIN
000083 LOSSHANDLI
000084 ABCD
000085 BY PIFPOLICYNO
000086 AFTER MATCH HOLD NEW-NOT-OLD
000087 ON TABLE HOLD
000086 AFTER MATCH HOLD NEW-NOT-OLD
000087 ON TABLE HOLD
000088 END
000089 -*

If I understand you correctly, you want records on everyone historically, if they have the current year as one of the records? I'd do it with a two pass to get one record for each 'current' year, sort it by the key fields, then join to a file using TO ALL on another file containing all the data.

TABLE FILE A
PRINT X
BY Y
WHERE Z IS-FROM DATE1 TO DATE2 (ON WAY TO PULL ALL 2006 RECS)
ON TABLE HOLD AS RPTFILE
END
JOIN Y IN RPTFILE TO ALL Y IN A AS J1
TABLE FILE PRTFILE
PRINT .....

Here's another idea...

This code will print all employees in a department that has hired someone in 2006. In each record of the hold file, you have the total number of employees in the department as well as the number of employees hired in 2006.

DEFINE FILE EMPLOYEE
DEPT_CNT/I5 = 1;
CUR_YR_CNT/I5 = IF DATE_HIRED GE 'JAN 01 2006' THEN 1 ELSE 0;
END
-*
TABLE FILE EMPLOYEE
SUM
DEPT_CNT
CUR_YR_CNT
BY DEPARTMENT
PRINT
LAST_NAME
FIRST_NAME
DATE_HIRED
BY DEPARTMENT
BY EMPLOYEE
WHERE DF_TRM_STATUS EQ 'A'
ON TABLE HOLD
END
-*
TABLE FILE HOLD
PRINT *
WHERE CUR_YR_CNT GT 0
END

Jim

Thanks guys your suggestions were good, but I had a problem earlier in this program. Turns out I was filtering out non 2006 records before I filtered out single instances of the claim. Because of that I was excluding some of the 2006 records.