sas execute a macro for each instance in a data step

Question

I have a macro which inserts data into a table over a set of given time-frame.

It loops through a series of from-to dates (which are stored in a dataset) and runs the macro with a proc sql insert statement.

When checking the data at the end of all of this, I notice that only data from the final from-to period is in the new data set.

Here is my code when calling the macro in the data step.

data _null_;
    set extract_insert_dates;
    %insert_table_extract(put(extract_start, date11.),put(extract_end, date11.));
run;

Is there something else I should be calling in the data step for this to work and insert data (run the macro) for each of the from-to periods, as opposed to just the final one?

Answer 1

Pretend you are the macro compiler and replace the macro call with the actual SAS code it will generate. Remember that to the macro process the parameter values of put(extract_start, date11.) and put(extract_end, date11.) are just strings of characters.

I suspect that you need to use call execute so the values of the data set variables extract_start and extract_end can be passed to the macro.

data _null_;
  set extract_insert_dates;
  call execute(cats('%nrstr(%insert_table_extract)(',put(extract_start, date11.),',',put(extract_end,date11.),')'));
run;