Macro Developing Date Value Ends with Invalid Date/Time/DateTime Constant Error Message

[hnb_matt_d]

Hey, Everyone,

I have code that's developing a date value based on the date of execution (code is set to run on the 7th and 21st of each month).  Here's the code for the date development:

data _null_;
call symput('day_of_month',put(intnx('day',today(),0,'b'),day.));
run;

%put day_of_month : &day_of_month;

%macro day_of_month_dependency;

%if &day_of_month=7 %then %do;

data num_days;
prev_month=intnx('month',today(),0)-intnx('month',today(),-1);
run;

proc sql noprint;
select 	prev_month
into : prev_month
from num_days
;quit;

%put prev_month : &prev_month;

%if &prev_month=30 %then %do;

data days_diff;
start_date=intnx('days',today(),-16);
run;
%end;

%if &prev_month=31 %then %do;

data days_diff;
start_date=intnx('days',today(),-17);
run;
%end;

%if &prev_month=28 %then %do;

data days_diff;
start_date=intnx('days',today(),-14);
run;
%end;

%if &prev_month=29 %then %do;

data days_diff;
start_date=intnx('days',today(),-15);
run;
%end;

proc sql noprint;
select 	start_date format=date9. as start_date
into : start_date
from 	days_diff
;quit;

%put start_date : &start_date;
%end;

%else %do;

data _null_;
call symput('start_date',put(intnx('day',today(),-14,'b'),date9.));
run;

%put start_date : &start_date;
%end;
%mend;
%day_of_month_dependency;

The most 6/7 produced the correct date of 21MAY2024.  But when following steps attempted to utilize that date, an error was thrown indicating an invalid date/time/datetime constant, and I'm not quite sure how this is occurring.    

Next step:

proc sql;
create table archive.alert_add_augits_&report_date. as
select 	audit_internal_id
		,user_internal_id
		,create_date as IR_ADD_DATE
		,event
		,note
from 	rcmr.acm_audits 
where 	datepart(create_date) between "&start_date."d and "&end_date."d
and 	event in ('Alert(s) added to Case' 'Added as related item')
and 	note like '%REFERRAL%'
;quit;

Log:

507        %put day_of_month : &day_of_month;
day_of_month :  7
508        
509        %macro day_of_month_dependency;
510        
511        %if &day_of_month=7 %then %do;
512        
513        data num_days;
514        prev_month=intnx('month',today(),0)-intnx('month',today(),-1);
515        run;
516        
517        proc sql noprint;
518        select 	prev_month
519        into : prev_month
520        from num_days
521        ;quit;
522        
523        %put prev_month : &prev_month;
524        
525        %if &prev_month=30 %then %do;
526        
527        data days_diff;
528        start_date=intnx('days',today(),-16);
529        run;
530        %end;
531        
532        %if &prev_month=31 %then %do;
11                                                         The SAS System                                 09:30 Friday, June 7, 2024

533        
534        data days_diff;
535        start_date=intnx('days',today(),-17);
536        run;
537        %end;
538        
539        %if &prev_month=28 %then %do;
540        
541        data days_diff;
542        start_date=intnx('days',today(),-14);
543        run;
544        %end;
545        
546        %if &prev_month=29 %then %do;
547        
548        data days_diff;
549        start_date=intnx('days',today(),-15);
550        run;
551        %end;
552        
553        proc sql noprint;
554        select 	start_date format=date9. as start_date
555        into : start_date
556        from 	days_diff
557        ;quit;
558        
559        %put start_date : &start_date;
560        %end;
561        
562        %else %do;
563        
564        data _null_;
565        call symput('start_date',put(intnx('day',today(),-14,'b'),date9.));
566        run;
567        
568        %put start_date : &start_date;
569        %end;
570        %mend;
571        %day_of_month_dependency;

NOTE: The data set WORK.NUM_DAYS has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

prev_month :       31

NOTE: The data set WORK.DAYS_DIFF has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
12                                                         The SAS System                                 09:30 Friday, June 7, 2024


NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

start_date : 21MAY2024
572        
573        * Isolate IR add audit history for period from the 25th of the preceding month or the 7th of the curren month, depending
573      ! on report execution date. ;
574        * The note indicating '%REFERRAL%' excludes all other alert type adds. ;
575        proc sql;
576        create table archive.alert_add_augits_&report_date. as
577        select 	audit_internal_id
578        		,user_internal_id
579        		,create_date as IR_ADD_DATE
580        		,event
581        		,note
582        from 	rcmr.acm_audits
583        where 	datepart(create_date) between "&start_date."d and "&end_date."d
WARNING: Apparent symbolic reference START_DATE not resolved.
ERROR: Invalid date/time/datetime constant "&start_date."d.
584        and 	event in ('Alert(s) added to Case' 'Added as related item')
585        and 	note like '%REFERRAL%'
586        ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
586      !  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Job is currently automated through crontab.  When I run this manually, I usually don't get an error (I ran it successfully today without issue).  

Thanks in advance for any and all direction.  

[Kurt_Bremser · Accepted solution]

Your code looks like an honest entry fo the Obfuscated SAS Code Contest.

intnx('day',today(),0,'b')

will always result in

today()

, so why use INTNX at all?

data num_days;
prev_month=intnx('month',today(),0)-intnx('month',today(),-1);
run;

proc sql noprint;
select 	prev_month
into : prev_month
from num_days
;quit;

Why do you create a dataset, only to put a value into a macro variable with SQL, when you can do the same in one step with CALL SYMPUTX?

All the macro coding is not necessary, you can do the same conditions with IF in a single DATA step.

[hnb_matt_d]

Well...  It's obviously been a while.  Some changes at work have caused quite the delay in getting back to this...  

To respond to @Kurt_Bremser, I am using such an antiquated iteration of SAS that IF statements often throw errors.  

I get the INTNX extraneousness, so thank you for pointing that out.  I'm going to call this one closed as this report has run for multiple months without error or intervention being required.  I'm going to guess one of the "updates" that work implemented to keep SAS from falling off the cliff of no longer being supported removed an issue, however inadvertent the thought of fixing anything from work may have been.  

[Tom]

So you have this error in your SAS log:

WARNING: Apparent symbolic reference START_DATE not resolved.
ERROR: Invalid date/time/datetime constant "&start_date."d.

And the reason is obvious.  You set a value to START_DATE inside of a macro and are trying to use the value after the macro has ended.  The easiest change is to set a value to the macro variable BEFORE running the macro. 

%let start_date=Before macro runs ;
%day_of_month_dependency;

That will make sure the macro variable exists so that the macro code will update the existing macro variable instead of make a NEW LOCAL macro variable that will just disappear when the macro ends.

[hnb_matt_d]

Forest for the trees, @Tom.  

I've been in and out of this code so many times, I couldn't see an issue with that.  Seems glaringly apparent now.  

It has been remediated.  Thank you for looking through the code and finding this.