creating and modifying the control table based on exact match

[forumsguy]

Hi All,

I have source table, target table and control table.

In first run, my target table will be loaded completely, but in second run only new records should be loaded and duplicate records should be loaded to control table. How to implement that ?? Need some help.

Source table run1

var1	var2	var3
10	20	30
40	50	60

Target table run1

var1	var2	var3
10	20	30
40	50	60

Control table run1

Job_name	Status	var_List	No of rows	Date	Duplicates
Sample	Success	var1,var2,var3	2	17-Jul-14	N

Now in run2 Source is

var1	var2	var3
50	60	70
40	50	60

Target should be

var1	var2	var3
10	20	30
40	50	60
50	60	70

Control table should be

Job_name	Status	var_List	No of rows	Date	Duplicates
Sample	Success	var1,var2,var3	2	17-Jul-14	N
Sample	Success	var1,var2,var3	1	17-Jul-14	Y

Any help is highly appreciated

[RW9]

Why not just load the dataset as normal then do a proc sort nodupkey dupout=?  E.g. This would then give you a table with distinct rows and a table with the duplicates.  You can then post-process the duplicates to get your control dataset.