Prev: duplicate records data into single row
Next: SASUSER.TEMPLAT
From: Sdlentertd on 26 Jan 2010 16:53
Have
Table 1:

Index NCP1 rx1 NCP2 rx2 NCP3 rx3
aaaaa 5465 1111 5465 1222 5465 1223
bbbbb 8889 1225 8889 1555 8889 1545

Table2:
NCP RX OTHERINFO
5465 1111 red
5465 1222 yellow
5465 3333 blue
8889 1225 red
8889 1555 yellow
8889 3323 green

Want to have
Compare Table 1 to Table 2 and add OtherINFo stuff into the table
based on matching BOTH fields NCP and RX

Table_want:
Index NCP RX OTHERINFO
aaaa 5465 1111 red
aaaa 5465 1222 yellow
bbbb 8889 1225 red
bbbb 8889 1555 yellow


Proc sql;
create table output as
select
a1.Index ,
a2.ncp,
a2.rx,
a2.otherinfo
from table1 as a1 left join
table2 as a2 on a1.ncp1 = a2.ncp and
a1.rx1 = a2.rx
here I don't know how to put this into the code:
or A1.ncp2 = A2.ncp and
A1.rx2 = A2.rx
or A1.ncp3 = A2.ncp and
A1.rx3 = A2.rx
and whatever doesn't match out put it into a seperate table....
Table_junk:
Index NCP RX
aaaa 5465 1223
bbbb 8889 1545



From: Arthur Tabachneck on 26 Jan 2010 19:04
I'm confused! As far as I can tell your code already outputs the file you
wanted (except that you labeled it "output" rather than "want").

There may be an easier way, but you could get the combinations that don't
match your on statement by simply wrapping it in "not ( .... )" But that
produces a number of combinations that aren't in your example.

e.g., try the following:

data Table1;
input Index $ NCP1 rx1 NCP2 rx2 NCP3 rx3;
cards;
aaaaa 5465 1111 5465 1222 5465 1223
bbbbb 8889 1225 8889 1555 8889 1545
;

data Table2;
input NCP RX OTHERINFO $;
cards;
5465 1111 red
5465 1222 yellow
5465 3333 blue
8889 1225 red
8889 1555 yellow
8889 3323 green
;

Proc sql;
create table want as
select
a1.Index ,
a2.ncp,
a2.rx,
a2.otherinfo
from table1 as a1 left join
table2 as a2 on a1.ncp1 = a2.ncp and
a1.rx1 = a2.rx
or A1.ncp2 = A2.ncp and
A1.rx2 = A2.rx
or A1.ncp3 = A2.ncp and
A1.rx3 = A2.rx
;
create table others as
select
a1.Index ,
a2.ncp,
a2.rx,
a2.otherinfo
from table1 as a1 left join
table2 as a2 on not (
a1.ncp1 = a2.ncp and
a1.rx1 = a2.rx
or A1.ncp2 = A2.ncp and
A1.rx2 = A2.rx
or A1.ncp3 = A2.ncp and
A1.rx3 = A2.rx
)
;
quit;

HTH,
Art
---------
On Tue, 26 Jan 2010 13:53:33 -0800, Sdlentertd <sdlentertd(a)GMAIL.COM>
wrote:

>Have
>Table 1:
>
>Index NCP1 rx1 NCP2 rx2 NCP3 rx3
>aaaaa 5465 1111 5465 1222 5465 1223
>bbbbb 8889 1225 8889 1555 8889 1545
>
>Table2:
>NCP RX OTHERINFO
>5465 1111 red
>5465 1222 yellow
>5465 3333 blue
>8889 1225 red
>8889 1555 yellow
>8889 3323 green
>
>Want to have
>Compare Table 1 to Table 2 and add OtherINFo stuff into the table
>based on matching BOTH fields NCP and RX
>
>Table_want:
>Index NCP RX OTHERINFO
>aaaa 5465 1111 red
>aaaa 5465 1222 yellow
>bbbb 8889 1225 red
>bbbb 8889 1555 yellow
>
>
>Proc sql;
>create table output as
>select
> a1.Index ,
>a2.ncp,
>a2.rx,
>a2.otherinfo
>from table1 as a1 left join
> table2 as a2 on a1.ncp1 = a2.ncp and
> a1.rx1 = a2.rx
>here I don't know how to put this into the code:
> or A1.ncp2 = A2.ncp and
> A1.rx2 = A2.rx
> or A1.ncp3 = A2.ncp and
> A1.rx3 = A2.rx
>and whatever doesn't match out put it into a seperate table....
>Table_junk:
>Index NCP RX
>aaaa 5465 1223
>bbbb 8889 1545
 | 
Pages: 1
Prev: duplicate records data into single row
Next: SASUSER.TEMPLAT