Dynamic Macro Loop - To Create An Automatic Loop

[ertr]

Hello @Reeza and @Tom,

I’m so sorry I tried something but I could not handle it. Actually, I understood @Tom’s latest method and it worked in my sample data set fine but it didn’t work in my real data set. The results didn’t match the manaul method’s results 😞 Do you have an idea why the results cannot match with each other?

I also try to create macro structure but I couldn’t handle it @Reeza I’m so sorry. My following sample does not seem fine, I know, I’ll re-examine it but could you help me, please?

On the other hand, first row should be like that -> if '31JUL1989'd <=(Less Than or Equal To) PRO_END_DATE_YM <= '31AUG1989'd then Period = 1;

Thank you very much both of you again.

Data Want;
Set Have;
Length Period 4.; 
Period = 0;
if '31JUL1989'd <= PRO_END_DATE_YM <= '31AUG1989'd then Period = 1;
/*Decreasing 1 monthbecause it cannot excess the StartDate*/
/*This part changed being Less Than or Equal To instead of Less Than*/
.......;
Run;

Data Have;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10826
10827
10828
10829
10830
10850
10870
10880
10991
10999
11111
11222
11333
11444
11555
11666
;
Run;

%Let Start_Date=31JUL1989;
%Let End_Date=31DEC1991;
%Macro Date_Intervals(A,B,C,DataIn,DataOut,Date_Start,Date_End);
Data DataOut.;
Set DataIn.;
Length Period 4.; 
Period = 0;

%If &A.=1  %Then %Do;
Boundary&A. = "&Date_Start"d;
%End;
Boundary&A.=Intnx("Month", "&Date_Start"d, &B., "E");

Format Boundary: Date9.;

If Boundary&A. < PRO_END_DATE_YM <= boundary&C. then Period = &i.;

Run;

%Mend Date_Intervals;
%Macro Dynamic;

%Let i=1;
%Let j=1;
%Let x=2;
%Do %Until(&End_Date.);
%Date_Intervals(&i.,&j.,&x.,Have,Want,&Start_Date.,&End_Date.);
%Let i=&i.+1;
%Let j=&j.+3;
%Let x=&x.+1;
%End;
%Mend;

%Dynamic;

[Reeza]

Stop. What are the rules for calculating the variable period in words?

If you can't define the problem you won't be able to solve it. And we can't help. 

[ertr]

Hello again @Reeza,

Let me try to tell you, I think I cannot get your explanations 😞 Sorry

We define the date values. First one is Start Date and second one is End Date. We need to use as a base for End Date. It should decrease three(month) by three. But if End Date will excess Start Date then it should be over on Start Date and symbol should like -> "<=".

Actually, your following code is correct but the point is that I should make suitable this code for every input data set.

If there are 10 Boundary in your following code so there will be 10 periods

For example, in you boundary2, boundary3 and boundary4 statements 1,4 and 7 values should be parametric.

Periods also should be parametric and boundaries too. I try yo build this structure. 

I hope I could make myself clear.

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;

Data Want;
sest have;
Length Period 4.; 
Period = 0;

boundary1 = "&startDate"d;
boundary2 = intnx("month", "&startDate"d, 1, 'e');
boundary3 = intnx("month", "&startDate"d, 4, 'e');
boundary4 = intnx("month", "&startDate"d, 7, 'e');

format boundary: date9.;

if boundary1 <= PRO_END_DATE_YM <= boundary2 then Period = 1;/*Decreasing 1 month because it cannot excess the StartDate*/
if boundary2 < PRO_END_DATE_YM <= boundary3 then Period = 2;/*Decreasing 3 months*/
if boundary3 < PRO_END_DATE_YM <= boundary4 then Period = 3;/*Decreasing 3 months*/

Thank you,

[Reeza]

Still not clear why the first interval is 1 not 3, it's not past the end date. 

[Reeza]

Work backwards if your definition is from end to start date. Don't try and pre calculate the dates either. 

1. Use intck to determine number of boundaries

2. Create a do loop with that boundary

3. Increment a date variable using the I to increment. It doesn't matter if you go past start date, but you can use MIN(start_date, boundary) to ensure that you don't. 

4. Assign periods 

What happens if the data has dates outside start/end date?

See if you can code that logic, I'll try and post code later if someone else already hasn't. The only macro variables in the code should be the start and end date. 

How is this going to be used? Will there be multiple start/end dates for a single data set? 

Usage ultimately determines end design. 

This is what we needed to see to help answer your question except I think your last sentence is supposed to be if the last boundary is past the start date not end date past the start. 

We define the date values. First one is Start Date and second one is End Date. We need to use as a base for End Date. It should decrease three(month) by three. But if End Date will excess Start Date then it should be over on Start Date and symbol should like -> "<=".

---

[ertr]

Hello @Rezza,

I tried to implement your foregoing articles and I think I still need to decrease some steps to build desired dynamic structures.

1st -> I need to give parametric value instead of 3 in the following statement “%Do i=1 %To 3;” but question mark in my mind is how can I determine this value being a parametric.how can I know this value?

2nd-> I need to enhance the following statement values(1,4,7) being a parametric “Intnx("Month", "&StartDate"d, 1, "E");, Intnx("Month", "&StartDate"d, 4, "E");, Intnx("Month", "&StartDate"d, 7, "E");

Then it will probably seem proper, what do you think @Reeza?

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
Data Have;
Format Pro_End_Date_Ym DATE9.;
Infile Datalines Missover;
Input Pro_End_Date_Ym;
Datalines;
10826
10827
10828
10829
10830
10850
10870
10880
10991
10999
;
Run;
Options Mprint;
%Macro New;
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
%Do i=1 %To 3;
%If &i.=1 %Then %Do;
Boundary&i. = "&StartDate"D;
Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, 1, "E");
%End;
%If &i.=2 %Then %Do;
Boundary&i. = Intnx("Month", "&StartDate"d, 1, "E");
Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, 4, "E");
%End;
%If &i.=3 %Then %Do;
Boundary&i. = Intnx("Month", "&StartDate"d, 4, "E");
Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, 7, "E");
%End;

Format Boundary: Date9.;
%If &i.=1 %Then %Do;
If Boundary&i. <= PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.;
%End;
%Else %Do;
If Boundary&i. < PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.;
%End;
%End;
Run;
%Mend New;
%New;

But I just realized one thing, you used “1” value in your-> “Intnx("Month", "&StartDate"d, 1, "E"); “ statement because there is just one month between 31JUL1989 and 31AUG1989.

What if Start Date is given being a 31JUN1989 then it should be Intnx("Month", "&StartDate"d, 2, "E"); and continue like -> Intnx("Month", "&StartDate"d, 5, "E");, Intnx("Month", "&StartDate"d, 8, "E");

So how can I provide this kind of dynamic structure over your code?

[Tom]

So the intervals are based of the END date (not the START date).  The only use that START date is doing is to allow use numbers that start with one and increase towards the end date instead of using negative numbers from the end date down to the start date.

You can calculate the number of periods by counting the number of months between the START and END dates.  You can then subtract from that the number of months between the current date and the END date to define the period number for the current date.

This code will take your example data and calculate the PERIOD using a formula. I also created EXPECTED variable using your IF/THEN code with specific dates to test that it gave the same results.

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
%let nperiods=%sysfunc(ceil(%sysfunc(intck(month,"&startdate"d,"&enddate"d))/3));

data want ;
  set have ;
  period = &nperiods - int(intck('month',date,"&enddate"d)/3);
  expected = 0;
  if '31JUL1989'd < date <= '31AUG1989'd then expected = 1;
  if '31AUG1989'd < date <= '30NOV1989'd then expected = 2;
  if '30NOV1989'd < date <= '28FEB1990'd then expected = 3;
run;

[ertr]

Hello @Tom,

I just saw your latest post, thank you very much, it is very useful for me.

Now, I need to try in my real data and I will inform you.

By the help of your &Nperiods macro variable I fixed my previous code, it seems proper but as I mentioned in my previous post what if Start Date is 31JUN1989 instead of 31JUL1989 so I need to replace the “1” value with “2” value in the following statement “Intnx("Month", "&StartDate"d, 1, "E");" Do you have an idea about this subject?

Options Mprint;
%Macro New;
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
%Let j=1;
%Do i=1 %To &NPeriods.;

%If &i.=1 %Then %Do;
Boundary&i. = "&StartDate"D;
Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d, &i., "E");
%End;

%If &i.~=1 %Then %Do;
Boundary&i. = Intnx("Month", "&StartDate"d,&j., "E");

%Let j=%Eval(&j.+3);

Boundary%Eval(&i.+1) = Intnx("Month", "&StartDate"d,&j., "E");
%End;

Format Boundary: Date9.;

%If &i.=1 %Then %Do;
If Boundary&i. <= PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.;
%End;
%Else %Do;
If Boundary&i. < PRO_END_DATE_YM <= Boundary%Eval(&i.+1) Then Period =&i.;
%End;
%End;
Run;
%Mend New;
%New;

And your code goes like this

Data Want ;
  Set Have ;
  Period = &nperiods - Int(Intck('Month',Pro_End_Date_Ym,"&enddate"d)/3);
  Expected = 0;
  If '31JUL1989'd < Pro_End_Date_Ym <= '31AUG1989'd Then expected = 1;
  If '31AUG1989'd < Pro_End_Date_Ym <= '30NOV1989'd Then expected = 2;
  If '30NOV1989'd < Pro_End_Date_Ym <= '28FEB1990'd Then expected = 3;
Run;

Thank you

[Tom]

I am not sure why you are continuing to work on making complex code with multiple IF statements.  Perhaps you are still trying to insure that the algorithm works? To properly debug this type of problem you should create some test data that has values that will check all of the issues that you can imagine.  So you would want dates that are just before, after and on key boundaries.  Then you can try different algorithms and see what happens.  So if the START and END dates are '31JUL1989'D and  '28FEB1990'D then you could use test data with the first and last day of each month from June 1989 through March 1990.  You could then set your expected value in your test data and try the algorithm on the test data and see what happens.

[Reeza]

You should use @Tom's code. It's simple, straightforward, understandable and maintainable. Sometimes it's annoying to realize all your code can be simplified into a single line or two of code. From Tom's code this is all you need in a single data step. 

data want;
set have;
    nperiods=ceil(intck('month', "&startdate"d, "enddate"d)/3);
    Period = nperiods - Int(Intck('Month',Pro_End_Date_Ym,"&enddate"d)/3);
run;

Here's an example of how to calculate the boundaries that you wanted via a loop. This isn't helpful, except to demonstrate how you can count backwards and how a loop would work. 

data boundary_calculation;
	end_date="&enddate"d;
	start_date="&startdate"d;
	intervals=ceil(intck('month', start_date, end_date)/3);
	boundary_end=end_date;

	do i=1 to intervals;
		boundary_start=max(start_date, intnx('month', end_date, -3*i, 'e'));
		period=intervals-i+1;
		output;
		boundary_end=boundary_start;
	end;
	format end_date start_date boundary_start boundary_end date9.;
	keep period boundary_start boundary_end;
run;

There is no reason to have a macro loop or any sort of macro here beyond your start and end date.

You more than have an answer to your original question now. Good Luck.

[ertr]

Thank you very much @Tom and @Reeza,

Yes, I tried the other method because if your method won't work then I will use the other method.

Today I tried on real data but the results didn't match each other. I prepared new sample code. I attached the sample code because it includes many 58 dates then if I try to implement your method and try to implement manual method the results seem different.

Your Code

%Let StartDate=01JUN2010;
%Put StartDate;
%Let EndDate=31MAR2015;
%Put EndDate;
%Let NPeriods=%Sysfunc(Ceil(%Sysfunc(Intck(Month,"&StartDate"d,"&EndDate"D))/3));
%Put NPeriods;
 
Data Want;
  Set Have;
  Period = &NPeriods - Int(Intck('month',Date,"&Enddate"d)/3);
 Run;

Manual Code

Data Want2;
Set Have;
Length Period 4.;
Period = 0;
 
If '01JUN2010'd <= Date <= '30JUN2010'd Then Period = 1;
If '30JUN2010'd < Date <= '30SEP2010'd Then Period = 2;
If '30SEP2010'd < Date <= '31DEC2010'd Then Period = 3;
If '31DEC2010'd < Date <= '31MAR2011'd Then Period = 4;
If '31MAR2011'd < Date <= '30JUN2011'd Then Period = 5;
If '30JUN2011'd < Date <= '30SEP2011'd Then Period = 6;
If '30SEP2011'd < Date <= '31DEC2011'd Then Period = 7;
If '31DEC2011'd < Date <= '31MAR2012'd Then Period = 8;
If '31MAR2012'd < Date <= '30JUN2012'd Then Period = 9;
If '30JUN2012'd < Date <= '30SEP2012'd Then Period = 10;
If '30SEP2012'd < Date <= '31DEC2012'd Then Period = 11;
If '31DEC2012'd < Date <= '31MAR2013'd Then Period = 12;
If '31MAR2013'd < Date <= '30JUN2013'd Then Period = 13;
If '30JUN2013'd < Date <= '30SEP2013'd Then Period = 14;
If '30SEP2013'd < Date <= '31DEC2013'd Then Period = 15;
If '31DEC2013'd < Date <= '31MAR2014'd Then Period = 16;
If '31MAR2014'd < Date <= '30JUN2014'd Then Period = 17;
If '30JUN2014'd < Date <= '30SEP2014'd Then Period = 18;
If '30SEP2014'd < Date <= '31DEC2014'd Then Period = 19;
If '31DEC2014'd < Date <= '31JAN2015'd Then Period = 20;
If '31JAN2015'd < Date <= '31MAR2015'd Then Period = 21;
 
Run;

Sample data set is attached.

That is the reason why I try to build macro structure because there are less equal and less than symbols in the code. If your code will work, I'm gladly use.

Thank you

[ertr]

Hello @Tom,

Did you have a time to investigate my latest post. Should I accept your method as a solution. In my last sample your code gives different results that is the reason why I have some concerns whether I use your code or not.

Thank you

[Reeza]

Your code doesn't match your stated rules.

This isn't a 3 month interval.

If '31DEC2014'd < Date <= '31JAN2015'd Then Period = 20;

[ertr]

Yes, I realized late, apologize for that 😞 

Thank you for trying to help me madam 🙂

[Tom · Accepted solution]

You probably want to adjust the number of periods to handle the situation you have presented with using a start date that is not the end of the month.  For example try this program.  I added some values before and after the interval defined by startdate to enddate and added a IF statement to set those to PERIOD=0.

data have ;
  attrib date informat=date9. format=date9. ;
  input date @@ ;
cards;
01MAY2010 31MAY2010 01JUN2010 30JUN2010 01JUL2010 01AUG2010
01SEP2010 01OCT2010 01NOV2010 01DEC2010 01JAN2011 01FEB2011
01MAR2011 01APR2011 01MAY2011 01JUN2011 01JUL2011 01AUG2011
01SEP2011 01OCT2011 01NOV2011 01DEC2011 01JAN2012 01FEB2012
01MAR2012 01APR2012 01MAY2012 01JUN2012 01JUL2012 01AUG2012
01SEP2012 01OCT2012 01NOV2012 01DEC2012 01JAN2013 01FEB2013
01MAR2013 01APR2013 01MAY2013 01JUN2013 01JUL2013 01AUG2013
01SEP2013 01OCT2013 01NOV2013 01DEC2013 01JAN2014 01FEB2014
01MAR2014 01APR2014 01MAY2014 01JUN2014 01JUL2014 01AUG2014
01SEP2014 01OCT2014 31OCT2014 01NOV2014 30NOV2014 01DEC2014
31DEC2014 01JAN2015 01FEB2015 01MAR2015 31MAR2015 01APR2015
;
%let startdate=01jun2010;
%let enddate=31mar2015;
%let startdate2 = %sysfunc(intnx(month,"&startdate"d,0,b))-1;
%let startdate2 = %sysfunc(putn(&startdate2,date9));
%let nperiods=%sysfunc(ceil(%sysfunc(intck(month,"&startdate2"d,"&enddate"d))/3));
%put &=nperiods;

data want;
  set have;
  if date < "&startdate"d or date > "&enddate"d then period=0;
  else period = &nperiods - int(intck('month',date,"&enddate"d)/3);
run;

To see the results by period you could run this little data _null_ step since the input was sorted by DATE.

data _null_;
  set want ;
  by period notsorted;
  if first.period then put / period= z2. +1 @;
  put date @ ;
run;

Whch produces this list.

period=00  01MAY2010 31MAY2010
period=01  01JUN2010 30JUN2010
period=02  01JUL2010 01AUG2010 01SEP2010
period=03  01OCT2010 01NOV2010 01DEC2010
period=04  01JAN2011 01FEB2011 01MAR2011
period=05  01APR2011 01MAY2011 01JUN2011
period=06  01JUL2011 01AUG2011 01SEP2011
period=07  01OCT2011 01NOV2011 01DEC2011
period=08  01JAN2012 01FEB2012 01MAR2012
period=09  01APR2012 01MAY2012 01JUN2012
period=10  01JUL2012 01AUG2012 01SEP2012
period=11  01OCT2012 01NOV2012 01DEC2012
period=12  01JAN2013 01FEB2013 01MAR2013
period=13  01APR2013 01MAY2013 01JUN2013
period=14  01JUL2013 01AUG2013 01SEP2013
period=15  01OCT2013 01NOV2013 01DEC2013
period=16  01JAN2014 01FEB2014 01MAR2014
period=17  01APR2014 01MAY2014 01JUN2014
period=18  01JUL2014 01AUG2014 01SEP2014
period=19  01OCT2014 31OCT2014 01NOV2014 30NOV2014 01DEC2014 31DEC2014
period=20  01JAN2015 01FEB2015 01MAR2015 31MAR2015
period=00  01APR2015