BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
singhsahab
Lapis Lazuli | Level 10

Hi All,

 

I need to copy dataset from one oracle user account (one database ) to another oracle user account (another database). I'm using below code for this 

 


PROC COPY IN=TE1 OUT=TE CLONE;
SELECT table1;
RUN;

 

But successful copy the length of variable got updated three times automatically . Means in Old table1 variable called Name have 100 character , then the length automatically increased as 300 characters for new table1 in new database.

 

Thank you all in advance ....  

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You are asking SAS to connect convert your Oracle table to a SAS dataset and then convert your SAS dataset back into an Oracle table.  Most likely you have SAS setup to multiple lengths of character variables by a factor of 1.5 in each direction to handle possible issues with transcoding of character strings.

 

It would be better to find out how to do this function using Oracle code.  If the tables are in different schemas in the same database it should be straight forward perhaps something like the code below. 

libname xx oracle ...... ;
proc sql ;
  connect using xx ;
  execute (
  create table schema2.table1 as select * from schema1.table1 
 ) by xx;
quit;

If they are in different databases then you probably need slightly different code.

 

You could possibly use SAS to help you get the list of tables to copy, and then use that list to generate the Oracle commands to copy the individual tables.

View solution in original post

7 REPLIES 7
Criptic
Lapis Lazuli | Level 10

Have you tried using a data step for this?

 

data secondLib.table1;
 set firstLib.table1;
run;
singhsahab
Lapis Lazuli | Level 10

Hey,

 

I have huge number of datasets , i want to copy all dataset from one library to anther library, if i go through Data and Set statement i need to write huge lines of code.

 

Thanks

ballardw
Super User

@singhsahab wrote:

Hey,

 

I have huge number of datasets , i want to copy all dataset from one library to anther library, if i go through Data and Set statement i need to write huge lines of code.

 

Thanks


Really sounds like a database clone issue not involving SAS at all if the libraries involved are Oracle or other databases.

Or operating system copy command or even a back up and restore to a different location.

error_prone
Barite | Level 11
I recommend using either proc SQL or a tool provided by Oracle. By using proc copy or datastep converting happen, which can't be avoided imho.
singhsahab
Lapis Lazuli | Level 10

i tried PROC SQL and PROC DATA procedure with SET and LENGTH statement but the same issue. Can you please let me know why variable length got automatically increased. I'm so scared and I've no idea how to fix this . 

ShiroAmada
Lapis Lazuli | Level 10

Try this...

 

%MACRO COPY_SRC_TBLS(srcLIB, tgtlib);
proc sql;
  create table SRC_tbl_list as
select memname from dictionary.tables
where upcase(libname)="&srclib";
quit;

%let n=&sqlobs.;

proc sql noprint;
  select memname into :ds1-:ds&n from SRC_TBL_LIST;
QUIT;

PROC SQL;
  %do i=1 %to &n.;
      create table &tgtlib..&&ds&i. like &srclib..&&ds&i.;

      insert into tgtlib.&&ds&i.
         select * from &srclib..&&ds&i.;
  %end;
QUIT;
%mend;

%COPY_SRC_TBLS(SASHELP, WORK);

 

 Steps:

1. Use dictionary.tables to get a list of tables in your source library.

2. Proc sql creates an automatic count macro variable known as sqlobs - this is the count based on your query.  So we put that in our macro variable called n.

3. The create table like in my sql - replicates or copies the structure of a source table (no records, empty table).

4. We start populating the target table using the records from the source table.

 

This way, the length of your columns will remain the same.

 

 

Hope this helps.  

Tom
Super User Tom
Super User

You are asking SAS to connect convert your Oracle table to a SAS dataset and then convert your SAS dataset back into an Oracle table.  Most likely you have SAS setup to multiple lengths of character variables by a factor of 1.5 in each direction to handle possible issues with transcoding of character strings.

 

It would be better to find out how to do this function using Oracle code.  If the tables are in different schemas in the same database it should be straight forward perhaps something like the code below. 

libname xx oracle ...... ;
proc sql ;
  connect using xx ;
  execute (
  create table schema2.table1 as select * from schema1.table1 
 ) by xx;
quit;

If they are in different databases then you probably need slightly different code.

 

You could possibly use SAS to help you get the list of tables to copy, and then use that list to generate the Oracle commands to copy the individual tables.

SAS Innovate 2025: Register Now

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!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 6308 views
  • 1 like
  • 6 in conversation