topic Re: updating from multiple table in SAS Programming

updating from multiple table

Rohit_1990 — Wed, 02 Oct 2019 17:27:22 GMT

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

Re: updating from multiple table

tomrvincent — Wed, 02 Oct 2019 18:14:39 GMT

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

Re: updating from multiple table

PGStats — Wed, 02 Oct 2019 18:20:02 GMT

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

Re: updating from multiple table

Rohit_1990 — Thu, 03 Oct 2019 03:16:04 GMT

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

Re: updating from multiple table

PGStats — Thu, 03 Oct 2019 03:47:26 GMT

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

Re: updating from multiple table

Rohit_1990 — Thu, 03 Oct 2019 06:04:32 GMT

Yes I am using concomp algorithm

Re: updating from multiple table

PGStats — Thu, 03 Oct 2019 20:56:37 GMT

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

Please post your optnet code.

Re: updating from multiple table

Rohit_1990 — Fri, 04 Oct 2019 13:05:11 GMT

I am using number strings