Help using Base SAS procedures

Insert into confusion

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 238
Accepted Solution

Insert into confusion


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;


Accepted Solutions
Solution
‎03-11-2014 06:57 AM
Super User
Super User
Posts: 7,392

Re: Insert into confusion

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;

View solution in original post


All Replies
Super User
Posts: 17,737

Re: Insert into confusion

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;

Regular Contributor
Regular Contributor
Posts: 238

Re: Insert into confusion

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.

Solution
‎03-11-2014 06:57 AM
Super User
Super User
Posts: 7,392

Re: Insert into confusion

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;

Regular Contributor
Regular Contributor
Posts: 238

Re: Insert into confusion


Thanks. These are both great options.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 348 views
  • 0 likes
  • 3 in conversation