looping thru dates dynamically

[hporter]

I have a dataset with account numbers and archive dates spanning 13 months. I then need to do some analysis on comparing 1 month to the next month. I have this all coded and it works, but I have to manually input the beginning and end dates to do the comparison and I would like to loop through and dynamically generate all the tables.

Basically my begin date for the first round needs to be 01JUN2022 and my end date needs to be 01JUL2022, which is represented by this:
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -12), date9.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -11), date9.);

For the 2nd round I need my begin date to be 01JUL2022 and my end date to be 01AUG2022, and so on and so forth thru 01JUL2023.

Here is an edited version of the code to make it as simple as possible:

%let beg = %sysfunc(intnx(month, %sysfunc(today()), -12), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -11), yymmddn8.);

proc sql;
create table myTable
as select

acctrefno, archive_date

from myTable

where archive_date in (
'01jul2022'd,
'01aug2022'd,
'01sep2022'd,
'01oct2022'd,
'01nov2022'd,
'01dec2022'd,
'01jan2023'd,
'01feb2023'd,
'01mar2023'd,
'01apr2023'd,
'01may2023'd,
'01jun2023'd
'01jul2023'd)

order by archive_date
;quit;

proc sql;
create table a
as select

acctrefno, archive_date

from work.myTable

where archive_date = &beg
order by acctrefno
;quit;

proc sql;
create table b
as select

acctrefno, archive_date

from work.myTable

where archive_date = &end
order by acctrefno
;quit;

Each time I run the code I am switching the 
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -12), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -11), yymmddn8.); 
to reflect the next round. So in this case that is round 1. The next would be:
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -11), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -10), yymmddn8.);
the next would be:
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -10), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -9), yymmddn8.);

Any help to automate this is greatly appreciated. 

[PaigeMiller]

A macro loop would work

EXAMPLE: UNTESTED CODE

%macro dothis;
    %do i=-12 %to -1;
         %let beg = %sysfunc(intnx(month, %sysfunc(today()), &i), yymmddn8.);
         %let end = %sysfunc(intnx(month, %sysfunc(today()), %eval(&i+1), yymmddn8.); 
/* Any other code you want goes here */
     %end;
%mend;
%dothis

Also, usually you would not want to format macro variable values, the unformatted values are what you need. That way you don't have to first format the values and then remove the format for use in code. Although it depends on what you have already coded.

[hporter]

Thank you I think this will work. I'm new to macro's and I did get the error:
ERROR: Expected close parenthesis after macro function invocation not found.

I used exactly what you had entered, is it wanting there to be an open/close paranthese after the %dothis?

[PaigeMiller]

---

@hporter wrote:
Thank you I think this will work. I'm new to macro's and I did get the error:
ERROR: Expected close parenthesis after macro function invocation not found.

I used exactly what you had entered, is it wanting there to be an open/close paranthese after the %dothis?

---

Surely you can find mis-matched parentheses in a line of code. I don't have to do that for you, do I?

[hporter]

I thought it was an error on the last line invoking the macro, my mistake.

[Reeza · Accepted solution]

%macro loop;

%do i=-12 %to -1;

%let beg = %sysfunc(intnx(month, %sysfunc(today()), &i., yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), %eval(&.i+1)), yymmddn8.);



proc sql;
create table myTable
as select
acctrefno, archive_date
from myTable
where archive_date in (
'01jul2022'd,
'01aug2022'd,
'01sep2022'd,
'01oct2022'd,
'01nov2022'd,
'01dec2022'd,
'01jan2023'd,
'01feb2023'd,
'01mar2023'd,
'01apr2023'd,
'01may2023'd,
'01jun2023'd
'01jul2023'd)

order by archive_date
;quit;

 

proc sql;
create table a_&i.
as select

acctrefno, archive_date

 

from work.myTable


where archive_date = &beg
order by acctrefno
;quit;


proc sql;
create table b_&i.
as select
acctrefno, archive_date
from work.myTable
where archive_date = &end
order by acctrefno
;quit;

%end;

%mend loop;

%loop;

untested. Are the dates in the SQL hardcoded? I think you could probably do this without macro's in a different way, using group by logic, but depends on the next steps to some degree.

---

@hporter wrote:

I have a dataset with account numbers and archive dates spanning 13 months. I then need to do some analysis on comparing 1 month to the next month. I have this all coded and it works, but I have to manually input the beginning and end dates to do the comparison and I would like to loop through and dynamically generate all the tables.

Basically my begin date for the first round needs to be 01JUN2022 and my end date needs to be 01JUL2022, which is represented by this:
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -12), date9.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -11), date9.);

For the 2nd round I need my begin date to be 01JUL2022 and my end date to be 01AUG2022, and so on and so forth thru 01JUL2023.

Here is an edited version of the code to make it as simple as possible:

%let beg = %sysfunc(intnx(month, %sysfunc(today()), -12), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -11), yymmddn8.);

proc sql;
create table myTable
as select

acctrefno, archive_date

from myTable

where archive_date in (
'01jul2022'd,
'01aug2022'd,
'01sep2022'd,
'01oct2022'd,
'01nov2022'd,
'01dec2022'd,
'01jan2023'd,
'01feb2023'd,
'01mar2023'd,
'01apr2023'd,
'01may2023'd,
'01jun2023'd
'01jul2023'd)

order by archive_date
;quit;

proc sql;
create table a
as select

acctrefno, archive_date

from work.myTable

where archive_date = &beg
order by acctrefno
;quit;

proc sql;
create table b
as select

acctrefno, archive_date

from work.myTable

where archive_date = &end
order by acctrefno
;quit;

Each time I run the code I am switching the 
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -12), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -11), yymmddn8.); 
to reflect the next round. So in this case that is round 1. The next would be:
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -11), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -10), yymmddn8.);
the next would be:
%let beg = %sysfunc(intnx(month, %sysfunc(today()), -10), yymmddn8.);
%let end = %sysfunc(intnx(month, %sysfunc(today()), -9), yymmddn8.);

Any help to automate this is greatly appreciated. 

---

[hporter]

Yes the dates in the first table (myTable) are hardcoded and won't change for some time. They look like 01JUN2022, 01JUL2022, 01AUG2022 etc.