BookmarkSubscribeRSS Feed
aclaraujo
Fluorite | Level 6

Hi everyone,

 

How to performer a begin/commit/rollback operation for two or more steps in proc sql?

 

For example:

 

 

proc sql;


connect to oracle as ora(...); **begin transaction insert into ora.a; insert into ora.b; delete from ora.c; **commit transaction for success or **rollback transaction for fail quit;

 

Thanks,

...

5 REPLIES 5
LinusH
Tourmaline | Level 20
Don't think that's possible, at least when using libname access. Commit is sent at least after each statement.
Rollback is managed by the COMMIT and ERRLIMIT options.
What is your use case?
Data never sleeps
aclaraujo
Fluorite | Level 6

Hi, LinusH

 

Thanks for your attention!

 

I have a linked_people (id_people1, id_people2) table and a history_people (id_people, operation_date) table. Everytime when insert a row in people_linked table, have be inserted two rows in history_people table.
This operation need be atomic.

SASKiwi
Opal | Level 21

Just wrap the code you would use in Oracle PL-SQL, including COMMIT and ROLLBACK commands, in an EXECUTE statement:

proc sql;connect to oracle as ora(...);

execute(

**begin transaction

insert into ora.a;

insert into ora.b;

delete from ora.c;

**commit transaction for success

or

**rollback transaction for fail
) by ora;

quit;
aclaraujo
Fluorite | Level 6

Hi SASKiwi, 

Thanks for response!

 

But, the problem is source table is a sas dataset. 

LinusH
Tourmaline | Level 20
Then you (just) need to upload those tables first.
Data never sleeps

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2057 views
  • 1 like
  • 3 in conversation