topic Re: Dynamic Macro Loop - To Create An Automatic Loop in SAS Programming

Dynamic Macro Loop - To Create An Automatic Loop

ertr — Sun, 19 Jun 2016 15:05:09 GMT

Hello everyone,

I have a question which is little bit complicated.

I have a sample data set which includes dates, I need to determine these dates periods. I have a sample code as below.

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;

Months start with EndDate and decreasing three by three but when it comes StartDate it needs to catch up the excess between EndDate and StartDate. I mean first period months can increase one by one. If you can investigate my following code, you can understand better.

/*Purpose*/
%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
/*Purpose*/


Data Want;
Set Have;
Length Period 4.; 
Period = 0;
if '31JUL1989'd < PRO_END_DATE_YM <= '31AUG1989'd then Period = 1;/*Decreasing 1 month
because it cannot excess the StartDate*/
if '31AUG1989'd < PRO_END_DATE_YM <= '30NOV1989'd then Period = 2;/*Decreasing 3 months*/
if '30NOV1989'd < PRO_END_DATE_YM <= '28FEB1990'd then Period = 3;/*Decreasing 3 months*/
Run;

My purpose is just define the StartDate and EndDate and create the dates and Periods automatically. Actually, I need to create dates and periods being macro variables. But it seems to me it cannot possible for us. I just want to give your opinions.

The foregoing data step gives my desired output, but I want to take them dynamically.

Thank you,

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 19 Jun 2016 17:30:47 GMT

It's definitely possible. Use intnx to increment dates, make sure to look at the last parameter that aligns the dates to beginning or end of month.

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Sun, 19 Jun 2016 21:34:27 GMT

@Reeza,

Thank you very much for reminder, the following is going to provide me the my desired dynamic loop, however, I have some questions.

Why this statement doesn't work -> %Let FormattedDate=%Sysfunc(Intnx('Month',"&LatestDate."D,0,"E")); and the following code seems okay but I'm not sure, also seems little bit extended, are there any short methods?

Thank you

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
/*Hold variable in the Macro variable*/
Proc Sql;
Select Pro_End_Date_Ym Into:Dates Separated By " " From Have
Order By Pro_End_Date_Ym Desc;
Quit;

%Let Count=%Sysfunc(CountW(&Dates));/*Count of Dates*/
%Let LatestDate=%Scan(&Dates,-1);/*Hold latest Value to use in further steps*/
%Let FormattedDate=%Sysfunc(Intnx('Month',"&LatestDate."D,0,"E")); /*Why it doesn't work???*/
%Put &Dates;
%Put &Count;
%Put &LatestDate;
%Put &FormattedDate;
/*Convert values to latest day of month*/
%Macro Months(Month);
Data Want2(Drop=Pro_End_Date_Ym);
Set Have;
		%Do i=1 %To &Count.;
		%Let Var&i = %Scan(&Dates,&i,%str( ));
			Months=Intnx('Month',"&&Var&i"D,0,"E"); Output; Format Months Date9.;
		%End;
Run;
%Mend Months;
%Months; /*Why it brings 100 row instead of 10*/
/*Hold converted values in the Macro variable*/
Proc Sql;
Select Distinct Months Into:Dates2 Separated By " " From Want2
Order By Months Asc;
Quit;
%Let Count2=%Sysfunc(CountW(&Dates2));/*Count of Dates2*/
%Put &Dates2;
%Put &Count2;
%Macro Months2(Month2);
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
%If &Count GT 0 %Then %Do;
	%Do j = &Count %To 1 %By -1;
	%Let Var&j = %Scan(&Dates2,&j,%Str( ));
If Intnx('Month',"&&Var&j."D,-3,"E")< PRO_END_DATE_YM <=Intnx('Month',"&&Var&j."D,0,"E")Then Period = &j; 
    %End;
	%IF PRO_END_DATE_YM <=&LatestDate. %Then %Do;
	If Intnx('Month',"&StartDate."D,-3,"E")< PRO_END_DATE_YM <=Intnx('Month',"&LatestDate."D,0,"E")Then Period = &j; 
%End;
%End;
Run;
%Mend Months2;
%Months2;

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 19 Jun 2016 22:00:46 GMT

Wow. I don't think you need that much macro logic...start with your first macro, months. You get 100 results because you loop 10 times over each record. I think you only need the data step and no macro logic there. Try simplifying that as a starter.

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 19 Jun 2016 22:16:23 GMT

You don't quote parameters in %sysfunc.

Maybe post what you want your output to be and we can help you get there without all of that macro logic.

%Let FormattedDate=%Sysfunc(Intnx(Month,"&LatestDate."D,0,E));

Re: Dynamic Macro Loop - To Create An Automatic Loop

Tom — Sun, 19 Jun 2016 22:53:15 GMT

What are you actually trying to do? Hard to tell but it looks like you are just dividing a time interval into periods and calculating for the dates in your dataset which period it falls into? Why not just do that directly?

So if you have a variable DATE (perhaps your PRO_END_DATE_YM variable) and you want see how many months it is away from the START date then use the INTCK() function.

%let start=31JUL1989 ;

data want ;
  set have ;
  month_num = intck('month',"&start"d,DATE);
run;

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 19 Jun 2016 23:34:57 GMT

I agree with @Tom. Additionally, you may be able to use SAS QTR to calculate some of these and you don't need to create custom intervals depending on your requirements.

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Mon, 20 Jun 2016 08:36:18 GMT

Hello @Reeza and @Tom;

Thank you for your explanations, I'm going to try to explain what I am trying to do?

Actually, the following code creates my desired output

Data Want;
Set Have;
Length Period 4.; 
Period = 0;
if '31JUL1989'd < PRO_END_DATE_YM <= '31AUG1989'd then Period = 1;
/*Decreasing 1 month because it cannot excess the StartDate*/
if '31AUG1989'd < PRO_END_DATE_YM <= '30NOV1989'd then Period = 2;
/*Decreasing 3 months*/
if '30NOV1989'd < PRO_END_DATE_YM <= '28FEB1990'd then Period = 3;
/*Decreasing 3 months*/
Run;

I want to create this structure being dynamic.

But these date values for e example->"28FEB1990", "30NOV1989", "31AUG1989" are decreasing three by three but it doesn't mean it represents quarters.

For example, the customer is going to write Start and End Date as parametric ->

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

But in my foregoing example when it comes "31AUG1989" there is just one month between "31AUG1989" and StartDate("31JUL1989") so it shouldn't decrease three months it should decrease just one month.

My purpose is that get Start and End Date then create the Data Want which I wrote in my foregoing Data Step. I would like to remind that this is a sample data, the data will have more observations in real environment.

As a summary, I just determine the Start and End Date the it will create dates and periods

For Example ->"28FEB1990", "30NOV1989", "31AUG1989" and for Periods -> 1, 2 and 3 will automatically.

Something like this

%Let StartDate=31JUL1989;
%Let EndDate=28FEB1990;
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
if "&Macro2"d < PRO_END_DATE_YM <= "&Macro2"d then Period = &Macro2;
/*Decreasing 1 month because it cannot excess the StartDate*/
if "&Macro2"d  < PRO_END_DATE_YM <= "&Macro2"d then Period = &Macro2;
/*Decreasing 3 months*/
if "&Macro2"d < PRO_END_DATE_YM <= "&Macro2"d then Period = &Macro2;
/*Decreasing 3 months*/
Run;

Actually the following Months2 macro helps me but as you realize before this macro I did many steps

%Macro Months2(Month2);
Data Want;
Set Have;
Length Period 4.; 
Period = 0;
%If &Count GT 0 %Then %Do;
	%Do j = &Count %To 1 %By -1;
	%Let Var&j = %Scan(&Dates2,&j,%Str( ));
If Intnx('Month',"&&Var&j."D,-3,"E")< PRO_END_DATE_YM <=Intnx('Month',"&&Var&j."D,0,"E")Then Period = &j; 
    %End;
	%IF PRO_END_DATE_YM <=&LatestDate. %Then %Do;
	If Intnx('Month',"&StartDate."D,0,"E")< PRO_END_DATE_YM <=Intnx('Month',"&LatestDate."D,0,"E")Then Period = &j; 
%End;
%End;
Run;
%Mend Months2;
%Months2;

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Mon, 20 Jun 2016 10:58:13 GMT

Why is the first one only one month, not three like the others? That's the key logic that needs to be captured.

Besides that, it's relatively easy:

%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*/
Run;

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Mon, 20 Jun 2016 22:01:27 GMT

Hello again,

@Tom and @Reeza,

Thank you for preparing a code but Start Date and End Date can change and Periods also depends on the Start Date and End Date.

So I need to create this structere for Macro variables, I mean StartDate and EndDate.

What I mean is that what if End Date is "31AUG1990" instead of "28FEB1990", Periods also will change for Start Date and End Date.

I will be happy, If I get some direction from you. Thanks

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Mon, 20 Jun 2016 22:22:51 GMT

The dates will change but rules have to stay the same. Whether its a macro or non macro solution this is true. So what are your rules.

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Mon, 20 Jun 2016 22:44:30 GMT

Sorry @Reeza and @Tom,

English is not my mother language, I guess that is the reason why I cannot explain myself clear.

Let's say I prepared your code then I delivered the code to the customer.

Here is your code;

%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*/
Run;

Then someday they want to implement your code for different data set. Let's say they want to use following data set

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;

Then they realize some of the periods comes zero and they also don't know SAS code.

I want to prevent this situation. Could I make myself clear?

I think Macro code is only solution, what do you think?

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Mon, 20 Jun 2016 22:56:34 GMT

@Reeza and @Tom,

EndDate also will change like this ->

%Let StartDate=31JUL1989;
%Let EndDate=31DEC1991;

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Tue, 21 Jun 2016 00:39:45 GMT

No. Given what you've shown I still believe any logic you implement via macros can easily be accomodated in a data step.

The rules need to be clear.

What happens if the period is 0? What defines a period, is it the 3 months periods from Dec to Feb, Mar - May, Jun-Aug, Sep - Dec, except truncated for if the start date is somewhere in between?

You can make it a macro to take a different data set, different variable and start period. That's fine. But the logic to calculate the periods should be implemented as a data step.

%macro date_intervals(datain = , variable = , date_start= , date_end =, dataout=);


data &dataout;
set &datain;

*date logic stuff goes here;


run;

%mend;

Re: Dynamic Macro Loop - To Create An Automatic Loop

Tom — Tue, 21 Jun 2016 02:05:53 GMT

So if you want to divide the internal into 3 month "PERIODS" you can do it arithmetically. No need for loops of any kind.

data have;
  input date @@ ;
  informat date date9. ;
  format date date9. ;
datalines;
22AUG1989 23AUG1989 24AUG1989 25AUG1989 26AUG1989 15SEP1989
05OCT1989 15OCT1989 03FEB1990 11FEB1990 03JUN1990 22SEP1990
11JAN1991 02MAY1991 21AUG1991 10DEC1991
;;;;

%let startdate=31jul1989;

data want;
  set have;
  period = 1 + ceil((intck('month',"&startdate"d,date)-1)/3) ;
run;

It might even be possible for you to create the STARTDATE macro variable from the input data.

proc sql noprint ;
  select intnx('month',min(date),-1,'e') format=date9.
    into :startdate
  from have
  ;
quit;

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Sun, 26 Jun 2016 17:19:55 GMT

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;

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 26 Jun 2016 17:37:24 GMT

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.

Re: Dynamic Macro Loop - To Create An Automatic Loop

ertr — Sun, 26 Jun 2016 20:52:32 GMT

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,

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 26 Jun 2016 20:57:14 GMT

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

Re: Dynamic Macro Loop - To Create An Automatic Loop

Reeza — Sun, 26 Jun 2016 21:10:54 GMT

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 -> "<=".