Buld SAS macro to upload data from multiple table into one table

Reply
New Contributor SNR
New Contributor
Posts: 4

Buld SAS macro to upload data from multiple table into one table

Hello,

I am trying to upload all table from library into one table in oracle and some challenges.

Please let me know how I can dot it?

 

Grand Advisor
Posts: 17,360

Re: Buld SAS macro to upload data from multiple table into one table

What are your challenges?

 

New Contributor SNR
New Contributor
Posts: 4

Re: Buld SAS macro to upload data from multiple table into one table

the chelange is how to properly assing variable for example

%macro Bld_TBL;

data lib.&mnth;

set cb.&mnth;

run;

%mend;

 

data _NULL_;

   do i=1 to &raw_max;

   call symput('mnth',cat('&tbl',i));

  call execute('%Bld_TBL');

                 output;

        

end;

run;

How do I assing value to mnth and how to pass this varible to data set statement

Respected Advisor
Posts: 3,063

Re: Buld SAS macro to upload data from multiple table into one table

The biggest problem with your program is you are trying to read a temporary SAS dataset into a permanent Oracle table using SQL PASSTHRU EXECUTE. Since this runs entirely in Oracle it has no knowledge of the SAS temporary table.

 

The best way to fix this problem is to add a step that copies the temporary SAS table to an Oracle temporary table. This link provides example code of how to do it:

 

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113602.htm

 

There are also problems in the rest of your code but I suggest you get the upload to Oracle working first without using macro then look at the macro problems. 

New Contributor SNR
New Contributor
Posts: 4

Re: Buld SAS macro to upload data from multiple table into one table

Thanks for quick replay.

here is what I am trying to do

%macro Bld_TBL;

data lib.&mnth;

set cb.&mnth;

run;

%mend;

 

data _NULL_;

   do i=1 to 11;

   call symput('mnth',cat('&tbl',i));

  call execute('%Bld_TBL');

                 output;

        

end;

run;

the problem is that  code creates only one table in Oracle beside 11 table

Grand Advisor
Posts: 17,360

Re: Buld SAS macro to upload data from multiple table into one table

Change your macro, have it take a parameter, which is the value of the mnth, rather than create a macro variable. The execution order/timing is different for call execute and the call symput which is probably causing the issue. 

 

%macro Bld_TBL(mnth);
 data lib.&mnth;
    set cb.&mnth;
 run;
%mend;

data _NULL_;
   do i=1 to 11;
       str=catt('%bld_tbl(', "&tbl", i, ');');
       call execute(str);      
    end;
run;

If you can do library references to Oracle, couldn't you use proc datasets to move the data over though?

 

proc datasets library=lib;
copy in=cb out=lib;
select &tbl1-&tbl11;
run;quit;

 

Super User
Super User
Posts: 6,351

Re: Buld SAS macro to upload data from multiple table into one table

[ Edited ]

First let's re-post your code from the attachment into a code block so that it is more visible.

*assign libref;
libname CB 'f:\CB\';

* create list of table under libref;
data one (Keep=row memname);
  set  sashelp.vmember;
  ROW=_N_;
  where libname ='CB' and memtype ='DATA' ;
run;

*identify number of tables; 
proc sql;  select MAX(ROW)  into:raw_max  from one;  quit;
/* table has 11 table */
* push tables name for library into macro variable;
proc sql;
 select memname into:tbl1 - :tbl&raw_max from one; 
quit;

/* Macro to upload data to oracle */ 
%macro UPLD_DT(mnth);
 %global mnth_1;
 %let mnth_1=&mnth;
  proc sql;
     connect to oracle (user=&user password=&pass);
     execute(insert into MASTER_TABLE (YR,MTH,ACCT_ID)
         select YY,MM,APPLID   
         from   &mnth_1
     )   by oracle;
    disconnect from oracle;
  quit; 
%mend;

/* Here is my challenge to make below code work */
data _null_;   
  do i=1 to &raw_max;
   call symput('mnth',cat('&setnm',i));
   call execute('%UPLD_DT');
  end; 
run;

You are making the process overly complicated. For example if you want to get the list of dataset names into macro variables then you can just do that in one PROC SQL step instead of the multiple steps you have.

* push tables name for library into macro variables ;
proc sql NOPRINT ;
 select memname into :tbl1 - :tbl99999
 from sashelp.vmember
 where libname ='CB' and memtype ='DATA' 
;
%let RAW_MAX=&sqlobs; 
quit;

But in reality there is no need for all of these macro variables.

 

The other issue is that it looks like you want to append all of the data from these tables into a single Oracle table.  So you should probably just use PROC APPEND.  And use an libref pointing to ORACLE. Also are the list of columns an important part of the program?  Or are you just listing the column names because the Oracel syntax requires it?  In SAS you do not normally need to list the column names as SAS knows that you want to place the data from the variable YR into the variable YR in the target table.

 

So here is simple way to generate what I think you want, without using macro variables or macros .

libname CB 'f:\CB\';
libname TARGET ORACLE user=&user password=&pass ;

data _null_;
  set sashelp.vmember ;
  where libname ='CB' and memtype ='DATA' ;
  call execute(catx(' '
       ,'proc append base=TARGET.MASTER_TABLE force'
       ,'data=',catx('.',libname,memname)
       ,';run;'
  ));
run;
New Contributor SNR
New Contributor
Posts: 4

Re: Buld SAS macro to upload data from multiple table into one table

Hi Tom, thanks for you reply

The problem with append that it takes to long.

 

In my case to upload table to Oracle takes just over 10 min. and to update master table takes takes less then 1 min.

When I try append, it took over two hours to upload two table and I have 50 of them.

tables that i need to upload have over 200 variables and over 2M observations.

 

Also, the reason i write a code in multiple steps becuase it is easy to read to person who is new to SAS and not good at macros.

 

 

Esteemed Advisor
Posts: 5,198

Re: Buld SAS macro to upload data from multiple table into one table

If you are updating an Oracle database on a regular basis, check if you can use an ETL tool instead.
For you specific problem, verify that you have bulk load enabled. That's probably the fastest way ya append.
Data never sleeps
Ask a Question
Discussion stats
  • 8 replies
  • 711 views
  • 2 likes
  • 5 in conversation