Macro to loop through dates

[eemrun]

I am having an issue with a macro looping through some dates. I have 2 macros and they are structured as below:

This first macro should be extracting datasets that have a date field in their name. E.g. 201803. The macro should loop through quarterly data. So starting at 201806 should move to 201809 and then 201812 datasets.

%macro extract_all(start,end,interval);
	%let start=%sysfunc(inputn(&start,yymmn6.));
	%let end=%sysfunc(inputn(&end,yymmn6.));
	%let dif=%sysfunc(intck(&interval.,&start,&end));

	
	*-----------------------------------;	
	*Perform Extraction;
	*-----------------------------------;	

	%do j=0 %to &dif;

		%let date=%sysfunc(intnx(&interval.,&start,&j,e),yymmn6.);
		%let date_d9=%sysfunc(intnx(month,%sysfunc(inputn(&date, yymmn6.)),0,E), date9.);
		%put Observation_Date = &date.;
		%put date_d9 = &date_d9.;

		/* run all extraction macros*/

		%Lookback(&date.);

	%end;
	

%mend extract_all;

The lookback macro extracts a variable over the last 12 months for the various quarterly datasets to be extracted. E.g. for 201806 the lookback macro should extract data 12 months prior (e.g. 201805, 201804, 201803, etc.) and then collate into one 1 final dataset for each quarter. The macro is partially defined below. I have left out the sql stuff for now.

%macro lookback(date);

	%local i lk_date month;
	%let start=%sysfunc(inputn(&date,yymmn6.));
	%do i=0 %to 11;
	%put date = &date.;
	%let lk_date=%sysfunc(intnx(month,&start,-&i,e),yymmn6.);
	%put Lookback_Date = &lk_date.;
	%put month = &i.;

	proc sql;
                ----------------
        %end;

    data val;
       merge datasets;
    run;

%mend;

The problem that I am facing is when I run the first macro for dates (201806-201812), instead of going 201806, 201809, 201812 as the final datasets, the macro loops to 201806, 201809 and 201903. I am struggling to understand why the last dataset is moving 2 quarters instead of 1. Can anyone help?

%extract_all
	(
	start 		= 201806,
	end 		= 201812,
	interval	= quarter

	);

[ChrisNZ]

1. Don' forget to always make your variables local.

2. Don't use tabs in code if you want your pasted code to look the same everywhere

3. This works fine:

%macro extract_all(start,end,interval);
  %local i dif date date_d9;
  %let start=%sysfunc(inputn(&start,yymmn6.));
  %let end  =%sysfunc(inputn(&end,yymmn6.));
  %let dif  =%sysfunc(intck(&interval.,&start,&end));
  %do i=0 %to &dif;
    %let date   =%sysfunc(intnx(&interval.,&start,&i,e),yymmn6.);
    %let date_d9=%sysfunc(intnx(month,%sysfunc(inputn(&date, yymmn6.)),0,E), date9.);
    %put Observation_Date = &date.;
    %put date_d9 = &date_d9.;
    %Lookback(&date.);
  %end;
%mend extract_all;
 
%macro lookback(date);
  %local i lk_date start;
  %let start=%sysfunc(inputn(&date,yymmn6.));
  %do i=0 %to 11;
    %let lk_date=%sysfunc(intnx(month,&start,-&i,e),yymmn6.);
    %put date = &date.;
    %put Lookback_Date = &lk_date.;
    %put month = &i.;
  %end;
%mend;

%extract_all( start     = 201806,
            , end       = 201807,
            , interval  = quarter );

Observation_Date = 201806
date_d9 = 30JUN2018
date = 201806
Lookback_Date = 201806
month = 0
date = 201806
Lookback_Date = 201805
month = 1
date = 201806
Lookback_Date = 201804
month = 2
date = 201806
Lookback_Date = 201803
month = 3
date = 201806
Lookback_Date = 201802
month = 4
date = 201806
Lookback_Date = 201801
month = 5
date = 201806
Lookback_Date = 201712
month = 6
date = 201806
Lookback_Date = 201711
month = 7
date = 201806
Lookback_Date = 201710
month = 8
date = 201806
Lookback_Date = 201709
month = 9
date = 201806
Lookback_Date = 201708
month = 10
date = 201806
Lookback_Date = 201707
month = 11

Observation_Date = 201809
date_d9 = 30SEP2018
date = 201809
Lookback_Date = 201809
month = 0
date = 201809
Lookback_Date = 201808
month = 1
date = 201809
Lookback_Date = 201807
month = 2
date = 201809
Lookback_Date = 201806
month = 3
date = 201809
Lookback_Date = 201805
month = 4
date = 201809
Lookback_Date = 201804
month = 5
date = 201809
Lookback_Date = 201803
month = 6
date = 201809
Lookback_Date = 201802
month = 7
date = 201809
Lookback_Date = 201801
month = 8
date = 201809
Lookback_Date = 201712
month = 9
date = 201809
Lookback_Date = 201711
month = 10
date = 201809
Lookback_Date = 201710
month = 11

[ballardw]

You start with:

This first macro should be extracting datasets that have a date field in their name. E.g. 201803. The macro should loop through quarterly data.

So, show the EXACT names of the data sets, not the date value, the entire data set names that you expect to extract for the given parameters in your example.

Then explain why if you want quarterly data sets the loop in Loopback is hard coded to get 12 monthly output "dates". The logic quite escapes me. Are the sets monthly or quarterly. It may be you are confusing us with poor word order.

Please don't leave stubs of  like that partial Proc sql.

	proc sql;
                ----------------

It doesn't explain anything of the logic if this is supposed to build the set DATASETS used in the Merge statement is a likely problem.

And why does that data step

data val;
       merge datasets;
    run;

"merge" a single data set? and hardcoded? It never changes based on any of the macro code so I don't see where anything is done.

And the loops, as is very easily seen in the log as generated by your %put statements includes the same months multiple times.

It might help to show the non-macro code that you used to develop the logic before attempting to create the macro version.