SAS macros: using macros in proc sql

Question

How to use macros in SQL? (for every thing, that was selected)

I mean something like this:

&VarTable is a table, which have two variables: (for example) Lib and Table

Each observation in &VarTable is the name of table: Lib.Table

I want to do things for every table:

1) exist?

2) sort it

and last condition: each table, if it exist, have a variable &VarField.

   %macro mSortedTable(vLib,vTab,vVar);
        %if %sysfunc(exist(&vLib..&vTab)) %then %do;
             proc sort data = &vLib..&vTab;
                 by &vVar; 
             run;
             &vLib..&vTab
        %end; 
        %else %do; "" %end;
   %mend mSortedTable;

   proc sql noprint;
          select %mSortedTable(vLib=Lib,vTab=Table,vVar=&VarField)
              into: AccumVar separated by " "
          from &VarTable;
   quit;

how to do this with sql and macros?

Answer 1

Do you have to use sql and macros? A simple data step and call execute would do what you need here.

Below is an example that takes a data set that has a list of tables to process, checks to see if the table exists and if it does, sorts it by &VarField. This could be easily extended to sort each table by a custom set of variables if desired.

If the table does not exist, it generates a warning message.

/* create fake data */
data testdat;
length lib $8 table $32;
input lib $ table $;
datalines;
work test1
work test2
work test3
work doesnotexist
;
run;
/* create 3 data sets */
data work.test1 work.test2 work.test3;
input var1 var2 var3;
datalines;
1 34 8
2 54 5
12 5 6
;
run;
/* end create data */

%let VarTable=work.testdat;
%let VarField=var2 var3;


data _null_;
  set &VarTable;
  dsname=catx('.',lib,table);
  if exist(dsname) then do;
    call execute("proc sort data=" || strip(dsname) || "; by &VarField; run;");
  end;
  else do;
    put "WARNING: The data set does not exist: " lib= table=;
  end;
run;