topic Re: Insert into confusion in SAS Procedures

Insert into confusion

tmm — Mon, 10 Mar 2014 19:23:23 GMT

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;

Re: Insert into confusion

Reeza — Mon, 10 Mar 2014 19:42:33 GMT

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;

Re: Insert into confusion

tmm — Mon, 10 Mar 2014 20:13:36 GMT

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.

Re: Insert into confusion

RW9 — Tue, 11 Mar 2014 10:57:29 GMT

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;

Re: Insert into confusion

tmm — Tue, 11 Mar 2014 13:13:57 GMT

Thanks. These are both great options.