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;
This is difficult to answer for a number of reasons.
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?
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
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:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.