How to write a macro to run a code every Monday of every month?

[Shradha1]

I have a block of code which extracts customers who had made a transfer in the last month.  This macro is currently monthly and runs once a month.

Now, I want to run this code on every Monday for the last 4 months.

How can I use a macro to achieve this and convert this exercise from monthly to weekly so that this macro runs on every Monday and we get the list of customers who made a transfer in last 7 days?

%LET Mon_ST = 202111;			
%LET Mon_EN = 202201;
	
Option obs = Max;			
			
%macro copy_data();

%DO Mnth = &Mon_ST. %TO &Mon_EN. ;			
%IF %SUBSTR(&Mnth.,5,2) = 13 %THEN %LET Mnth =	%EVAL(%SUBSTR(&Mnth.,1,4)+1)01;	
%let YYYYMMDD =	%SYSFUNC(INTNX(Month,"%SYSFUNC(MDY(%SUBSTR(&Mnth.,5,2),01,%SUBSTR(&Mnth.,1,4)),DATE9.)"D,0,E),YYMMddN8.);
	
	%Let Track_strt=%SYSFUNC(INTNX(Day,"%SYSFUNC(MDY(%SUBSTR(&YYYYMMDD.,5,2),%SUBSTR(&YYYYMMDD.,7,2),%SUBSTR(&YYYYMMDD.,1,4)),DATE9.)"D,-30,B),YYMMDDN8.);
	%Let Track_endt=%SYSFUNC(INTNX(Day,"%SYSFUNC(MDY(%SUBSTR(&YYYYMMDD.,5,2),%SUBSTR(&YYYYMMDD.,7,2),%SUBSTR(&YYYYMMDD.,1,4)),DATE9.)"D,0,E),YYMMDDN8.);

%Put  &Track_endt. &Track_strt.;	

/***** Block of Code extracting transfer history information in last 1 month *****/

%end;			

%mend;			
%copy_data();	

[Kurt_Bremser]

That's not a macro issue, that's a scheduling issue. On a UNIX SAS server, I would simply use cron/crontab to run the code on the desired days.

To limit to data of the last 7 days, use a macro variable:

%let track_strt = %eval(%sysfunc(today()) - 7);
%let track_endt = %sysfunc(today());

These values are the raw SAS date values and can be compared directly to SAS dates in the datasets (no conversion needed). See Maxim 28: Macro Variables Need No Formats.

Similarly, you use the raw values in calculations:

%let mnth=202201;
%let yyyymmdd =	%sysfunc(intnx(month,%sysfunc(inputn(&mnth.01,yymmdd8.)),0,e),yymmddn8.);
%put &yyyymmdd.;

Also note how I reduced statement complexity by using the INPUTN function instead of MDY and multiple %SUBSTR's.

[Shradha1]

@Kurt_Bremser Agreed!!. From a futuristic view scheduling this task would help. But if I want to do it retrospectively for the past 3 months, then I would need to create a macro and run it manually. That's where I am getting stuck.

[Kurt_Bremser]

So you want weekly data, starting with 15 or 16 weeks back from today?

First, I would limit the data with a WHERE condition, and secondly, use one of the YYWEEK formats for the dates in the reporting procedures (which then use the formatted values).

e.g.

proc means data=sashelp.citiday mean;
class date;
format date yyweeku7.;
var dcd1m;
run;

You can see that the results are grouped by weeks.

[ballardw]

Instead of messing about with a bunch of %sysfunc and the  MDY function with way too many %substr calls try using the INPUTN with the YYMMN informat.

Example:

%LET Mon_ST = 202111;

%let date = %sysfunc(inputn(&mon_st.,yymmn6.));

%let textdate= %sysfunc(putn(&date., date9.));

%put Textdate is: &textdate.;