Help using Base SAS procedures

PROC COPY

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

PROC COPY

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


Accepted Solutions
Highlighted
Solution
a week ago
Super User
Super User
Posts: 6,842

Re: PROC COPY

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


All Replies
Contributor
Posts: 49

Re: PROC COPY

Have you tried using a data step for this?

 

data secondLib.table1;
 set firstLib.table1;
run;
Occasional Contributor
Posts: 10

Re: PROC COPY

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

Super User
Posts: 11,114

Re: PROC COPY


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.

Frequent Contributor
Posts: 118

Re: PROC COPY

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.
Occasional Contributor
Posts: 10

Re: PROC COPY

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 . 

Frequent Contributor
Posts: 95

Re: PROC COPY

[ Edited ]

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.  

Highlighted
Solution
a week ago
Super User
Super User
Posts: 6,842

Re: PROC COPY

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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