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
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.
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
I merged the posts.
Use proc append:
proc append
data=staging.invoice
base=cleaning.invoice
;
run;
PROC SQL;
INSERT INTO Cleaning.Invoice(SELECT * FROM Staging.Invoice) ;
quit;
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.
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.