Greetings, I've developed a macro to help produce data sets pulling from different tables and date ranges, however I believe I'm not doing this in the most efficient way. Btw I'm working with Teradata so it is passing through Teradata. Here is my code: data test_data;
infile datalines;
format cust_id 3. depo_dt yymmddd10. deposit_amt dollar6.2 year_month source;
input cust_id 3. depo_dt :yymmdd10. deposit_amt dollar6.2 year_month source $16.;
datalines;
871 2019-02-21 $20.00 201902 USBANK
888 2019-05-13 $90.00 201905 WELLSFARGO
847 2019-03-25 $60.00 201903 CHASE
821 2019-09-18 $30.00 201909 CITIGROUP
;;
run;
%macro td_sql_macro (tbl_nme,yyyymm,mnth_1,mnth2);
proc sql noerrorstop;
connect to teradata (database=BANK_DATA user="user" password="pw" tdpid="tdpid" mode=teradata);
create table work.&tbl_nme as select * from connection to teradata (
select cust_id,
depo_dt,
deposit_amt,
year_month,
source
from BANK_DATA.source_data_&yyyymm._hist
where depo_dt >= date &mnth_1
and depo_dt < date &mnth2
and prc_dt = &yyyymm
and cust_id <> 0);
disconnect from teradata;
quit;
%mend;
%td_sql_macro(tbl_nme=bank_data_201809,yyyymm=201809,mnth_1='2018-09-01',mnth2='2018-10-01')
%td_sql_macro(tbl_nme=bank_data_201810,yyyymm=201810,mnth_1='2018-10-01',mnth2='2018-11-01');
%td_sql_macro(tbl_nme=bank_data_201811,yyyymm=201811,mnth_1='2018-11-01',mnth2='2018-12-01');
%td_sql_macro(tbl_nme=bank_data_201812,yyyymm=201812,mnth_1='2018-12-01',mnth2='2019-01-01');
%td_sql_macro(tbl_nme=bank_data_201901,yyyymm=201901,mnth_1='2019-01-01',mnth2='2019-02-01');
%td_sql_macro(tbl_nme=bank_data_201902,yyyymm=201902,mnth_1='2019-02-01',mnth2='2019-03-01');
%td_sql_macro(tbl_nme=bank_data_201903,yyyymm=201903,mnth_1='2019-03-01',mnth2='2019-04-01');
%td_sql_macro(tbl_nme=bank_data_201904,yyyymm=201904,mnth_1='2019-04-01',mnth2='2019-05-01');
%td_sql_macro(tbl_nme=bank_data_201905,yyyymm=201905,mnth_1='2019-05-01',mnth2='2019-06-01');
%td_sql_macro(tbl_nme=bank_data_201906,yyyymm=201906,mnth_1='2019-06-01',mnth2='2019-07-01');
%td_sql_macro(tbl_nme=bank_data_201907,yyyymm=201907,mnth_1='2019-07-01',mnth2='2019-08-01');
%td_sql_macro(tbl_nme=bank_data_201908,yyyymm=201908,mnth_1='2019-08-01',mnth2='2019-09-01');
libname BNK_DPST teradata tdpid="tdpid" database=bnk user="user" password="pw" bulkload=yes;
data BNK_DPST.deposits_all;
set bank_data_201908
bank_data_201907
bank_data_201906
bank_data_201905
bank_data_201904
bank_data_201903
bank_data_201902
bank_data_201901
bank_data_201812
bank_data_201811
bank_data_201810
bank_data_201809;
run; The beginning data step is just to see what the data is like, just pretend it is from one of the BANK_DATA.source_data_ tables. So this code works, however I want to make it more efficient/automated in the sense that instead of creating lines calling the macro with different values, I want to automate the macro to just loop through the different values, rather than hard-coding them. Looping through the different tables as well as date filters. The ultimate goal as you can see on the bottom is to combine all the created tables and then create the table in Teradata. I want the data to be a 12-month lookback period. Starting from todays month - 1. So if I was running it for September, I would want the date loops to start at August, going back 12 months. Can anyone help? See opportunities for improvement on how I can automatically loop through the different values without hard-coding table names and dates. Thanks
... View more