search the library to append data-sets having specific variable

Question

I have 2 datasets in library aa & bb respectively. My code checks first the datasets in the library if they having specific column variables in them. If the datasets have the specific variables then they are appended. But when i run my macro it doesn't check the datasets in the library & appends their values in test1 & test2 as it should, it doesn't perform intended function of checking the dataset if they have the variables in them, and returns errors symbolic reference for &ds &list not found & also shows syntax error at &ds and &list.

can you'll suggest any edits...

below is my code..

%macro CHK(lib1=,lib2=,varlist=);

%local
list
ds
;

  proc sql noprint;
    select distinct catx(".",libname,memname) into :list separated by " "
      from dictionary.columns
      where libname = %upcase ("&lib1") and %upcase(name) in("&varlist") ;
  quit;
%put &list;

  data test1;
    set &list;
  run;

  proc sql noprint;
    select distinct catx(".",libname,memname) into :ds separated by " "
      from dictionary.columns
      where libname = %upcase ("&lib2") and %upcase(name) in("&varlist") ;
  quit;

 %put &ds;

 data test2;
 set &ds;
 run;

%mend CHK;

%CHK(lib1=aa,lib2=bb,varlist=%str('nam', 'DD', 'ht'));

Answer 1

I see it as a three-step process:

1. Get a list of datasets in the library that have the variable.
2. Make a data set of unique values of the variable, in a structure that can be read into PROC FORMAT as a CNTLIN dataset, to create a format that maps each value to a sequential code.
3. Process each dataset, using the format to recode the variable.

Here is some sample code:

%macro Recode(lib=,var=);

%local
  DataList
  i
  data_i
;

%*1. Get a list of datasets in the library that have the variable;

proc sql noprint ;
  select distinct catx(".",libname,memname) into :DataList separated by " "
    from dictionary.columns
    where libname = %upcase("&lib") and upcase(name) = %upcase("&var");
quit;

%put >>&DataList<<;

%*2. Read the unique values of the variable into a dataset
     and then create a format named $recode that maps each unique value
     to a sequential code.
;

proc sql;
  create table __cntlin as
  select "$Recode" as fmtname, &var as start, put(monotonic(),z6.) as label 
    from
      (
       %do i=1 %to %sysfunc(countw(&datalist,%str( )));
         %let data_i=%scan(&datalist,&i,%str( ));
         %if &i>1 %then union;
         select &var from &data_i 
       %end; 
      )
  ;
quit;

proc format cntlin=__cntlin 
            library=work
            fmtlib 
  ;
  select $recode;
run;

%*3. Use the format to recode the variable in each dataset;

%do i=1 %to %sysfunc(countw(&datalist,%str( )));
  %let data_i=%scan(&datalist,&i,%str( ));

  data &data_i._R;
    set &data_i;
    &var._R=put(&var,$recode.);
  run;
%end;


*Cleanup;
proc catalog cat=work.formats;
  delete Recode.formatc;
quit;
proc datasets library=work memtype=data nolist;
  delete __cntlin;
quit;

%mend Recode;

%Recode(lib=work,var=name)

With your sample data, the above code produces two datasets Test1_R and Test2_R:

1454  data _null_;
1455    set Test1_R;
1456    put @1 Name @10 Name_R;
1457  run;

sam      000006
danny    000003
jacob    000004
susan    000009
sandra   000007
vinny    000010
alicia   000001
NOTE: There were 7 observations read from the data set WORK.TEST1_R.

1458
1459  data _null_;
1460    set Test2_R;
1461    put @1 Name @10 Name_R;
1462  run;

sam      000006
dann     000002
jhon     000005
susan    000009
sandy    000008
vinny    000010
NOTE: There were 6 observations read from the data set WORK.TEST2_R.