topic Re: how do I create a SAS macro to read and copy tables based from anoter table vales in SAS Programming

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

ger15xxhcker — Mon, 07 Feb 2022 09:31:53 GMT

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!

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

PeterClemmensen — Mon, 07 Feb 2022 09:50:08 GMT

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;

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

ger15xxhcker — Mon, 07 Feb 2022 10:39:01 GMT

Thanks. It's works!

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

PeterClemmensen — Mon, 07 Feb 2022 12:01:17 GMT

Anytime

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

ger15xxhcker — Mon, 07 Feb 2022 13:40:50 GMT

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!