BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shahsn11
Fluorite | Level 6

Hi,

 

I am trying to insert the data from 1 lib to another using the below SAS code

 

PROC SQL;
INSERT INTO Cleaning.Invoice 
SELECT * FROM Staging.Invoice;
quit;

I am getting the below error message

 

ERROR: You cannot reopen CLEANING.INVOICE.DATA for update access with member-level control because CLEANING.INVOICE.DATA is in use 
 by you in resource environment SQL (2).

 ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. 
        This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.

Metadata for both Invoice is same

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

create a lookup table for the billto variable:

proc sort
  data=cleaning.invoice (keep=billto)
  out=lookup
  nodupkey
;
by billto;
run;

and then prepare the dataset for appending by joining with that.

View solution in original post

5 REPLIES 5
shahsn11
Fluorite | Level 6

I am trying to insert the data from 1 lib to another using the below SAS code

 

PROC SQL;
INSERT INTO Cleaning.Invoice 
SELECT * FROM Staging.Invoice;
quit;


I am getting the below error message

 

 

ERROR: You cannot reopen CLEANING.INVOICE.DATA for update access with member-level control because CLEANING.INVOICE.DATA is in use 
by you in resource environment SQL (2).

ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. 
This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.


Metadata for both Invoice is same

AnilKumar_vDoIT
Calcite | Level 5
PROC SQL;
INSERT INTO Cleaning.Invoice(SELECT * FROM Staging.Invoice) ; 
quit;
shahsn11
Fluorite | Level 6

Thanks Kurt and Anil for the solution.

 

Anil unfortunately the solution propose by you is not working for me. It is giving error message like below. I see that there is no scope to improve the syntax error.

 

ERROR 22-322: Syntax error, expecting one of the following: ), ','. 

Kurt yours solution is working fine but i need to apply a where condition on staging.invoice which i cannot apply using proc append. I am looking for something like

PROC SQL;
INSERT INTO Cleaning.Invoice(SELECT * FROM Staging.Invoice where billto in (select billto from Cleaning.invoice)) ; 
quit;

Thanks.

Kurt_Bremser
Super User

create a lookup table for the billto variable:

proc sort
  data=cleaning.invoice (keep=billto)
  out=lookup
  nodupkey
;
by billto;
run;

and then prepare the dataset for appending by joining with that.

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
  • 596 views
  • 2 likes
  • 3 in conversation