Insert into using where before select

[David_Billa]

Could you please help me understand why the below INSERT INTO code is not working as excepted? I know that it will work if I code the WHERE after select clause but I would like to know why it is not working if I code before SELECT. How to make this work?

/* dataset 1*/
data have1;
input name $ age;
datalines;
bill 20
sam 10
ram 30
;
/* dataset 2*/
data have2;
input name $ age;
datalines;
bill 20
raj  40

;
/* create a temp table*/
proc sql;
create table have3 as
select * from have1
where trim(name) not in(select trim(name) from have2);
quit;

/* then insert into final table only age eq 20*/
proc sql;
insert into have2(where=(age=20))
select * from have3;
quit;

[Pmyosh]

This way you are applying the filter to Have2 and not Have3. 

And since you can't filter Have2 using INSERT INTO, no action is done

[David_Billa]

I don't think that's right. If I slightly Change the filter, it is still not working yet.

proc sql;
insert into have2(where=(age=20))
select * from have3;
quit;


proc sql;
select * from have2;
quit;

[Pmyosh]

Can you paste here the new filter that you applied?

In your new code/post, the filter is the same

[David_Billa]

This is the new filter. Indeed I edited the new post.

proc sql;
insert into have2(where=(age=20))
select * from have3;
quit;

[ed_sas_member]

Hi @David_Billa 

Why don't you use the set operator 'union' to achieve this. It enables you to combine the results of two SQL queries into a single table as follows:

proc sql;
	select * from have2 where age=20
	union
	select * from have3;
quit;

[David_Billa]

Thanks. But I no need any other way as the code which I have mentioned in the post is I get from DI Studio. I would like to know why it's not working and how to make that method works.

[Kurt_Bremser]

Insert into works on the physical table as it exists; it does never change any data that is already present, it only appends, therefore the where= dataset option is ignored.

IMO, using a where= dataset option in this way should result in a WARNING, as it can't work by principle.

[David_Billa]

There is no warning as you can see in the log below.

26         proc sql;
27         insert into have2(where=(age=20))
28         select * from have3;
NOTE: 2 rows were inserted into WORK.HAVE2.

29         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

[Kurt_Bremser]

---

@David_Billa wrote:

There is no warning as you can see in the log below.

26         proc sql;
27         insert into have2(where=(age=20))
28         select * from have3;
NOTE: 2 rows were inserted into WORK.HAVE2.

29         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

---

Yes, and I consider that a bug. In your position, I would bring this to the attention of SAS technical support. Especially when you can create such in principle invalid code with one of the GUI clients. The client should not allow you to do it, that's its function (IMO) as a support for those who can't (or don't want) to write the code themselves.

PS the code given by @ed_sas_member is the valid method for this action, and it is the one that you should create (or be able to create) with DI Studio.

But there will always be things where the pointy-clicky helpers can't keep up with the good ol' Brain Mk 1. That's why I write my codes, and don't have them written.

[Tom · Accepted solution]

Please explain better what you are trying to do.

Code with dataset options on the target of the INSERT makes no sense.  If you only want to insert observations where AGE=20 then apply that to the code that is selecting which observations to insert.  Either of these methods would work.

insert into want
select * from have where age=20
;
insert into want
select * from have(where=(age=20))
;

If you want to eliminate observations from the target dataset then you need to use a DELETE statement and not an INSERT statement.

delete from want where age=20;