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;
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;
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;
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.
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;
Thanks. These are both great options.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.