Posted by menglin.cao on 03/08/06 21:04

Hi,

I am trying to join two tables, one has multiple records per ID, the
other is unique record per ID.
The two tables look like below
A
ID date var1 var 2
001 1/1 10 20
001 2/1 12 15
001 3/1 17 18
002 2/1 13 10
002 3/1 12 14
............

B
ID
001
002
003
004
....

The join conditions are
1. table A's ID = table B's ID
2. take the variables associated with the 1st occrrence of the date
variable in table A's

I have the following SQL code but the it didn't work. It says the
columns are ambiguously defined. Anyone can help me? Greatly appreciate
it!

PROC SQL;
CONNECT TO ORACLE (USER="&user" PASS="&pass" PATH="@POWH17"
BUFFSIZE=25000);

CREATE TABLE actvy1_1st AS SELECT * FROM CONNECTION TO ORACLE
(
SELECT
actvy1.ID,
actvy1.var1,
actvy1.var2,
actvy1.date

from
A actvy1,
(select a.ID,
min(a.date) mindate
from A a,
B c
where a.ID =c.ID
group by ID
) b

where actvy1.ID =b.ID
and actvy1.date =b.mindate
order by ID
);
disconnect from oracle;
quit;

[Back to original message]