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 !
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;
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;
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.