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