Sql to proc sql conversion

Question

I have 2 tables that contains var values like (ID, date, var1,var2,var3....)

I need to get the data from table2 and add it to table1 for which (ID or date) does not exist in table1.

I am using the below code in sql to get new ID's from tab2 to tab1:

INSERT INTO table1
SELECT * FROM table2 a
WHERE ID not in(select ID from table1 where ID=a.ID)

Here is the code to add new date for existing ID's in tab2 to tab1 :

INSERT INTO table1
    SELECT * FROM table2 a
    WHERE date not in(select date from table1 where ID=a.ID)

I don't know how to do this in proc sql. Please share an effective method to do this task.

To insert the new ID I used:

proc sql;
create table lookup as
select a.ID
from table1 a inner join table2 b
  on a.ID = b.ID
;
insert into table1 
select * from table2 a
where a.ID not in (select ID from lookup)
;
quit;

This works well. But, it failed to insert a date for existing IDs. Please suggest some ideas to complete this step. Thanks in advance!

I'm a little confused, do the first two pieces of SQL work correctly? If yes, then you can just put them in between proc sql; and quit;, ending each one with a semicolon. — Amir
There are typos (missing spaces) in "where a.IDnot in (select IDfrom lookup)" — FloT

satish kumar Parida satish kumar Parida · Accepted Answer · 2019-03-16T12:15:53

SAS SQL is Similar to the SQl you have written.

The SAME insert statements can be warped as proc sqls in SAS and they work like charm.

If your SQL did work then the following will work too.

PROC SQL;
    INSERT INTO work.table1
    SELECT * FROM work.table2 a
    WHERE ID not in(select ID from work.table1 where ID=a.ID);

    INSERT INTO work.table1
    SELECT * FROM work.table2 a
    WHERE date not in(select date from work.table1 where ID=a.ID)
QUIT;

Sql to proc sql conversion

1 Answers