topic Re: Use of Dates in Macro in SAS Programming

Use of Dates in Macro

Santt0sh — Fri, 15 Sep 2023 02:31:50 GMT

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();

Re: Use of Dates in Macro

ballardw — Fri, 15 Sep 2023 04:54:06 GMT

Please do not post the question and details in a text box. Or if you must force line breaks. It is extremely hard to read long lines of narrative.

You don't include a log with the problem as far as I can see.

You state:

.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,

when I run your code with the i= 0 to 2 the first one I get is "DAYS0 = 20230801" not 20230802. You show a value that you are not actually generating as far as I can tell. You should state stuff that actually appears in our log.

So I am actually sure of your problem.

STATE the base date you want the program to run on. Code with TODAY changes values.

Show the list of values you want to generate when the date is that AND in which variables.

When we get things in understandable form I won't be surprised if part of the issue isn't this:

C_MTH = STRIP(INTNX('MONTH',TODAY(),-1));

Why are converting that to date value to character???? You are repeatedly using it as as the argument to INTNX so SAS has to convert C_mth from character back to numeric. Do that often enough with enough variables and you will encounter something where the conversion may not be correct.

If you are calling STRIP so many times because of leading spaces after use of PUT then you can avoid it by using -L to justify the PUT

Instead of

CATT(STRIP(PUT(INTNX('MONTH',C_MTH,0),MONNAME3.)))

use

PUT(INTNX('MONTH',C_MTH,0),MONNAME3. -L)

You are not providing a second function to CATT so it is basically wasting clock cycles and makes code hard to read.

Same with you don't have second arguement so 'RMTH' is sufficient.

CATS('RMTH')

Caution: You should not use the *<code> ; form of comments inside of macro definitions. The "comment" depending on the actual content may do interesting things. Macro language comments use %*<code>; or the /*<code>*/ structure.

Why do you have that data _null_ inside of the macro loop? Creating macro variables just to make a single assignment in another data step is a waste of clock cycles and makes for hard to read and follow code.

Re: Use of Dates in Macro

PaigeMiller — Fri, 15 Sep 2023 09:47:02 GMT

Re-formatting your question so it is readable would be very helpful. And it would also probably get you faster answers. As stated by @ballardw , do not type your question in a code box.

Re: Use of Dates in Macro

Kurt_Bremser — Fri, 15 Sep 2023 12:48:11 GMT

Don't overcomplicate things:

data _null_;
c_mth = intnx('month',today(),-1));
call symputx('RMTH',put(c_mth,,monname3.));
call symputx('REP_MTH',lowcase(put(c_mth,monyy7.)));
call symputx('ACT_MTH',lowcase(put(c_mnth,monname3.))!!PUT(c_mth,year2.));
call symputx('PERIOD',put(c_mth,yymmddn6.));
call symputx('RUN_DAYS',put(day(intnx('month',c_mth,0,'e')),z2.));
run;

CALL SYMPUTX will automatically strip the second argument.

INTNX with a third argument of 0 will result in the same value ("beginning" alignment).