Perhaps this will help. Here is some pseudocode that repeatedly calls a macro that extracts one month of data from a Teradata database and appends the monthly data to a master table of a full year of data. The entire block of code is within a RSUBMIT/ENDRSUBMIT block so it executes on your remote server. rsubmit ...;
%macro get_data (start, end);
* get one month of data;
proc sql;
connect to teradata (...);
create table month as
select * from connection to teradata (
select *
from some.table
where dt between &start and &end
);
quit;
* append single month of data to master table of annual data;
proc append base=full_year data=month;
run;
%mend;
data _null_;
yr_end_date = intnx('month', date(), -1, 'e');
yr_start_date = intnx('month', yr_end_date, -11);
put yr_start_date= yymmdd10. yr_end_date= yymmdd10.;
do until (yr_start_date > yr_end_date);
mnth_start_date = yr_start_date;
mnth_end_date = intnx('month', mnth_start_date, 0, 'e');
macro_call = cats('%get_data(', quote(put(mnth_start_date, yymmdd10.), "'"), ',',
quote(put(mnth_end_date, yymmdd10.), "'"), ');');
put macro_call=; * check validity of macro call;
*call execute (macro_call); * execute macro call;
yr_start_date = intnx('month', yr_start_date, 1);
end;
run;
* summarize the full year of data;
proc summary data=full_year ...;
class ...;
var ...;
output ...;
run;
endrsubmit; Here are the macro calls generated in the data _null_ step. macro_call=%get_data('2021-04-01','2021-04-30');
macro_call=%get_data('2021-05-01','2021-05-31');
macro_call=%get_data('2021-06-01','2021-06-30');
macro_call=%get_data('2021-07-01','2021-07-31');
macro_call=%get_data('2021-08-01','2021-08-31');
macro_call=%get_data('2021-09-01','2021-09-30');
macro_call=%get_data('2021-10-01','2021-10-31');
macro_call=%get_data('2021-11-01','2021-11-30');
macro_call=%get_data('2021-12-01','2021-12-31');
macro_call=%get_data('2022-01-01','2022-01-31');
macro_call=%get_data('2022-02-01','2022-02-28');
macro_call=%get_data('2022-03-01','2022-03-31');
... View more
