BookmarkSubscribeRSS Feed
pjagathis
Calcite | Level 5

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
PROC Star

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
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 5270 views
  • 0 likes
  • 3 in conversation