PROC COPY

[singhsahab]

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 ....  

[Criptic]

Have you tried using a data step for this?

data secondLib.table1;
 set firstLib.table1;
run;

[singhsahab]

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]

---

@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]

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]

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]

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 · Accepted solution]

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.