CODEWITHSUNDEEP
sqlmacrossassas-macro
Caio Oliveira
Caio Oliveira

Reputation: 1243

Execute Macro inside SQL statement

The Situation:

I have a table mytable with two columns: tablename and tablefield:

|-----------|------------|
| tablename | tablefield |
|-----------|------------|
| table1    | id         |
| table2    | date       |
| table3    | etc        |
|-----------|------------|

My core objective here is basically, make a Select for each of these tablenames, showing the MAX() value of its corresponding tablefield.

Proc SQL;
Select MAX(id) From table1;
Select MAX(date) From table2;
Select MAX(etc) From table3;
Quit;

ps: The solution have to pull the data from the table, so whether the table change its values, the solutions will make its changes also.

What I have tried:

From the most of my attempts, this is the most sofisticated and I believe the nearest from the solution:

proc sql;
create table table_associations (
    memname varchar(255), dt_name varchar(255)
);

Insert Into table_associations 
values ("table1", "id")
values ("table2", "date")
values ("table3", "etc");
quit;

%Macro Max(field, table);
Select MAX(&field.) From &table.;
%mend;

proc sql;
Select table, field, (%Max(field,table))
From LIB.table_associations
quit;

Creating the Macro, my intend is clear but, for this example, I should solve 2 problems:

Upvotes: 2

Views: 7419

Answers (2)

DWal
DWal

Reputation: 2762

Macros are not necessary here as you can just generate the code using put statements in the data step:

filename gencode temp;

data _null_;
  set table_associations end=eof;
  file gencode;
  if _n_=1 then put 'proc sql;';
  put 'select max(' tablefield ') from ' tablename ';';
  if eof then put 'quit;';
run;

%include gencode / source2;
filename gencode clear;

The code is written to the a temporary file named 'gencode'. You could make this file permanent if you want. _n_=1 and end=eof are used to print the statements before and after the queries. Finally, %include gencode runs the code and the source2 option prints the code to the log.

Upvotes: 1

Jeff
Jeff

Reputation: 1807

In a data step, you can use call execute to do what you're describing.

%Macro Max(field, table);
proc sql;
Select MAX(&field.) From &table.;
quit;
%mend;

data _null_;
    set table_associations;
    call execute('%MAX('||field||','||table||')');
run;

Upvotes: 3

Related Questions