BookmarkSubscribeRSS Feed
Shradha1
Obsidian | Level 7

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();	

 

4 REPLIES 4
Kurt_Bremser
Super User

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
Obsidian | Level 7
@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
Super User

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
Super User

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.;

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2198 views
  • 1 like
  • 3 in conversation