Macro Do Loop with multiple date parameters

[Len18]

Hi Everyone,

I have been struggling with this macro for a while now and have attempted different solutions.

I am using SAS EG 7.1 and would like to automate a report to run for a 13 month period (I was able to accomplish this). However, within the body of code that calls the macro there are other date variables in the where statement that I would like to change as the report month changes (I am stuck with this part).

This is what I have so far (please note this is extracted from a much larger piece of code, but I am hoping that once I am able to resolve this I will be able to apply the solution throughout):

%Macro ACCTS(beg_yymm, end_yymm);


%let beg_yy = %eval(%substr(&beg_yymm,1,4) * 1);
%let beg_mm = %eval(%substr(&beg_yymm,5,2) * 1);
%let end_yy = %eval(%substr(&end_yymm,1,4) * 1);
%let end_mm = %eval(%substr(&end_yymm,5,2) * 1);

%let L=%eval((&end_yy-&beg_yy)*12 + (&end_mm - &beg_mm) + 1);

%do I = 1 %to &L;
data _null_;
call symputx("Date&I", put(intnx('month',mdy(&beg_mm,1,&beg_yy),&I - 1,'E'),yymmn6.),'G');
run;
%put "Date&I is " &&Date&I;
%end;
%mend;



%ACCTS(&PREVDT,&CURRDT);



%MACRO TEMP01(currdt);
PROC SQL; 
CREATE TABLE TEMP01_&currdt. AS
SELECT A.*, INPUT(B.DATE,YYMMDDN8.) AS OPENING_DATE, &DTYYMMDD. 
FROM CLSACCT_&currdt. A
LEFT JOIN ALLACCT_&currdt. B
ON A.ACCOUNT_NO = B.ACCOUNT_NUMBER
WHERE INPUT(DATE,YYMMDDN8.) < &DTYYMMDD. 
ORDER BY 12 DESC
;QUIT;

%MEND;


%TEMP01 (&date13.);
%TEMP01 (&date12.);
%TEMP01 (&date11.);
%TEMP01 (&date10.);
%TEMP01 (&date9.);
%TEMP01 (&date8.);
%TEMP01 (&date7.);
%TEMP01 (&date6.);
%TEMP01 (&date5.);
%TEMP01 (&date4.);
%TEMP01 (&date3.);
%TEMP01 (&date2.);
%TEMP01 (&date1.);

[ballardw]

Please at least show us the values of your parameters.

Apparently you are passing some form of character appearing date. Likely instead of all the substring and MDY stuff you can get the value you need with input and INTNX or similar. If you are always doing sequential periods ( guess because you show no actual  values) then provide start and either number of periods or end period and the INTNX function can get the incremented dates.

Using text formatted values is generally pretty inefficient leading to lots of extra code as you take apart, convert, recreate string values that the computer really doesn't need.

[Len18]

My apologies:

%let todaysdate  = %sysfunc( today() );

%let currdt = %sysfunc( intnx( month, &todaysdate, -1, e), yymmn6. ); 
%put currdt is &currdt;

%let prevdt = %sysfunc( intnx( month, &todaysdate, -13, e), yymmn6. ); 
%put prevdt is &prevdt;

%let DTYYMMDD = %sysfunc( intnx( month, &todaysdate, -1, B), YYMMDDN8. ); 
%put DTYYMMDD is &DTYYMMDD;

[PaigeMiller]

---

@Len18 wrote:

My apologies:

%let todaysdate  = %sysfunc( today() );

%let currdt = %sysfunc( intnx( month, &todaysdate, -1, e), yymmn6. ); 
%put currdt is &currdt;

%let prevdt = %sysfunc( intnx( month, &todaysdate, -13, e), yymmn6. ); 
%put prevdt is &prevdt;

%let DTYYMMDD = %sysfunc( intnx( month, &todaysdate, -1, B), YYMMDDN8. ); 
%put DTYYMMDD is &DTYYMMDD;

---

Your code works properly. What is the problem?

[Len18]

The dtyymmdd variable does not change when the months do. I think perhaps i have not explained what i am attempting to do. If the currdt is 201902 then i want the dtyymmdd to be 20190201. Instead, it just shows the result of the intnx function using todays date for all the months in the loop ie 20200301

[PaigeMiller]

---

@Len18 wrote:
The dtyymmdd variable does not change when the months do. I think perhaps i have not explained what i am attempting to do. If the currdt is 201902 then i want the dtyymmdd to be 20190201. Instead, it just shows the result of the intnx function using todays date for all the months in the loop ie 20200301

---

Demonstrate what you mean here with actual code and output. Show us. Your code that I stated worked properly does not have a loop, even though you now refer to a loop. I do not understand.

[Tom]

If the macro variable DTYYMMDD has a value like 2020018 then it is not going to make much sense in your SELECT statement unless you tell SAS what name to use.  For example as a number:

CREATE TABLE TEMP01_&currdt. AS
  SELECT A.*
       , INPUT(B.DATE,YYMMDDN8.) AS OPENING_DATE
       , &DTYYMMDD. as NUMBER_IN_YYYYMMDD_STYLE

 or a string:

CREATE TABLE TEMP01_&currdt. AS
  SELECT A.*
       , INPUT(B.DATE,YYMMDDN8.) AS OPENING_DATE
       , "&DTYYMMDD." as STRING_IN_YYYYMMDD_STYLE

[PaigeMiller]

Important concept ... do NOT treat calendar dates as text strings that you have to pull apart in order to find a year or a month or a date. Treat dates as dates, and use date functions, date formats and date informats. This will be a lot easier, and SAS has already done the hard work to figure out that the month before January 2020 is December 2019, and so on.

So, if I assume (I shouldn't have to assume, but you really don't tell us) that &BEG_YYMM is for example 1911 (November 2019), then you work with this as a SAS date as follows

%let beg_date = %sysfunc(inputn(&beg_yymm,yymmn4.));
%put &=beg_date %sysfunc(putn(&beg_date,yymm5.));

So you didn't have to pull apart the strings and make use of it as strings, you have a date value that SAS recognizes as November 2019. And now all of your looping ought to be simple.

If you want a loop of 13 months, it would look like this:

%do i=0 %to 12;
    %let thismonth=%sysfunc(intnx(month,&beg_date,&i,s));
    %put &=i &=thismonth %sysfunc(putn(&thismonth,yymm5.));
%end;

I hope this gets you going down the right path. Use SAS date values, use SAS date functions (like INTNX), use SAS date formats (like YYMM5.) and use SAS date informats (like yymmn4.)

[Len18]

Thanks. I will amend the code as best as i can. The entire program is a compilation of several pieces of codes from other users and multiple tables so a lot of it was inherited. I am relatively new to programming but i admit the date formats used made things difficult. I converted the dates later in the program closer to the end as every time i changed something another part would stop working.

[Tom · Accepted solution]

What are you trying to do here? Your code is jumping through all kinds of hoops making simple things complicated.

If you have a string like 202004 and you want to convert that into the first day of April 2020 just use the YYMMN6. informat.

If you have a date like '01APR2020'd (which is the number 22006) and you want a string like 202004 then use the YYMMN6. format.

If you want to increment by a date/time/datetime interval use the INTNX() function.  If you want to calculate how many intervals use the INTCK() function.

Not sure what the macro variable DTYYMMDD has or where it gets set, but here is your second macro adjusted to take as input a start and end value, where both are in that YYYYMM pattern.

%MACRO TEMP01(beg_yymm, end_yymm);
%local start currdt i ;
%let start=%sysfunc(inputn(&beg_yymm,yymmn6));
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end_yymm,yymmn6))));
%let currdt=%sysfunc(intnx(month,&start,&i),yymmn6);
PROC SQL; 
CREATE TABLE TEMP01_&currdt. AS
  SELECT A.*
       , INPUT(B.DATE,YYMMDDN8.) AS OPENING_DATE
       , &DTYYMMDD. 
  FROM CLSACCT_&currdt. A
  LEFT JOIN ALLACCT_&currdt. B
   ON A.ACCOUNT_NO = B.ACCOUNT_NUMBER
  WHERE INPUT(DATE,YYMMDDN8.) < &DTYYMMDD. 
  ORDER BY 12 DESC
;
QUIT;
%end;
%MEND;

So then you can call it like this:

%let DTYYMMDD=whatever;
%let PREVDT=201911;
%let CURRDT=202004;
%temp01(&PREVDT,&CURRDT);

[Len18]

Thanks everyone for your patience and assistance. @Tom  your solution worked, I added the following:

%LET DTYYMMDD = %sysfunc(intnx(month,&start,&i),YYMMDDN8);

So the final code looked like:

%MACRO TEMP01(beg_yymm, end_yymm);
%local start currdt i ;
%let start=%sysfunc(inputn(&beg_yymm,yymmn6));
%do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end_yymm,yymmn6))));
%let currdt=%sysfunc(intnx(month,&start,&i),yymmn6);
%LET DTYYMMDD = %sysfunc(intnx(month,&start,&i),YYMMDDN8);
PROC SQL; 
CREATE TABLE TEMP01_&currdt. AS
  SELECT A.*
       , INPUT(B.DATE,YYMMDDN8.) AS OPENING_DATE
       , &DTYYMMDD. AS MONTH_SATRT
  FROM CLSACCT_&currdt. A
  LEFT JOIN ALLACCT_&currdt. B
   ON A.ACCOUNT_NO = B.ACCOUNT_NUMBER
  WHERE INPUT(DATE,YYMMDDN8.) < &DTYYMMDD. 
  ORDER BY 12 DESC
;
QUIT;
%end;
%MEND;

%let todaysdate  = %sysfunc( today() );

%let currdt = %sysfunc( intnx( month, &todaysdate, -1, e), yymmn6. ); *TO CALC END OF 13MTH REPORTING PERIOD;
%put currdt is &currdt;

%let prevdt = %sysfunc( intnx( month, &todaysdate, -13, e), yymmn6. ); *TO CALC START OF 13MTH REPORTING PERIOD;
%put prevdt is &prevdt;

%let DTYYMMDD = %sysfunc( intnx( month, &todaysdate, -1, B), YYMMDDN8. ); *TO CALC END OF 13MTH REPORTING PERIOD IN YYMMDDN8. FORMAT (1ST DAY IN MTH);
%put DTYYMMDD is &DTYYMMDD;

%temp01(&PREVDT,&CURRDT);

the output now gives me records which were opened before the start of a particular month. See below:

Report_Month	Account_Number	OPENING_DATE	Month_Start
Mar-19	1	20190227	20190301
Mar-19	2	20190227	20190301
Mar-19	3	20190227	20190301
Mar-19	4	20190227	20190301
Mar-19	5	20190226	20190301
Mar-19	6	20190226	20190301

[Tom]

You still seem to have a logic problem (caused by not storing dates as dates).

You make the macro variable DTYYMMDD as string of digits like 2020401 which both store as the number 2,020,401 into the new variable MONTH_SATRT and try to use as if it was a DATE value in the WHERE condition. 

Do you want to store it as that goofy number? 

- If YES then remove the INPUT() function call in the WHERE condition.

WHERE b.DATE < &DTYYMMDD.

- If NO then remove the format from the %sysfunc(intnx()) call and add it to the SELECT clause.

%LET DTYYMMDD = %sysfunc(intnx(month,&start,&i));
...
       , &DTYYMMDD. AS MONTH_SATRT format=YYMMDDN8.
...
  WHERE input(b.DATE,yymmdd8.) < &DTYYMMDD.

[Len18]

Thanks. Actually, i removed the field from the select statement as it added no value there. So, all the remaining fields are now dates