How to reduce time taken by sas macro code to run

[tnachis]

I need help with my monthly report sas code below:
Firstly the code takes too long to run while the data is relatively small. When it completes a message that reads: The contents of log is too large.

Please can you check what the issue which my code?

%MACRO monthly_schedule;
%let flag=n;
%do %until(&flag=y);
proc sql noprint;
select count(update_flag) into:flag_cnt
from work.schedule_dt
where update_flag = 1 and Business in ('Spain' 'England')
; quit;

%PUT &flag_cnt.;

%if &end_date. > &lastest_refrsh_dt. %then
%do;
/*Execute vbt status rpt and email to recipients */
%if %eval(&flag_cnt)=9 %then
%do;
%let flag_cnt=Y;
%put The flags are updated.;

%vbt_status_rpt;
%send_vbt_email;

/*update Config table*/
proc sql noprint;
delete from config table where location ='abc' AND SUB ='cde' and value ='31-Mar-22'd;
quit;

proc sql noprint;
INSERT INTO config table (Location, SUB, Value)
VALUES ( "abc", "cde", '30-Apr-22'd);
quit;

%end;
%end;
%end;
%mend vbt_monthly_schedule;

%vbt_monthly_schedule;
%else
%do;
%put The flags are not updated;
/* Send email if data is not available after the 6th Day */
%macro send_sixthdy_email;
%let today = %sysfunc(today() );
%if( %sysfunc(intnx(month, &today., 0, B) ) NE &lastest_refrsh_dt.
AND %sysfunc(day(&today.) ) GE 3
AND 2 LE %sysfunc(weekday(&today.) ) LE 3
) %then
%do;
%put lastest_refrsh_dt is &lastest_refrsh_dt.;
/* send email if data not ready by the 6th */
%emailnotification(ctnt=%str(data not available),
subj=%str(data not available ),
mailist ="j@yah");
%end;
%mend send_sixthdy_email;

%end;
%end;
%mend monthly_schedule;

[PaigeMiller]

This is difficult to answer for a number of reasons.

1. We don't have your data
2. You are doing some looping which can be a problem, hard to say, we don't know what the looping is doing or how many iterations it will take
3. You are using macros that we don't have
4. Your code is not properly indented, it's difficult for humans to look at
5. You have a macro definition containing and inside a loop is another macro definition, usually this is to be avoided and can cause the program to take a long time (and you never all this second macro anyway)

Can you look at the log and see where the time delay is happening?

Can you place %PUT statements in the code to print out the current date/time so that you can identify where things are taking a long time?

[Patrick]

Just by "pruning" the code to the bits that might actually do something it looks to me that if macro call %vbt_status_rpt; doesn't set &flag=y  then you've coded yourself an infinite loop.

%MACRO monthly_schedule;
  %let flag=n;

  %do %until(&flag=y);

    proc sql noprint;
      select count(update_flag) into:flag_cnt
        from work.schedule_dt
          where update_flag = 1 and Business in ('Spain' 'England')
      ;
    quit;

    %PUT &flag_cnt.;

    %if &end_date. > &lastest_refrsh_dt. %then
      %do;
        /*Execute vbt status rpt and email to recipients */
        %if %eval(&flag_cnt)=9 %then
          %do;
            %let flag_cnt=Y;
            %put The flags are updated.;

            %vbt_status_rpt;
            %send_vbt_email;

            /*update Config table*/
            proc sql noprint;
              delete from config table where location ='abc' AND SUB ='cde' and value ='31-Mar-22'd;
            quit;

            proc sql noprint;
              INSERT INTO config table (Location, SUB, Value)
                VALUES ( "abc", "cde", '30-Apr-22'd);
            quit;

          %end;
      %end;
  %end;
%else
  %do;
    %put The flags are not updated;
  %end;
%end;
%mend monthly_schedule;

As an impression of what you've shared: Your code needs some "cleaning-up". Try to write macros that are modules on it's own and also try to not have hardcoded values within the macro that require regular change (like the hardcoded dates).

Below bit is certainly not good macro design/coding practice

[Kurt_Bremser]

Maxim 2: Read the Log. It will alert you to several serious issues.

The first I see is that this statement:

%mend vbt_monthly_schedule;

has no corresponding %MACRO statement.

Some rules:

• NEVER define macros within other macros, only use them
• Use indentation so your %DO - %END and DO - END blocks become visible
• when you have longer nested blocks, it is a good idea to add a comment to the END which describes to which DO it belongs

See this changed and annotated code that points out issues:

%macro send_sixthdy_email;
%let today = %sysfunc(today() );
%if( %sysfunc(intnx(month, &today., 0, B) ) NE &lastest_refrsh_dt.
AND %sysfunc(day(&today.) ) GE 3
AND 2 LE %sysfunc(weekday(&today.) ) LE 3
) %then
%do;
%put lastest_refrsh_dt is &lastest_refrsh_dt.;
/* send email if data not ready by the 6th */
%emailnotification(ctnt=%str(data not available),
subj=%str(data not available ),
mailist ="j@yah");
%end;
%mend send_sixthdy_email;

%MACRO monthly_schedule;

%let flag=n;
%do %until(&flag=y);

  proc sql noprint;
  select count(update_flag) into:flag_cnt
  from work.schedule_dt
  where update_flag = 1 and Business in ('Spain' 'England')
  ;
  quit;

  %PUT &flag_cnt.;
  %if &end_date. > &lastest_refrsh_dt.
  %then %do;
    /* Execute vbt status rpt and email to recipients */
    %if %eval(&flag_cnt)=9
    %then %do;
      %let flag_cnt=Y;
      %put The flags are updated.;

      %vbt_status_rpt;
      %send_vbt_email;

      /*update Config table*/
      proc sql noprint;
      delete from config table where location ='abc' AND SUB ='cde' and value ='31-Mar-22'd;
      quit;

      proc sql noprint;
      INSERT INTO config table (Location, SUB, Value)
      VALUES ( "abc", "cde", '30-Apr-22'd);
      quit;

    %end; /* if %eval(&flag_cnt)=9 */
  %end; /* if &end_date. > &lastest_refrsh_dt. */
%end; /* %do %until(&flag=y); */
%mend vbt_monthly_schedule; /* ERROR !!! */
%vbt_monthly_schedule;
%else %do;
  %put The flags are not updated;
  /* Send email if data is not available after the 6th Day */
  /* there is no code to do anything here !!! */
%end;
%end; /* one %END too much !!! */
%mend monthly_schedule;

It seems that you tried to do too much in one effort. Build your macro(s) small step by small step.