Hi all,

I have two hold files: HOLD1, HOLD2 which contains some IDs of HOLD1. After merging, the file should have all data of HOLD1, plus some data of HOLD2 which its IDs are in HOLD1.

Example:

HOLD1:
ID  DATE        AMOUNT
11  09/30/2008  100
22  09/30/2008  200
33  09/30/2008  300

HOLD2:
ID  DATE        AMOUNT
11  08/31/2008  110
12  08/31/2008  120
13  08/31/2008  123 
22  08/31/2008  234
33  08/31/2008  333
  
The result needs to be:

ID  DATE        AMOUNT
11  09/30/2008  100
11  08/31/2008  110
22  09/30/2008  200
22  08/31/2008  234
33  09/30/2008  300
33  08/31/2008  333

Thank you,
Pku

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

You can accomplish it with Match File (two merges, three data sources) as sketched below:

file hold1: by ID
file hold2: by id by date by amount
hold old-and-new
run
(yields the rows of hold2 with matching ID in hold1)

file hold1: by id by date by amount
hold old-or-new
end
(merges any rows of hold1 not already obtained from hold2)

One way:

  
DEFINE FILE HOLD1
H1/A1 = '1';
END

MATCH FILE HOLD1
PRINT DATE AS DATE1 AMOUNT AS AMOUNT1 H1
BY ID
RUN
FILE HOLD2
PRINT DATE AS DATE2 AMOUNT AS AMOUNT2  
BY ID
AFTER MATCH HOLD AS HOLD3 OLD
END
-RUN

DEFINE FILE HOLD3
DATE/MDYY = IF H1 EQ '1' THEN DATE1 ELSE DATE2;
AMOUNT/I9 = IF H1 EQ '1' THEN AMOUNT1 ELSE AMOUNT2;
END

TABLE FILE HOLD3
PRINT ID DATE AMOUNT
END

I like the alternatives that you provided Jack. Afterall, anything is possible...

Thanks, Jack and Microfich.

I used Jack's way with a bit of modification since the IDs could not be duplicated.
After matching HOLD2 to HOLD1 as HOLD3 as "OLD-AND-NEW". Then, I used "MORE" to merge HOLD1 back to HOLD3 to get the result.

  
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS FINAL
MORE
FILE HOLD3
END

Thanks again,
Pku