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 more
