Insert into confusion

[tmm]

I am a little confused with my insert into code. I have 2 datasets and want to keep all of the first dataset and only move those records in the second dataset that are not in the first.

proc sql;

insert into first a

select * from second b

where not exists (select 1 from first a where a.strata=b.strata);

run;

[Reeza]

I think  your where condition is incorrect.

One way to test results before proceeding is to select first and then once you have that working, insert.

ie. Get this part working first:

proc sql;

select * from second b

where not exists (select 1 from first a where a.strata=b.strata);

run;

[tmm]

The where works when I run that alone, it is when adding the insert to that I get an error:

You cannot reopen first for update access with member-level control because first is in use by you in resource environment SQL.

I know that means that since the table exists in my library that I cannot insert into it if using it in this query. So, I bet Ihave to select the records that where not exist and then append to first.

[RW9 · Accepted solution]

Hi,

Yes, I don't think you can subquery a table which is already being processed.  Try a join and coalesce (which takes first non-nul value):

proc sql;

  create table WANT as

  select  COALESCE(A.STRATA,B.STRATA) as STRATA

  from    FIRST A

  full join SECOND B

  on      A.STRATA=B.STRATA;

quit;

Alternatively you could adopt a two step approach such as:

proc sql;
  create table TMP as
  select  *
  from    SECOND
  where   STRATA not in (select distinct STRATA from FIRST);
  insert into FIRST
  select  *
  from    TMP;
quit;

[tmm]

Thanks. These are both great options.