01-15-2016 10:05 PM
the chelange is how to properly assing variable for example
do i=1 to &raw_max;
How do I assing value to mnth and how to pass this varible to data set statement
01-15-2016 06:49 PM
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:
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.
01-15-2016 10:08 PM
Thanks for quick replay.
here is what I am trying to do
do i=1 to 11;
the problem is that code creates only one table in Oracle beside 11 table
01-15-2016 10:17 PM
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;
01-16-2016 10:56 AM - edited 01-16-2016 10:58 AM
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;
02-01-2016 02:30 PM
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.
02-02-2016 11:33 AM