Hi All, I am trying to Automate a code to extract daily data for example "01Aug2023 - 02Aug2023" from teradata. i have the below code which is working, but i have 2 issues , Which i am not able to resolve, kindly suggest. 1. When i run the loop from %DO i = 0 %TO &RUN_DAYS. -1 ; I starts from 2 the dates are calculated as 02Aug2023 instead of 01Aug2023, but when i use %DO i = 0 %TO %EVAL(&RUN_DAYS. -1); the dates are calculated day0 = 01Aug2023 and so on.... 2. I am not able to use the dates while extraction data from teradata table. I am able to use the dates as mentioned below, which would include duplicate data. start date End Date 20230802 20230803 20230803 20230804 20230804 20230805 but was trying to have the dates as mentioned below start date End Date 20230802 20230803 20230804 20230805 20230806 20230807 I can extract the data for each day using the above code, as i don't want make changes to the Already running code in production. Please find the logs and the code attached.
DATA _NULL_;
C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); /*REPORTING MONTH IS AUGUST2023 OR ANY MONTH PREVIOUS TO THE CURRENT MONTH*/
CALL SYMPUT (CATS('RMTH'),CATT(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.)))); /*ONLY MONTH NAME FOR REPORTING PURPOSES*/
CALL SYMPUT (CATS('REP_MTH'),CATT(LOWCASE(PUT(INTNX('MONTH',C_MTH,0),MONYY7.))) );/*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/
CALL SYMPUT (CATS('ACT_MTH'),CATT(LOWCASE(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.))),STRIP(PUT(INTNX('YEAR',C_MTH,0),YEAR2.)))) ; /*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/
CALL SYMPUT (CATS('PERIOD'),PUT(INTNX('MONTH',TODAY(),-1),YYMMDDN6.));/*DATE FORMAT FOR EXTRACTION*/
CALL SYMPUT (('RUN_DAYS'),PUT(DAY(INTNX('MONTH',C_MTH,0,'E')),Z2.)); /*NUMBER OF DAYS OF THE MONTH*/
RUN;
%PUT "RMTH &RMTH.";
%PUT "REP_MTH &REP_MTH.";
%PUT "ACT_MTH &ACT_MTH.";
%PUT "PERIOD &PERIOD.";
%PUT "RUN_DAYS &RUN_DAYS.";
%MACRO DATA_PULL();
%DO i = 0 %TO 2; *%EVAL(&RUN_DAYS. -1);
%LET MTH&i. = %SYSFUNC(PUTN(&i.,Z2.));
DATA _NULL_;
C_MTH = STRIP(INTNX('MONTH',TODAY(),-1));
CALL SYMPUT (CATS("TDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),&i.),YYMMDDN8.)));
CALL SYMPUT (CATS("PTDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),%EVAL(&i.+1)),YYMMDDN8.)));
RUN;
%PUT "DAYS&i. = &&TDY&i.";
%PUT "PTDAYS&i. = &&PTDY&i.";
/*WE ARE EXTRACTING DATA FROM TERADATA SALES TABLE. UISNG BETWEEN START DATE AND END DATE FOR A PERIOD OF EXTRACTION.*/
DATA ST&i.;
STDTE = "&&TDY&i.";
ENDDTE = "&&PTDY&i.";
RUN;
%END;
%MEND;
%DATA_PULL();
DATA _NULL_; C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); /*REPORTING MONTH IS AUGUST2023 OR ANY MONTH PREVIOUS TO THE CURRENT MONTH*/ CALL SYMPUT (CATS('RMTH'),CATT(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.)))); /*ONLY MONTH NAME FOR REPORTING PURPOSES*/ CALL SYMPUT (CATS('REP_MTH'),CATT(LOWCASE(PUT(INTNX('MONTH',C_MTH,0),MONYY7.))) );/*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/ CALL SYMPUT (CATS('ACT_MTH'),CATT(LOWCASE(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.))),STRIP(PUT(INTNX('YEAR',C_MTH,0),YEAR2.)))) ; /*ONLY MONTH & YEAR FORMAT REPORTING PURPOSES*/ CALL SYMPUT (CATS('PERIOD'),PUT(INTNX('MONTH',TODAY(),-1),YYMMDDN6.));/*DATE FORMAT FOR EXTRACTION*/ CALL SYMPUT (('RUN_DAYS'),PUT(DAY(INTNX('MONTH',C_MTH,0,'E')),Z2.)); /*NUMBER OF DAYS OF THE MONTH*/ RUN; %PUT "RMTH &RMTH."; %PUT "REP_MTH &REP_MTH."; %PUT "ACT_MTH &ACT_MTH."; %PUT "PERIOD &PERIOD."; %PUT "RUN_DAYS &RUN_DAYS."; %MACRO DATA_PULL(); %DO i = 0 %TO 2; *%EVAL(&RUN_DAYS. -1); %LET MTH&i. = %SYSFUNC(PUTN(&i.,Z2.)); DATA _NULL_; C_MTH = STRIP(INTNX('MONTH',TODAY(),-1)); CALL SYMPUT (CATS("TDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),&i.),YYMMDDN8.))); CALL SYMPUT (CATS("PTDY&i."),STRIP(PUT(INTNX('DAY',(INTNX('MONTH',C_MTH,0)),%EVAL(&i.+1)),YYMMDDN8.))); RUN; %PUT "DAYS&i. = &&TDY&i."; %PUT "PTDAYS&i. = &&PTDY&i."; /*WE ARE EXTRACTING DATA FROM TERADATA SALES TABLE. UISNG BETWEEN START DATE AND END DATE FOR A PERIOD OF EXTRACTION.*/ DATA ST&i.; STDTE = "&&TDY&i."; ENDDTE = "&&PTDY&i."; RUN; %END; %MEND; %DATA_PULL();
... View more