Proc sql insert into -by a variable

Reply
Contributor
Posts: 66

Proc sql insert into -by a variable


I have two data set aa and bb as follows

DATA AA;
INPUT a b @@;
DATALINES;
1 2 3 4 5 6 7 8
;
RUN;
DATA BB;
INPUT a b @@;
DATALINES;
5 4 7 6 9 10 11 12
;
RUN;

Now I want to insert those rows of  data set bb into aa which are not in data set aa by variable a. i.e I want to insert those rows from bb where a from bb is not in a from aa.

This is my attempt -that does not work.

PROC SQL;
INSERT INTO AA(a,b)
SELECT a, b
FROM BB

where a not in (select a from aa);
QUIT;

Any help would be highly appreciated!

Trusted Advisor
Posts: 1,204

Re: Proc sql insert into -by a variable

First you need to create two tables AAA and BBB which are mirror copies of AA and BB. With your code it is not possible to access and update the same table at the same time. You can rename AAA and BBB again as AA and BB after processing.

Proc sql;
create table aaa as
select * from aa;
create table bbb as
select * from bb;
quit;

PROC SQL;
INSERT INTO AAA
SELECT *
FROM BB
where a not in (select a from aa);
INSERT INTO BBB
SELECT *
FROM AA
where a not in (select a from bb);
quit;

proc sql;
select * from aaa;
select * from bbb;
quit;

SAS Employee
Posts: 3

Re: Proc sql insert into -by a variable

DATA WORK.AA;

  IF 0 THEN MODIFY WORK.AA;

  SET WORK.BB;

 

 

  IF _N_=1 THEN DO;

    DCL Hash _h(DATASET:'WORK.AA');

    _h.defineKey('a');

    _h.defineDone();

  END;

 

  IF _h.Check() THEN

    OUTPUT;

RUN;

Super User
Super User
Posts: 7,413

Re: Proc sql insert into -by a variable

Not sure I am following you exactly.  So do you want a row inserted based only on a or do you want a combination, or in fact do you want two rows insert.  So in your example the number 9 in a has 10 also on that row, do you want 1 row inserted into aa or severa:

1     2

3     4

5     6

7     8

9     10     -< only one ignore that 10 is not in the original

Or

...

7     8

9     10

9     10     -< Additional record as 10 is not in orignal

If you only want one then set the two together and sort nodupkey:

data cc;

  set aa bb;

run;

proc sort data=cc nodupkey;

  by a;

run;

Maybe provide example output to clarify.

Super User
Posts: 9,687

Re: Proc sql insert into -by a variable

From you error log. It seems that you can't open table AA twice in INSERT . and how about make a temporary table ?

DATA AA;
INPUT a b @@;
DATALINES;
1 2 3 4 5 6 7 8
;
RUN;
DATA BB;
INPUT a b @@;
DATALINES;
5 4 7 6 9 10 11 12
;
RUN;

PROC SQL;
create table temp as
SELECT a, b
FROM BB
where a not in (select a from aa );

INSERT INTO AA(a,b)
 select * from temp;
QUIT;

Xia Keshan

Ask a Question
Discussion stats
  • 4 replies
  • 326 views
  • 0 likes
  • 5 in conversation