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.
%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 selectacctrefno, 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 selectacctrefno, archive_date
from work.myTable
where archive_date = &beg
order by acctrefno
;quit;
proc sql;
create table b
as selectacctrefno, 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.
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 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?
%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 selectacctrefno, 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 selectacctrefno, archive_date
from work.myTable
where archive_date = &beg
order by acctrefno
;quit;
proc sql;
create table b
as selectacctrefno, 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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: