Execute Macro inside SQL statement -
the situation:
i have table mytable
2 columns: tablename
, tablefield
:
|-----------|------------| | tablename | tablefield | |-----------|------------| | table1 | id | | table2 | date | | table3 | etc | |-----------|------------|
my core objective here basically, make select
for each of these tablenames, showing max()
value of corresponding tablefield.
proc sql; select max(id) table1; select max(date) table2; select max(etc) table3; quit;
ps: solution have pull data table, whether table change values, solutions make changes also.
what have tried:
from of attempts, sofisticated , believe nearest solution:
proc sql; create table table_associations ( memname varchar(255), dt_name varchar(255) ); insert table_associations values ("table1", "id") values ("table2", "date") values ("table3", "etc"); quit; %macro max(field, table); select max(&field.) &table.; %mend; proc sql; select table, field, (%max(field,table)) lib.table_associations quit;
creating macro, intend clear but, example, should solve 2 problems:
- execute macro inside
sql
statement; and - make macro understand string value parameter
sql
command.
in data step, can use call execute
you're describing.
%macro max(field, table); proc sql; select max(&field.) &table.; quit; %mend; data _null_; set table_associations; call execute('%max('||field||','||table||')'); run;
Comments
Post a Comment