Ok, I have been struggling with this for a day now...I have a report that compares 2 months data, same table, same columns, just different months. I had originally done a left outter join, and it was working beautifully until I noticed that a person could be missing from one month or the other. So then I change the join to a MATCH. This worked one way but not the other, in other words, if the person was missing from one month, it would match data one way, but if I reversed the match it would put zeros in the column data, I'm assuming because one side had nulls. So I tried the concatenation method, which worked, but now I cannot compare the data the way I originally did.

What I really want, which I would have thought the match would have worked...is to compare both sets of data, return OLD-AND-NEW and then compare the data hold file to spit out the differences. I think Im staring at this too long, someone may be able to tell me faster what my issue is.

Here is the original code that does the final comparison.


-MRNOEDIT BEGIN
-INCLUDE SET_ENV_LIST
-MRNOEDIT END
-DEFAULT &covdt1 = '12312011'
-DEFAULT &covdt2 = '01312012'
SET ASNAMES = MIXED
DEFINE FILE SFA_MONTHLY_ERS
EMPAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPAMT, '20', EMPAMTNUM);
EMPYRAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.STEAMT, '20', EMPYRAMTNUM);
HCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCR_MON_AMT, '20', HCRANNAMT);
DCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCR_MON_AMT, '20', DCRANNAMT);
EMP_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPAMTNUM ELSE 0;
EMPLR_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPYRAMTNUM ELSE 0;
EMP_DENTAL_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DH 1','DH 2','DH 3','DH 4','DB 1','DB 2','DB 3','DB 4') THEN EMPAMTNUM ELSE 0;
EMP_OPTLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('OL1','OL2','OL3','OL4','OL170','OL175','OL180','OL185','OL270','OL275','OL280','OL285','OL370','OL375','OL380','OL385','OL470','OL475','OL480','OL485') THEN EMPAMTNUM ELSE 0;
EMP_ADD_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('AD1','AD170','AD175','AD180','AD4','AD470','AD475','AD480') THEN EMPAMTNUM ELSE 0;
EMP_DEPLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DL') THEN EMPAMTNUM ELSE 0;
EMP_SHTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('ST') THEN EMPAMTNUM ELSE 0;
EMP_LNGTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('LT') THEN EMPAMTNUM ELSE 0;
EMPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN HCRANNAMT ELSE 0;
DEPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN DCRANNAMT ELSE 0;
SSN /A9V=EDIT(ASSN,'999999999');
BASIC_LIFE/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPAMTNUM ELSE 0;
BASIC_LIFE_EMPLR/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPYRAMTNUM ELSE 0;
EMP_HEALTH_ERS2/P12.2=EMP_HEALTH_ERS + BASIC_LIFE;
EMPLYER_HEALTH_ERS2/P12.2=EMPLR_HEALTH_ERS + BASIC_LIFE_EMPLR;
END
TABLE FILE SFA_MONTHLY_ERS
SUM
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DENTAL_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_OPTLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_ADD_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DEPLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_SHTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_LNGTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPLYER_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPFLEX_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DEPFLEX_ERS
BY SSN
WHERE SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.COVDT = '&covdt1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLDERS1
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.666667,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=1.500000,
$
ENDSTYLE
END

DEFINE FILE SFA_MONTHLY_ERS
EMPAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPAMT, '20', EMPAMTNUM);
EMPYRAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.STEAMT, '20', EMPYRAMTNUM);
HCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCR_MON_AMT, '20', HCRANNAMT);
DCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCR_MON_AMT, '20', DCRANNAMT);
EMP_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPAMTNUM ELSE 0;
EMPLR_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPYRAMTNUM ELSE 0;
EMP_DENTAL_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DH 1','DH 2','DH 3','DH 4','DB 1','DB 2','DB 3','DB 4') THEN EMPAMTNUM ELSE 0;
EMP_OPTLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('OL1','OL2','OL3','OL4','OL170','OL175','OL180','OL185','OL270','OL275','OL280','OL285','OL370','OL375','OL380','OL385','OL470','OL475','OL480','OL485') THEN EMPAMTNUM ELSE 0;
EMP_ADD_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('AD1','AD170','AD175','AD180','AD4','AD470','AD475','AD480') THEN EMPAMTNUM ELSE 0;
EMP_DEPLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DL') THEN EMPAMTNUM ELSE 0;
EMP_SHTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('ST') THEN EMPAMTNUM ELSE 0;
EMP_LNGTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('LT') THEN EMPAMTNUM ELSE 0;
EMPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN HCRANNAMT ELSE 0;
DEPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN DCRANNAMT ELSE 0;
SSN /A9V=EDIT(ASSN,'999999999');
BASIC_LIFE/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPAMTNUM ELSE 0;
BASIC_LIFE_EMPLR/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPYRAMTNUM ELSE 0;
EMP_HEALTH_ERS2/P12.2=EMP_HEALTH_ERS + BASIC_LIFE;
EMPLYER_HEALTH_ERS2/P12.2=EMPLR_HEALTH_ERS + BASIC_LIFE_EMPLR;
END
TABLE FILE SFA_MONTHLY_ERS
SUM
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DENTAL_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_OPTLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_ADD_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DEPLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_SHTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_LNGTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPLYER_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPFLEX_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DEPFLEX_ERS
BY SSN
WHERE SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.COVDT = '&covdt2'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLDERS2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.666667,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=1.500000,
$
ENDSTYLE
END
JOIN
LEFT_OUTER HLDERS2.HLDERS2.SSN IN HLDERS2 TO HLDERS1.HLDERS1.SSN
IN HLDERS1 TAG J1 AS J1
END
JOIN
LEFT_OUTER HLDERS2.HLDERS2.SSN IN HLDERS2 TO MULTIPLE
SPBPERS.SPBPERS.SPBPERS_SSN IN SPBPERS TAG J2 AS J2
END
JOIN
LEFT_OUTER FILE HLDERS1 AT J2.SPBPERS.SPBPERS_PIDM TO MULTIPLE FILE SPRIDEN
AT SPRIDEN.SPRIDEN.SPRIDEN_PIDM TAG J3 AS J3
WHERE ( J2.SPBPERS.SPBPERS_PIDM EQ J3.SPRIDEN.SPRIDEN_PIDM ) AND ( J3.SPRIDEN.SPRIDEN_CHANGE_IND EQ MISSING );
END
DEFINE FILE HLDERS2
DISPTABL/A1 = IF ((HLDERS1.HLDERS1.EMP_HEALTH_ERS2 EQ HLDERS2.HLDERS2.EMP_HEALTH_ERS2) AND (HLDERS1.HLDERS1.EMP_DENTAL_ERS EQ HLDERS2.HLDERS2.EMP_DENTAL_ERS) AND ( HLDERS1.HLDERS1.EMP_OPTLIFE_ERS EQ HLDERS2.HLDERS2.EMP_OPTLIFE_ERS) AND (HLDERS1.HLDERS1.EMP_ADD_ERS EQ HLDERS2.HLDERS2.EMP_ADD_ERS) AND ( HLDERS1.HLDERS1.EMP_DEPLIFE_ERS EQ HLDERS2.HLDERS2.EMP_DEPLIFE_ERS) AND (HLDERS1.HLDERS1.EMP_SHTERMDIS_ERS EQ HLDERS2.HLDERS2.EMP_SHTERMDIS_ERS) AND (HLDERS1.HLDERS1.EMP_LNGTERMDIS_ERS EQ HLDERS2.HLDERS2.EMP_LNGTERMDIS_ERS) AND (HLDERS1.HLDERS1.EMPLYER_HEALTH_ERS2 EQ HLDERS2.HLDERS2.EMPLYER_HEALTH_ERS2) AND (HLDERS1.HLDERS1.EMPFLEX_ERS EQ HLDERS2.HLDERS2.EMPFLEX_ERS) AND (HLDERS1.HLDERS1.DEPFLEX_ERS EQ HLDERS2.HLDERS2.DEPFLEX_ERS)) THEN 'N' ELSE 'Y';
NAME/A25=SPRIDEN.SPRIDEN.SPRIDEN_LAST_NAME || ', ' | SPRIDEN.SPRIDEN.SPRIDEN_FIRST_NAME || ' ' | SPRIDEN_MI;
DIFF1/P12.2 = IF (HLDERS1.HLDERS1.EMP_HEALTH_ERS2 NE HLDERS2.HLDERS2.EMP_HEALTH_ERS2) THEN HLDERS1.HLDERS1.EMP_HEALTH_ERS2 - HLDERS2.HLDERS2.EMP_HEALTH_ERS2 ELSE 0;
DIFF2/P12.2 = IF (HLDERS1.HLDERS1.EMP_DENTAL_ERS NE HLDERS2.HLDERS2.EMP_DENTAL_ERS) THEN HLDERS1.HLDERS1.EMP_DENTAL_ERS - HLDERS2.HLDERS2.EMP_DENTAL_ERS ELSE 0;
DIFF3/P12.2 = IF (HLDERS1.HLDERS1.EMP_OPTLIFE_ERS NE HLDERS2.HLDERS2.EMP_OPTLIFE_ERS) THEN HLDERS1.HLDERS1.EMP_OPTLIFE_ERS - HLDERS2.HLDERS2.EMP_OPTLIFE_ERS ELSE 0;
DIFF4/P12.2 = IF (HLDERS1.HLDERS1.EMP_ADD_ERS NE HLDERS2.HLDERS2.EMP_ADD_ERS) THEN HLDERS1.HLDERS1.EMP_ADD_ERS - HLDERS2.HLDERS2.EMP_ADD_ERS ELSE 0;
DIFF5/P12.2 = IF (HLDERS1.HLDERS1.EMP_DEPLIFE_ERS NE HLDERS2.HLDERS2.EMP_DEPLIFE_ERS) THEN HLDERS1.HLDERS1.EMP_DEPLIFE_ERS - HLDERS2.HLDERS2.EMP_DEPLIFE_ERS ELSE 0;
DIFF6/P12.2 = IF (HLDERS1.HLDERS1.EMP_SHTERMDIS_ERS NE HLDERS2.HLDERS2.EMP_SHTERMDIS_ERS) THEN HLDERS1.HLDERS1.EMP_SHTERMDIS_ERS - HLDERS2.HLDERS2.EMP_SHTERMDIS_ERS ELSE 0;
DIFF7/P12.2 = IF (HLDERS1.HLDERS1.EMP_LNGTERMDIS_ERS NE HLDERS2.HLDERS2.EMP_LNGTERMDIS_ERS) THEN HLDERS1.HLDERS1.EMP_LNGTERMDIS_ERS - HLDERS2.HLDERS2.EMP_LNGTERMDIS_ERS ELSE 0;
DIFF8/P12.2 = IF (HLDERS1.HLDERS1.EMPFLEX_ERS NE HLDERS2.HLDERS2.EMPFLEX_ERS) THEN HLDERS1.HLDERS1.EMPFLEX_ERS - HLDERS2.HLDERS2.EMPFLEX_ERS ELSE 0;
DIFF9/P12.2 = IF (HLDERS1.HLDERS1.DEPFLEX_ERS NE HLDERS2.HLDERS2.DEPFLEX_ERS) THEN HLDERS1.HLDERS1.DEPFLEX_ERS - HLDERS2.HLDERS2.DEPFLEX_ERS ELSE 0;
DIFF10/P12.2 = IF (HLDERS1.HLDERS1.EMPLYER_HEALTH_ERS NE HLDERS2.HLDERS2.EMPLYER_HEALTH_ERS2) THEN HLDERS1.HLDERS1.EMPLYER_HEALTH_ERS - HLDERS2.HLDERS2.EMPLYER_HEALTH_ERS ELSE 0;
END
TABLE FILE HLDERS2
PRINT
HLDERS1.HLDERS1.SSN
NAME
HLDERS1.HLDERS1.EMP_HEALTH_ERS2 AS 'EMP, HEALTH-&covdt1 '
HLDERS2.HLDERS2.EMP_HEALTH_ERS2 AS 'EMP, HEALTH-&covdt2 '
DIFF1 AS 'DIF,HLTH'
HLDERS1.HLDERS1.EMP_DENTAL_ERS AS 'DENTAL-&covdt1 '
HLDERS2.HLDERS2.EMP_DENTAL_ERS AS 'DENTAL-&covdt2 '
DIFF2 AS 'DIF,DENT'
HLDERS1.HLDERS1.EMP_OPTLIFE_ERS AS 'OPT, LIFE-&covdt1 '
HLDERS2.HLDERS2.EMP_OPTLIFE_ERS AS 'OPT, LIFE-&covdt2 '
DIFF3 AS 'DIF,LIFE'
HLDERS1.HLDERS1.EMP_ADD_ERS AS 'ADD &covdt1 '
HLDERS2.HLDERS2.EMP_ADD_ERS AS 'ADD &covdt2 '
DIFF4 AS 'DIF,ADD'
HLDERS1.HLDERS1.EMP_DEPLIFE_ERS AS 'DEP, LIFE-&covdt1 '
HLDERS2.HLDERS2.EMP_DEPLIFE_ERS AS 'DEP, LIFE-&covdt2 '
DIFF5 AS 'DIF,DEP-LIFE'
HLDERS1.HLDERS1.EMP_SHTERMDIS_ERS AS 'STD-&covdt1 '
HLDERS2.HLDERS2.EMP_SHTERMDIS_ERS AS 'STD-&covdt2 '
DIFF6 AS 'DIF,STD'
HLDERS1.HLDERS1.EMP_LNGTERMDIS_ERS AS 'LTD-&covdt1 '
HLDERS2.HLDERS2.EMP_LNGTERMDIS_ERS AS 'LTD-&covdt2 '
DIFF7 AS 'DIF,LTD'
HLDERS1.HLDERS1.EMPFLEX_ERS AS 'HCR, FLEX-&covdt1 '
HLDERS2.HLDERS2.EMPFLEX_ERS AS 'HCR, FLEX-&covdt2 '
DIFF8 AS 'DIF,HCR'
HLDERS1.HLDERS1.DEPFLEX_ERS AS 'DCR, FLEX-&covdt1 '
HLDERS2.HLDERS2.DEPFLEX_ERS AS 'DCR, FLEX-&covdt2 '
DIFF9 AS 'DIF,DCR'
HLDERS1.HLDERS1.EMPLYER_HEALTH_ERS2/D12.2 AS 'STATE,HEALTH-&covdt1 '
HLDERS2.HLDERS2.EMPLYER_HEALTH_ERS2/D12.2 AS 'STATE,HEALTH-&covdt2 '
DIFF10 AS 'DIF,ST-HLTH'
BY NAME NOPRINT
ON TABLE SUBHEAD
"ERS &covdt1 TO ERS &covdt2 BILLED Comparisons "
ON TABLE SUBFOOT
"Run Date: <+0>&DATEMDYY "
WHERE DISPTABL EQ 'Y';
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT &WFFMT.(,).Select type of display output.
ON TABLE SET STYLE *
INCLUDE = endeflt,

$
ENDSTYLE
END
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
LINE=1,
JUSTIFY=LEFT,
$
TYPE=TABFOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=TABFOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
SIZE=10,
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.500000,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=1.791667,
$
ENDSTYLE
END

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

WebFOCUS 7.6.9
Windows
all output (Excel, HTML, PDF)

here is the MATCH that produces zeros depending on which way your run it

-MRNOEDIT BEGIN
-INCLUDE SET_ENV_LIST
-MRNOEDIT END
-DEFAULT &covdt1 = '12312011'
-DEFAULT &covdt2 = '01312012'
SET ASNAMES = MIXED
SET ALL = PASS
DEFINE FILE SFA_MONTHLY_ERS
EMPAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPAMT, '20', EMPAMTNUM);
EMPYRAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.STEAMT, '20', EMPYRAMTNUM);
HCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCR_MON_AMT, '20', HCRANNAMT);
DCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCR_MON_AMT, '20', DCRANNAMT);
EMP_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPAMTNUM ELSE 0;
EMPLR_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPYRAMTNUM ELSE 0;
EMP_DENTAL_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DH 1','DH 2','DH 3','DH 4','DB 1','DB 2','DB 3','DB 4') THEN EMPAMTNUM ELSE 0;
EMP_OPTLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('OL1','OL2','OL3','OL4','OL170','OL175','OL180','OL185','OL270','OL275','OL280','OL285','OL370','OL375','OL380','OL385','OL470','OL475','OL480','OL485') THEN EMPAMTNUM ELSE 0;
EMP_ADD_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('AD1','AD170','AD175','AD180','AD4','AD470','AD475','AD480') THEN EMPAMTNUM ELSE 0;
EMP_DEPLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DL') THEN EMPAMTNUM ELSE 0;
EMP_SHTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('ST') THEN EMPAMTNUM ELSE 0;
EMP_LNGTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('LT') THEN EMPAMTNUM ELSE 0;
EMPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN HCRANNAMT ELSE 0;
DEPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN DCRANNAMT ELSE 0;
SSN /A9V=EDIT(ASSN,'999999999');
BASIC_LIFE/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPAMTNUM ELSE 0;
BASIC_LIFE_EMPLR/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPYRAMTNUM ELSE 0;
EMP_HEALTH_ERS2/P12.2=EMP_HEALTH_ERS + BASIC_LIFE;
EMPLYER_HEALTH_ERS2/P12.2=EMPLR_HEALTH_ERS + BASIC_LIFE_EMPLR;
END
TABLE FILE SFA_MONTHLY_ERS
SUM
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DENTAL_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_OPTLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_ADD_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DEPLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_SHTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_LNGTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPLYER_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPFLEX_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DEPFLEX_ERS
BY LOWEST SSN
WHERE SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.COVDT EQ '&covdt1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLDERS1
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.666667,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=1.500000,
$
ENDSTYLE
END

DEFINE FILE SFA_MONTHLY_ERS
EMPAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPAMT, '20', EMPAMTNUM);
EMPYRAMTNUM/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.STEAMT, '20', EMPYRAMTNUM);
HCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCR_MON_AMT, '20', HCRANNAMT);
DCRANNAMT/P12.2=ATODBL(SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCR_MON_AMT, '20', DCRANNAMT);
EMP_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPAMTNUM ELSE 0;
EMPLR_HEALTH_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('HI 4','HI 3','HI 2','HI 1','HO 4','HO 3','HO 2','HO 1') THEN EMPYRAMTNUM ELSE 0;
EMP_DENTAL_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DH 1','DH 2','DH 3','DH 4','DB 1','DB 2','DB 3','DB 4') THEN EMPAMTNUM ELSE 0;
EMP_OPTLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('OL1','OL2','OL3','OL4','OL170','OL175','OL180','OL185','OL270','OL275','OL280','OL285','OL370','OL375','OL380','OL385','OL470','OL475','OL480','OL485') THEN EMPAMTNUM ELSE 0;
EMP_ADD_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('AD1','AD170','AD175','AD180','AD4','AD470','AD475','AD480') THEN EMPAMTNUM ELSE 0;
EMP_DEPLIFE_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('DL') THEN EMPAMTNUM ELSE 0;
EMP_SHTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('ST') THEN EMPAMTNUM ELSE 0;
EMP_LNGTERMDIS_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('LT') THEN EMPAMTNUM ELSE 0;
EMPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.HCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN HCRANNAMT ELSE 0;
DEPFLEX_ERS/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DCRFLAG EQ 'Y' AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.RECTYP EQ 'F' THEN DCRANNAMT ELSE 0;
SSN /A9V=EDIT(ASSN,'999999999');
BASIC_LIFE/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPAMTNUM ELSE 0;
BASIC_LIFE_EMPLR/P12.2=IF SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.PLNCD IN ('BL') AND SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.LINCD EQ '20' THEN EMPYRAMTNUM ELSE 0;
EMP_HEALTH_ERS2/P12.2=EMP_HEALTH_ERS + BASIC_LIFE;
EMPLYER_HEALTH_ERS2/P12.2=EMPLR_HEALTH_ERS + BASIC_LIFE_EMPLR;
END
TABLE FILE SFA_MONTHLY_ERS
SUM
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DENTAL_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_OPTLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_ADD_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_DEPLIFE_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_SHTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMP_LNGTERMDIS_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPLYER_HEALTH_ERS2
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.EMPFLEX_ERS
SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.DEPFLEX_ERS
BY LOWEST SSN
WHERE SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.COVDT EQ '&covdt2'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HLDERS2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.666667,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=1.500000,
$
ENDSTYLE
END
MATCH FILE HLDERS2
PRINT HLDERS2.HLDERS2.SSN HLDERS2.HLDERS2.EMP_HEALTH_ERS2 HLDERS2.HLDERS2.EMP_DENTAL_ERS HLDERS2.HLDERS2.EMP_OPTLIFE_ERS HLDERS2.HLDERS2.EMP_ADD_ERS HLDERS2.HLDERS2.EMP_DEPLIFE_ERS HLDERS2.HLDERS2.EMP_SHTERMDIS_ERS HLDERS2.HLDERS2.EMP_LNGTERMDIS_ERS HLDERS2.HLDERS2.EMPLYER_HEALTH_ERS2 HLDERS2.HLDERS2.EMPFLEX_ERS HLDERS2.HLDERS2.DEPFLEX_ERS
BY HLDERS2.HLDERS2.SSN
RUN
FILE HLDERS1
PRINT HLDERS1.HLDERS1.SSN HLDERS1.HLDERS1.EMP_HEALTH_ERS2 HLDERS1.HLDERS1.EMP_DENTAL_ERS HLDERS1.HLDERS1.EMP_OPTLIFE_ERS HLDERS1.HLDERS1.EMP_ADD_ERS HLDERS1.HLDERS1.EMP_DEPLIFE_ERS HLDERS1.HLDERS1.EMP_SHTERMDIS_ERS HLDERS1.HLDERS1.EMP_LNGTERMDIS_ERS HLDERS1.HLDERS1.EMPLYER_HEALTH_ERS2 HLDERS1.HLDERS1.EMPFLEX_ERS HLDERS1.HLDERS1.DEPFLEX_ERS
BY HLDERS1.HLDERS1.SSN
AFTER MATCH HOLD AS HLDERS OLD-OR-NEW
END

TABLE FILE HLDERS
SUM
HLDERS.HLDERS.SSN
HLDERS.HLDERS.EMP_HEALTH_ERS2
HLDERS.HLDERS.EMP_DENTAL_ERS
HLDERS.HLDERS.EMP_OPTLIFE_ERS
HLDERS.HLDERS.EMP_ADD_ERS
HLDERS.HLDERS.EMP_DEPLIFE_ERS
HLDERS.HLDERS.EMP_SHTERMDIS_ERS
HLDERS.HLDERS.EMP_LNGTERMDIS_ERS
HLDERS.HLDERS.EMPLYER_HEALTH_ERS2
HLDERS.HLDERS.EMPFLEX_ERS
HLDERS.HLDERS.DEPFLEX_ERS
BY HLDERS.HLDERS.SSN NOPRINT
ON TABLE NOTOTAL
END

Wouldn't it be easier if you hade month1 and month2 buckets and then in your define add the
/*
WHERE SFA_MONTHLY_ERS.SFA_MONTHLY_ERS.COVDT EQ '&covdt1' or = '&covdt2'
\*
logic and then do the rest of your comparisons and printing ?

ha, didnt think of that....let me try.

still doesnt explain why my MATCH didnt work though....

Thanks!

Well sometimes you need a demented mind to do this stuff.

By "depending which way" I presume you mean depending on whether month 1 is the OLD data source (and month 2 the NEW) in the Match, or vice versa.

If EMP_HEALTH_ERS2 for SSN 123-45-6789 is x dollars in the first month and y in the second, you are looking for x-y, but what you are getting is simply x. Here's why.

Look at your request:

MATCH FILE HLDERS2
PRINT
  HLDERS2.HLDERS2.SSN
  HLDERS2.HLDERS2.EMP_HEALTH_ERS2
  HLDERS2.HLDERS2.EMP_DENTAL_ERS
  HLDERS2.HLDERS2.EMP_OPTLIFE_ERS
  HLDERS2.HLDERS2.EMP_ADD_ERS
  HLDERS2.HLDERS2.EMP_DEPLIFE_ERS
  HLDERS2.HLDERS2.EMP_SHTERMDIS_ERS
  HLDERS2.HLDERS2.EMP_LNGTERMDIS_ERS
  HLDERS2.HLDERS2.EMPLYER_HEALTH_ERS2
  HLDERS2.HLDERS2.EMPFLEX_ERS
  HLDERS2.HLDERS2.DEPFLEX_ERS
BY HLDERS2.HLDERS2.SSN
RUN
FILE HLDERS1
PRINT
  HLDERS1.HLDERS1.SSN
  HLDERS1.HLDERS1.EMP_HEALTH_ERS2
  HLDERS1.HLDERS1.EMP_DENTAL_ERS
  HLDERS1.HLDERS1.EMP_OPTLIFE_ERS
  HLDERS1.HLDERS1.EMP_ADD_ERS
  HLDERS1.HLDERS1.EMP_DEPLIFE_ERS
  HLDERS1.HLDERS1.EMP_SHTERMDIS_ERS
  HLDERS1.HLDERS1.EMP_LNGTERMDIS_ERS
  HLDERS1.HLDERS1.EMPLYER_HEALTH_ERS2
  HLDERS1.HLDERS1.EMPFLEX_ERS
  HLDERS1.HLDERS1.DEPFLEX_ERS
BY HLDERS1.HLDERS1.SSN
AFTER MATCH HOLD AS HLDERS OLD-OR-NEW
END

TABLE FILE HLDERS
SUM
  HLDERS.HLDERS.SSN
  HLDERS.HLDERS.EMP_HEALTH_ERS2
  HLDERS.HLDERS.EMP_DENTAL_ERS
  HLDERS.HLDERS.EMP_OPTLIFE_ERS
  HLDERS.HLDERS.EMP_ADD_ERS
  HLDERS.HLDERS.EMP_DEPLIFE_ERS
  HLDERS.HLDERS.EMP_SHTERMDIS_ERS
  HLDERS.HLDERS.EMP_LNGTERMDIS_ERS
  HLDERS.HLDERS.EMPLYER_HEALTH_ERS2
  HLDERS.HLDERS.EMPFLEX_ERS
  HLDERS.HLDERS.DEPFLEX_ERS
BY HLDERS.HLDERS.SSN NOPRINT
ON TABLE NOTOTAL
END

The fieldnames in both legs of the match (disregarding file and segment qualification) are identical. The hold file produced by the MATCH will have the following 1+11+11=23 fieldnames (and aliases):

SSN                      E01  *
SSN                      E02
EMP_HEALTH_ERS2          E03  *
EMP_DENTAL_ERS           E04  *
EMP_OPTLIFE_ERS          E05  *
EMP_ADD_ERS              E06  *
EMP_DEPLIFE_ERS          E07  *
EMP_SHTERMDIS_ERS        E08  *
EMP_LNGTERMDIS_ERS       E09  *
EMPLYER_HEALTH_ERS2      E10  *
EMPFLEX_ERS              E11  *
DEPFLEX_ERS              E12  *
SSN                      E13
EMP_HEALTH_ERS2          E14
EMP_DENTAL_ERS           E15
EMP_OPTLIFE_ERS          E16
EMP_ADD_ERS              E17
EMP_DEPLIFE_ERS          E18
EMP_SHTERMDIS_ERS        E19
EMP_LNGTERMDIS_ERS       E20
EMPLYER_HEALTH_ERS2      E21
EMPFLEX_ERS              E22
DEPFLEX_ERS              E23

All the fieldnames in the final TABLE request (again disregarding qualification) are ambiguous (each distinct fieldname occurs two or more times in the hold file's column list). The fieldnames occuring in the TABLE request are interpreted as referring to the first matching fieldname (the ones I asterisked). So your final report has a line for every every SSN that has a data row in either month, but the statistics reflect only one month (the OLD leg of the Match), since the final 11 columns, representaing the stats for the other month are inaccessible.

Here's what I think you are looking for:

MATCH FILE HLDERS2
SUM
  EMP_HEALTH_ERS2        AS X_EMP_HEALTH_ERS2
  EMP_DENTAL_ERS         AS X_EMP_DENTAL_ERS
  EMP_OPTLIFE_ERS        AS X_EMP_OPTLIFE_ERS
  EMP_ADD_ERS            AS X_EMP_ADD_ERS
  EMP_DEPLIFE_ERS        AS X_EMP_DEPLIFE_ERS
  EMP_SHTERMDIS_ERS      AS X_EMP_SHTERMDIS_ERS
  EMP_LNGTERMDIS_ERS     AS X_EMP_LNGTERMDIS_ERS
  EMPLYER_HEALTH_ERS2    AS X_EMPLYER_HEALTH_ERS2
  EMPFLEX_ERS            AS X_EMPFLEX_ERS
  DEPFLEX_ERS            AS X_DEPFLEX_ERS
BY SSN
RUN
FILE HLDERS1
SUM
  EMP_HEALTH_ERS2        AS Y_EMP_HEALTH_ERS2
  EMP_DENTAL_ERS         AS Y_EMP_DENTAL_ERS
  EMP_OPTLIFE_ERS        AS Y_EMP_OPTLIFE_ERS
  EMP_ADD_ERS            AS Y_EMP_ADD_ERS
  EMP_DEPLIFE_ERS        AS Y_EMP_DEPLIFE_ERS
  EMP_SHTERMDIS_ERS      AS Y_EMP_SHTERMDIS_ERS
  EMP_LNGTERMDIS_ERS     AS Y_EMP_LNGTERMDIS_ERS
  EMPLYER_HEALTH_ERS2    AS Y_EMPLYER_HEALTH_ERS2
  EMPFLEY_ERS            AS X_EMPFLEX_ERS
  DEPFLEY_ERS            AS X_DEPFLEX_ERS
BY SSN
AFTER MATCH HOLD AS HLDERS OLD-OR-NEW
END

TABLE FILE HLDERS
SUM
  COMPUTE DIF_EMP_HEALTH_ERS2        = X_EMP_HEALTH_ERS2     - Y_EMP_HEALTH_ERS2        ;
  COMPUTE DIF_EMP_DENTAL_ERS         = X_EMP_DENTAL_ERS      - Y_EMP_DENTAL_ERS         ;
  COMPUTE DIF_EMP_OPTLIFE_ERS        = X_EMP_OPTLIFE_ERS     - Y_EMP_OPTLIFE_ERS        ;
  COMPUTE DIF_EMP_ADD_ERS            = X_EMP_ADD_ERS         - Y_EMP_ADD_ERS            ;
  COMPUTE DIF_EMP_DEPLIFE_ERS        = X_EMP_DEPLIFE_ERS     - Y_EMP_DEPLIFE_ERS        ;
  COMPUTE DIF_EMP_SHTERMDIS_ERS      = X_EMP_SHTERMDIS_ERS   - Y_EMP_SHTERMDIS_ERS      ;
  COMPUTE DIF_EMP_LNGTERMDIS_ERS     = X_EMP_LNGTERMDIS_ERS  - Y_EMP_LNGTERMDIS_ERS     ;
  COMPUTE DIF_EMPLYER_HEALTH_ERS2    = X_EMPLYER_HEALTH_ERS2 - Y_EMPLYER_HEALTH_ERS2    ;
  COMPUTE DIF_EMPFLEX_ERS            = X_EMPFLEX_ERS         - Y_EMPFLEX_ERS            ;
  COMPUTE DIF_DEPFLEX_ERS            = X_DEPFLEX_ERS         - Y_DEPFLEX_ERS            ;
BY SSN
END