BookmarkSubscribeRSS Feed
Santt0sh
Lapis Lazuli | Level 10
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(); 

 

3 REPLIES 3
ballardw
Super User

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.

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Kurt_Bremser
Super User

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 825 views
  • 0 likes
  • 4 in conversation