0

In T-SQL I used to be able to do the following:

delete t1
from table1 t1
join table2 t2 on t1.rowid = t2.rowid and t1.value <> t2.value

I'd like to be able to do the same in SAS.

taking the code above and wrapping in proc sql; and quit; throws a syntax error.

Is below my only option ?

proc sql;
delete from table1 t1
where t1.value <> (select t2.value from table2 t2 where t1.rowid = t2.rowid) 
and t1.rowid in (select t2.rowid from table t2);
quit;

Thank you.

ple103
  • 2,080
  • 6
  • 35
  • 50
Ben
  • 485
  • 9
  • 19

1 Answers1

1

So you have probably figured out, that delete is not very efficient.

If you have the disk space, I would recommend just creating a new table based on the inner join (the records you want), drop table1, and rename the results table1.

%let n=1000000;

data table1;
do rowid=1 to &n;
    value = rowid**2;
    output;
end;
run;

data table2;
do rowid=1 to &n;
    value = (mod(rowid,2)=1)*rowid**2;
    output;
end;
run;

proc sql noprint;
create table table1_new as
select a.*
    from table1 as a
      inner join
         table2 as b
      on a.rowid=b.rowid
       and 
         a.value = b.value;

drop table table1;

quit;

proc datasets lib=work nolist;
change table1_new = table1;
run;
quit;
DomPazz
  • 12,415
  • 17
  • 23
  • If you need something that edits in place, let me know and explain why. The why will help determine the best path to do it. – DomPazz Mar 22 '17 at 00:57