updating from multiple table

[Rohit_1990]

Hi All,

i have four datasets as follows

data one

col1 cls_id
1 a
2 c
3 a

data two

col1 cls_id
1 d
6 e
8 d

data three

col1 cls_id
7 h
9 f
8 h

data four

col1 cls_id
10 m
5 l
9 m

Now from these tables i need to create a final table so that each common element from these tables have a common cls_id
and all associated elements to that common element in different table also do have same common cls_id.

* FYI Actually i was running optnet to link elements but because of data size i am not able to run in a single go.
Hence,I had to split the data and run optnet and try to merge these four tables .

In table one and two element 1 is common and 8 is a associated element further 8 is also present in table 3 with
7 attached to it.

hence 1,3,7,8 should share same cluster id

similarly 9 and 10 should have same id

Any help would be greatly appreciated.

Regards

[tomrvincent]

So you want up to 4 separate cls_id columns per col1 or up to 4 cls_id records per col1?

[PGStats]

So, what should the "final table" be for these data?

[Rohit_1990]

the final table should be something like this

col1    final_cls_id

1            h

2            c 

3            h

5            l  

6            e

7            h

8            h

9            m

10          m

so since value 1 in  col1 indifferent tables i associated to 3 & 8  and 8 is again attached to 7 as they have same cls_id in different tables.

Hence I have assigned same cls_id to all these values.

I have a table which contains 70 million records but I am able to run proc optnet on some 15 million records only . Is there any way to run optnet based on group by condition.

Thanks and Regards

[PGStats]

Are you using the concomp algorithm? What do you use as node labels (IDs)?

[Rohit_1990]

Yes I am using concomp algorithm

[PGStats]

What do you use as node labels (IDs). Numbers or character strings?

Please post your optnet code.

[Rohit_1990]

I am using number strings