PROC SQL delete from oracle table

[pjagathis]

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

[RW9]

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.

[pjagathis]

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

[SASKiwi]

Your code looks to have correct syntax.

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

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.

[RW9]

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