BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hporter
Obsidian | Level 7

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
%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. 


 

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
hporter
Obsidian | Level 7
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
Diamond | Level 26

@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?

--
Paige Miller
hporter
Obsidian | Level 7
I thought it was an error on the last line invoking the macro, my mistake.
Reeza
Super User
%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
Obsidian | Level 7
Yes the dates in the first table (myTable) are hardcoded and won't change for some time. They look like 01JUN2022, 01JUL2022, 01AUG2022 etc.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 596 views
  • 1 like
  • 3 in conversation