SAS job to reload Oracle Memory Loaded Tables with Macros

WaelAburezeq — Wed, 05 Aug 2020 19:07:00 GMT

hi all,

I have successfully created an ODBC library that view all oracle datasets then we load the needed tables. However, these tables won't get refreshed in SAS memory unless reloaded again. I am trying to create a job that extracts the loaded data into memory, unload and then reload them again. I am stuck at a point where I can't pass the table name to SAS macro (although it is working in %put statement). forgive me this might be a very easy but tricky part while working with SAS macros. Below are my script:

1- listing tables into a dataset (loaded_tables)

ods output TableInfo=loaded_tables(where=(Global=1) keep = Name Global);
proc casutil;
list tables incaslib="SAS_CONN_ORA" ;
run;

2- inserting these tables into an array so we can loop over them

data _NULL_;
    set loaded_tables nobs = nobs;
    array data Name;
    format t1 $50.;
    retain t1;
    array t[1] $;
    do i = 1 to dim(t);
        if _N_ = 1 then t[i] = put(data[i], 8.);
            else t[i] = compress(t[i] || ',' || put(data[i], 8.));
	*put data[i] ::: this works and prints the tables' names;
    %reload(data[i]); *this is the macro that should drop and reload the table;
    end;
run;

3- Macro to drop and reload the tables (I am stuck at the point of getting the table's names although the previous put data[i] is working:

%macro reload(table_name);
%put "&table_name"; *this is not printing the table name;

/*proc casutil;
    droptable casdata=&table_name incaslib="SAS_CONN_ORA" quiet;
	run;

proc casutil;
	load casdata=&table_name incaslib="SAS_CONN_ORA" 
	outcaslib="SAS_CONN_ORA" casout=&table_name;
run;

*/
%mend reload;

Thank you !

Re: SAS job to reload Oracle Memory Loaded Tables with Macros

SASKiwi — Wed, 05 Aug 2020 20:27:04 GMT

Try this:

data _NULL_;
    set loaded_tables nobs = nobs;
    array data Name;
    format t1 $50.;
    retain t1;
    array t[1] $;
    do i = 1 to dim(t);
        if _N_ = 1 then t[i] = put(data[i], 8.);
            else t[i] = compress(t[i] || ',' || put(data[i], 8.));
	*put data[i] ::: this works and prints the tables' names;
    exec_cmd = '%reload(' !! data[i] !! ');';
    call execute('%nrstr(' !! exec_stmt !! ')'); 
    end;
run;

topic SAS job to reload Oracle Memory Loaded Tables with Macros in Developers

SAS job to reload Oracle Memory Loaded Tables with Macros

Re: SAS job to reload Oracle Memory Loaded Tables with Macros