BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6


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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
Reeza
Super User

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
Fluorite | Level 6 tmm
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6 tmm
Fluorite | Level 6


Thanks. These are both great options.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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