how do I create a SAS macro to read and copy tables based from anoter table vales

[ger15xxhcker]

data work.TEST_TABLE;
input libname:$32. tablename:$32. to_libname:$32. to_tablename:$32.;
datalines;
sashelp class work copied_class
sashelp cars work copied_cars
;
run;

Hi!

I want to create a macro that reads from a table and copies it according to the specified conditions. So it reads from which lib to which lib and which table to copy. Could you help put this into practice? Thanks!

[PeterClemmensen · Accepted solution]

Unless you have a good reason why you want a macro to do this, Call Execute is a nice tool to create data driven code.

Try this

data work.TEST_TABLE;
input libname:$32. tablename:$32. to_libname:$32. to_tablename:$32.;
datalines;
sashelp class work copied_class
sashelp cars work copied_cars
;
run;

data _null_;
   set test_table;
   call execute
   (compbl(cat(
   "data ", catx('.', to_libname, to_tablename), ";
       set ", catx('.', libname, tablename),";
   run;"
    )));
run;

[ger15xxhcker]

Thanks. It's works!

[PeterClemmensen]

Anytime

[ger15xxhcker]

Can you help me with one more thing?

There are two tables. One of them is what board to copy and where you helped. The other step would be to overwrite or clear certain fields with a default value based on another table.

So if the value of to_archive is Y and the value of the procedure field is default_value then it should be overwritten with the value written there and if the value of to_empty is then emptied that field.

data work.to_archive_and_deperson;
input libname:$32.  tablename:$32. to_libname:$32. to_tablename:$32.;
datalines;
sashelp class work copied_class
sashelp cars work copied_cars
;
run;

data work.columns_to_deperson;
infile datalines delimiter=',';
input sas_libname:$32. sas_table_name:$32. column_name:$32.  to_archeve:$1. procedure:$32. default_value:$32.;
datalines;
sashelp,class,name,Y,default_value,XXXX
sashelp,cars,modell,N, ,  
sashelp,cars,make,Y,to_empty,,
;
run;

Thanks a lot!