Help using Base SAS procedures

PROC SQL delete from oracle table

Reply
New Contributor
Posts: 4

PROC SQL delete from oracle table

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

Super User
Super User
Posts: 7,942

Re: PROC SQL delete from oracle table

Posted in reply to pjagathis

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.

New Contributor
Posts: 4

Re: PROC SQL delete from oracle table

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

Super User
Posts: 3,250

Re: PROC SQL delete from oracle table

Posted in reply to pjagathis

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.

Super User
Super User
Posts: 7,942

Re: PROC SQL delete from oracle table

Posted in reply to pjagathis

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

Ask a Question
Discussion stats
  • 4 replies
  • 2000 views
  • 0 likes
  • 3 in conversation