Comparing Values in 2 Macro Variables

[A_SAS_Man]

I have two macro variables, I would like to compare the two macro variables and return a third macro that only contains the values the other two have in common.

data test1;
input code1 $6. code2 $6. code3 $6.;
datalines;
80090 34521 63421
;

data test2;
input code1 $6. code2 $6. code3 $6. code4 $6.;
datalines;
23145 23147 52134 90843
;

proc sql;
    select distinct name
    into :macro1 separated by " "
    from dictionary.columns
     where upcase(memname)= 'TEST1'
             And
             upcase(libname) = 'WORK'
     order by name
;
quit;

proc sql;
    select distinct name
    into :macro2 separated by " "
    from dictionary.columns
     where upcase(memname)= 'TEST2'
             And
             upcase(libname) = 'WORK'
     order by name
;
quit;

%put &macro1;
%put &macro2;

I would like to return a macro3 that would only contain code1 code2 code3. One macro will not always be a perfect subset of the other.

[Reeza]

Is your data originally in two tables? If so can you create the table 3 out of test1/test2 instead of the macro variable? 

[mkeintz · Accepted solution]

You apparently want the variable names that are common to both test1 and test2, right?  If so, don't bother to do that work in macro language.  Do it in SQL:

data test1;
input code1 $6. code2 $6. code3 $6.;
datalines;
80090 34521 63421
;

data test2;
input code1 $6. code2 $6. code3 $6. code4 $6.;
datalines;
23145 23147 52134 90843
;

proc sql noprint;
  create table test1_vars as select name
    from dictionary.columns
    where upcase(memname)= 'TEST1' And upcase(libname) = 'WORK';
  create table test2_vars as select name
    from dictionary.columns
    where upcase(memname)= 'TEST2' And upcase(libname) = 'WORK';

  select distinct name into :macro1 separated by ' '
    from test1_vars;
  select distinct name into :macro2 separated by ' '
    from test2_vars;
  select distinct name into :macro3 separated by ' '
   from (select name from test1_vars intersect select name from test2_vars)
  ;
quit;

By the way, if you use SELECT DISTINCT, then ORDER BY the same variable is redundant.