How to create a Macro loop of YYYY-MM and a variable increment

[Scooby3g]

Hi there, I want to create a backbone table using Macro at a starting YYYY-MM that equals (Variable_NAME_TIME) to 1 and then as time moves forward the 'Variable_NAME_TIME' will increase by a increment of 1 until previous month. In this case Sept 2022.

for example:

Yr_mon      Variable_NAME_TIME

2021-04      1

2021-05      2

2021-06      3

2021-07      4

2021-08      5

2021-09      6

I was able to come up with a macro loop to get YYYY-Mon but not how to get Variable_NAME_TIME.  Any suggestions or advice would be very appreciated! 

*SET MACRO BEGIN DATE;
%let begin_date = '2021-04';
%put &begin_date;
* SET MACRO END DATE IN YYYY-MM;
%let Prev_Month_short = %sysfunc(intnx(month,%sysfunc(date()),-1),YYMMD.);
%put &Prev_Month_short;
* SET MACRO CURRENT DATE IN YYYY-MM;
%let Cur_Month = %sysfunc(today(),YYMMD.);
%put &Cur_Month;

%macro date_loop(start,end);
	%let start=%sysfunc(inputn(&start,anydtdte9.));
		%let end=%sysfunc(inputn(&end,anydtdte9.)); 
   			%let dif=%sysfunc(intck(month,&start,&end));
	%do i=0 %to &dif;
		%let date=%sysfunc(intnx(month,&start,&i,b),YYMMD.); 
      	%put &date;
	%end;
%mend date_loop;
%date_loop(&begin_date , &Prev_Month_short)

[Reeza]

Use your i macro variable as is, or add 1?

*SET MACRO BEGIN DATE;
%let begin_date = '2021-04';
%put &begin_date;
* SET MACRO END DATE IN YYYY-MM;
%let Prev_Month_short = %sysfunc(intnx(month,%sysfunc(date()),-1),YYMMD.);
%put &Prev_Month_short;
* SET MACRO CURRENT DATE IN YYYY-MM;
%let Cur_Month = %sysfunc(today(),YYMMD.);
%put &Cur_Month;

%macro date_loop(start,end);
	%let start=%sysfunc(inputn(&start,anydtdte9.));
		%let end=%sysfunc(inputn(&end,anydtdte9.)); 
   			%let dif=%sysfunc(intck(month,&start,&end));
	%do i=0 %to &dif;
		%let date=%sysfunc(intnx(month,&start,&i,b),YYMMD.); 
      	%put &date;
      	%put variable_name_time = &i.;
      	%put variable_name_time = %eval(&i.+1);
	%end;
%mend date_loop;
%date_loop(&begin_date , &Prev_Month_short)

---

@Scooby3g wrote:

Hi there, I want to create a backbone table using Macro at a starting YYYY-MM that equals (Variable_NAME_TIME) to 1 and then as time moves forward the 'Variable_NAME_TIME' will increase by a increment of 1 until previous month. In this case Sept 2022.

for example:

Yr_mon      Variable_NAME_TIME

2021-04      1

2021-05      2

2021-06      3

2021-07      4

2021-08      5

2021-09      6

I was able to come up with a macro loop to get YYYY-Mon but not how to get Variable_NAME_TIME.  Any suggestions or advice would be very appreciated! 

*SET MACRO BEGIN DATE;
%let begin_date = '2021-04';
%put &begin_date;
* SET MACRO END DATE IN YYYY-MM;
%let Prev_Month_short = %sysfunc(intnx(month,%sysfunc(date()),-1),YYMMD.);
%put &Prev_Month_short;
* SET MACRO CURRENT DATE IN YYYY-MM;
%let Cur_Month = %sysfunc(today(),YYMMD.);
%put &Cur_Month;

%macro date_loop(start,end);
	%let start=%sysfunc(inputn(&start,anydtdte9.));
		%let end=%sysfunc(inputn(&end,anydtdte9.)); 
   			%let dif=%sysfunc(intck(month,&start,&end));
	%do i=0 %to &dif;
		%let date=%sysfunc(intnx(month,&start,&i,b),YYMMD.); 
      	%put &date;
	%end;
%mend date_loop;
%date_loop(&begin_date , &Prev_Month_short)

---

[ballardw]

---

@Scooby3g wrote:

Hi there, I want to create a backbone table using Macro at a starting YYYY-MM that equals (Variable_NAME_TIME) to 1 and then as time moves forward the 'Variable_NAME_TIME' will increase by a increment of 1 until previous month. In this case Sept 2022.

---

Table to me implies a data set. I don't see anything in your code creating a data set and don't see any obvious for use for this.

If you actually want a data set I strongly recommend that you actually use a DATE value instead of a string or other value that imitates a date. There are a large number of tools that SAS supplies to deal with dates that will make it easier to use in the long run.

A data set could be made this way, if I understand your requirement.

%let begin_date = 202104;

data want;
  yr_mon = input("&begin_date.",yymmn6.);
  do until (yr_mon > intnx('month',today(),-1,'B') );
     Variable_NAME_TIME+1;
     output;
     yr_mon= intnx('month',yr_mon,1,'B');
  end;
  format yr_mon  yymmd7.;
run;

Your code actually shows a reason not to use values like YYYY-MM as you are using INPUT repeatedly to get a date value. Do it once.

Typically formatting macro variable values holding dates makes any use of the result more complicated.

Also I don't see a use for that macro, unless this expected to be just a part of something else as none of the values you use/create are available outside of that macro.

[Tom · Accepted solution]

I am not sure what you are asking for.  You already have a counter variable, you named in I for some reason.  Just rename it to the new name.

%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.)); 
%do variable_name_time=1 %to %sysfunc(intck(month,&start,&end))+1;
  %let yr_mon=%sysfunc(intnx(month,&start,&variable_name_time-1,b),YYMMD7.); 
  %put &=yr_mon &=variable_name_time;
%end;
%mend date_loop;
%date_loop(01APR2021,01SEP2021);

Result:

119  %date_loop(01APR2021,01SEP2021);
YR_MON=2021-04 VARIABLE_NAME_TIME=1
YR_MON=2021-05 VARIABLE_NAME_TIME=2
YR_MON=2021-06 VARIABLE_NAME_TIME=3
YR_MON=2021-07 VARIABLE_NAME_TIME=4
YR_MON=2021-08 VARIABLE_NAME_TIME=5
YR_MON=2021-09 VARIABLE_NAME_TIME=6

[Scooby3g]

Thanks so much Tom!! This is exactly what I was looking to get as a backbone table! =D

[Tom]

---

@Scooby3g wrote:

Thanks so much Tom!! This is exactly what I was looking to get as a backbone table! =D

---

What do you mean by "TABLE"?  A report like in Tables, Listing and Figures?  Or a dataset?

Either way that code did not create any table.  It just sequentially set a pair of macro variables.

[Scooby3g]

I was planning on using it as a backbone to left join another dataset to it, rather than manually adding a month to it as time progress.

Could I trouble you with one more question? Is it possible to add a total for each year within this data step? Inserting it below Variable_NAME_TIME = 9 and having it being reflected as Variable_NAME_TIME = 10?

Yr_mon      Variable_NAME_TIME

2021-04      1

2021-05      2

2021-06      3

2021-07      4

2021-08      5

2021-09      6

2021-10      7

2021-11      8

2021-12      9

2021-Total   10

2022-01      11

2022-02      12

2022-03      13

[Tom]

If you want a DATASET why are you using macro code?

Just write a data step instead.  It is soooooo much easier to work with data in a data step than trying to do it in macro code.

/*%macro date_loop(start,end); */
%let start=01APR2021;
%let end=01MAR2022;
data want;
  length yr_mon $10 variable_name_time 8;
  start = intnx('month',input("&start",anydtdte20.),0);
  end = intnx('month',input("&end",anydtdte20.),0);
  do offset=0 to intck('month',start,end);
    date=intnx('month',start,offset);
    yr_mon = put(date,yymmd7.);
    variable_name_time+1;
    output;
    if month(date)=12 then do;
      variable_name_time+1;
      yr_mon=catx('-',year(date),'Total');
      output;
    end;
  end;
  format start end date yymmdd10.;
 * keep yr_mon variable_name_time;
run;
/*
%mend;
%date_loop(01APR2021,01MAR2022);
*/
proc print data=want;
run;

Result:

                     variable_
Obs      yr_mon      name_time         start           end    offset          date

  1    2021-04            1       2021-04-01    2022-03-01       0      2021-04-01
  2    2021-05            2       2021-04-01    2022-03-01       1      2021-05-01
  3    2021-06            3       2021-04-01    2022-03-01       2      2021-06-01
  4    2021-07            4       2021-04-01    2022-03-01       3      2021-07-01
  5    2021-08            5       2021-04-01    2022-03-01       4      2021-08-01
  6    2021-09            6       2021-04-01    2022-03-01       5      2021-09-01
  7    2021-10            7       2021-04-01    2022-03-01       6      2021-10-01
  8    2021-11            8       2021-04-01    2022-03-01       7      2021-11-01
  9    2021-12            9       2021-04-01    2022-03-01       8      2021-12-01
 10    2021-Total        10       2021-04-01    2022-03-01       8      2021-12-01
 11    2022-01           11       2021-04-01    2022-03-01       9      2022-01-01
 12    2022-02           12       2021-04-01    2022-03-01      10      2022-02-01
 13    2022-03           13       2021-04-01    2022-03-01      11      2022-03-01

[Scooby3g]

Thanks for the suggestion Tom!! I'm going to try this method instead!