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

I have a dataset that has counts of activity types grouped by month and year (for a 13 month period).  This dataset will be compiled monthly (at the beginning of each month for the preceding 13 months), so the immediately preceding month (December, in January's instance) will have two (2) appearances.  One for the month immediately preceding (December 2022) and one for 13 months ago (December 2021).    I've also been tasked with re-creating the last seven (7) months' data (June 2022 through December 2022).  Everything is working outside the proc transpose not ordering dates (months/years - or however this needs to be formatted) chronologically from left to right.  More specifically, March 2022 continually appears as the last month listed (to the right) with every other month/year appearing in order as wanted.     data volumes; set volumes_1; format month_2 monyy.; month_2 = input(month !! "01",yymmdd8.); run; ^This code creates a new variable, month_2, in a date format.  Full disclosure, I've run the proc transpose (included below) using a date formatted month_2 and a character formatted month variable with the same results (March 2022 not cooperating and appearing in order).     proc sort data=volumes (drop=month); by transaction_type_code month_2; run; proc transpose data=volumes out=PIVOT (drop=_:); by transaction_type_code; id month_2; var volume; run; ^There's the sort and subsequent proc transpose, to result in:   I've utilized multiple date formats for month_2, with none of them working properly.  HELP!       ... View more