topic PROC SQL delete from oracle table in SAS Procedures

PROC SQL delete from oracle table

pjagathis — Mon, 11 Aug 2014 13:02:06 GMT

Hi All,

I need to delete few records in an oracle table from a SAS program directly.

My program is below,

libname abc oracle path=path1

schema=schema1 user=user11 password=XXXXXXXX readbuff=10000;

proc sql;

delete from abc.table1 where flag='T';

quit;

Is the above logic will work?

Do we need to give commit statement?

Thanks

Jagathis

Re: PROC SQL delete from oracle table

RW9 — Mon, 11 Aug 2014 13:30:41 GMT

TBH I have not used the libname statement to do these things. I would write it (may need to check syntax):

proc sql;

connect to oracle (...);

execute by oracle(delete ...);

execute by oracle(commit);

quit;

Can check the operation in Toad or Oracel Dev to see what works, then copy the code over into the SQL statement. Other questions include: do you have permission to do this? Is this a wise thing to be doing - I would suggest that removing data would be better off going through the OC system rather than via third party.

Re: PROC SQL delete from oracle table

pjagathis — Mon, 11 Aug 2014 14:40:08 GMT

Hi RW9,

Yes... I have access to do this activity.

I am not sure, whether this way will be an efficient one, and so i posted here to get the suggestion on this.

What is OC?

Thanks

Jagathis

Re: PROC SQL delete from oracle table

SASKiwi — Mon, 11 Aug 2014 19:40:24 GMT

Your code looks to have correct syntax.

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0wvt1t0egww81n1nrdqjvc32oyl.htm

There are SAS options to control COMMITS but usually experimentation is required to optimise the right number. The RW9 solution may give you better control.

Re: PROC SQL delete from oracle table

RW9 — Tue, 12 Aug 2014 08:21:37 GMT

Yes, OC=Oracle Clinical, should be the same for just Oracle though.