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]

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.