topic Re: Inserting data from 1 Lib to another in SAS Procedures

Inserting data from 1 Lib to another

shahsn11 — Thu, 02 Aug 2018 05:27:49 GMT

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

Inserting data from one library to another

shahsn11 — Thu, 02 Aug 2018 05:39:43 GMT

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

Re: Inserting data from 1 Lib to another

Kurt_Bremser — Thu, 02 Aug 2018 05:46:13 GMT

I merged the posts.

Use proc append:

proc append
  data=staging.invoice
  base=cleaning.invoice
;
run;

Re: Inserting data from 1 Lib to another

AnilKumar_vDoIT — Thu, 02 Aug 2018 06:13:10 GMT

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

Re: Inserting data from 1 Lib to another

shahsn11 — Thu, 02 Aug 2018 17:40:38 GMT

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.

Re: Inserting data from 1 Lib to another

Kurt_Bremser — Thu, 02 Aug 2018 17:50:06 GMT

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.