Apparent symbolic reference START_DATE not resolved.

[User_2024]

I have an intial_data1 dataset that has my base data; I am trying to get last 3 weekday of data for every weekday between monday to friday for the whole month (the code runs on weekdays).
I have determined the logic and wrote a macro for calculating the previous 3 weekdays. I want to display the start and end date for my logic; I have called out the columns I want including the macro variable but I have the 'apparent symbolic reference' not resolved error.

%macro weekdays(start_date, end_date);
%let current_day = %sysfunc(today());
%let weekday = %sysfunc(weekday(&current_day.));

%if &weekday. = 2 %then %do;
    /* If it's Monday, fetch wednesday & Friday */
    %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -3), date9.);
%end;
%else %if &weekday. = 3 %then %do;
 /* If it's Tuesday, fetch Thursday & Monday */
    %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;	
%else %if &weekday. = 4 %then %do;
 /* If it's Wednesday, fetch Friday & Monday */
    %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;	
%else %if &weekday. = 5 %then %do;
 /* If it's Thursday, fetch Monday & Wednesday */
    %let start_date = %sysfunc(intnx(day, &current_day., -3), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;
%else %if &weekday. = 5 %then %do;
 /* If it's Friday, fetch Tuesday & Thursday */
    %let start_date = %sysfunc(intnx(day, &current_day., -3), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;
%mend weekdays;


%put &start_date.;
%put &end_date.;



/*calculating last 3 weekdays based on logic*/
%weekdays(&start_date., &end_date.);

rsubmit;
proc delete data =myora.final_data;run;
proc sql;
connect to oracle(&ora_str.);
create table myora.final_data as
select * from connection to oracle(

select acct_num, mh_name, mh_cd, auth_dt, 
	  count(*) as total_auth_cnts, sum(auth_amt) as sum_auth_amt,
	  "&start_date."d as m_start_date,
      "&end_date."d as m_end_date
from initial_data1
where auth_dt >= TRUNC(SYSDATE) - 10  and auth_dt <= TRUNC(SYSDATE)
group by acct_num, mh_name, mh_cd, auth_dt
having 
	count(*) >= 10
	and sum(auth_amt) >= 100

);
disconnect from oracle;
quit;
endrsubmit;

%put m_start_date: &start_date.;
%put m_end_date: &end_date.;

Error: 321  proc sql;
322  connect to oracle(&ora_str.);
323  create table myora.final_data as
324  select * from connection to oracle(
325
326  select acct_num, mh_name, mh_cd, auth_dt,
327        count(*) as total_auth_cnts, sum(auth_amt) as sum_auth_amt,
331        "&start_date."d as m_start_date,
WARNING: Apparent symbolic reference START_DATE not resolved.
332        "&end_date."d as m_end_date
WARNING: Apparent symbolic reference END_DATE not resolved.
333  from initial_data1
334  where auth_dt >= TRUNC(SYSDATE) - 10  and auth_dt <= TRUNC(SYSDATE)
335  group by acct_num, mh_name, mh_cd, auth_dt
336  having
337      count(*) >= 10
338      and sum(auth_amt) >= 100
339
340  );
ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement:
       select acct_num, mh_name, mh_cd, auth_dt, count(*) as total_auth_cnts,
       sum(auth_amt) as sum_auth_amt, , "&start_date."d as m_start_date, "&end_date."d as
       m_end_date from initial_data1 where auth_dt >= TRUNC(SYSDATE) - 10 and auth_dt <=
       TRUNC(SYSDATE) group by acct_num, mh_name, mh_cd, auth_dt having
       count(*) >= 10 and sum(auth_amt) >= 100.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
341  disconnect from oracle;
NOTE: Statement not executed due to NOEXEC option.
342  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.34 seconds
      cpu time            0.14 seconds

NOTE: Remote submit to ACXIOM complete.
556
557  %put m_start_date: &start_date.;
m_start_date: 25JUL2024
558  %put m_end_date: &end_date.;
m_end_date: 27JUL2024

[SASKiwi]

You've defined your macro variables in your local SAS session but are trying to use them in your remote SAS session. You can use the %SYSLPUT statement to copy macro variables to your remote SAS session:

%global start_date end_date;
%weekdays(&start_date., &end_date.);
%syslput _all_ ;

The above copies all locally defined macro variables to the remote SAS session you last signed onto. You may also need a %GLOBAL statement to pick up macro variables defined inside a macro.

[Tom]

But you never set any values into those macro variables.

First you defined a macro that has START_DATE and END_DATE as parameters. So those are by definition LOCAL to the macro.  So they only exist while the macro is running.

Then later you showed a call to the macro trying to pass in values from other macro variables that are also named START_DATE and END_DATE.  But you never showed any code that set those other macro variables.  It might be less confusing to use a different name for the macro variables you are using outside of the macro scope.

And finally you have a third reference to macro variables START_DATE and END_DATE running on some remote SAS session.  You also do not show any place where the macro variables are given values in that SAS session.

What does the remote SAS session have to do with the first part of your program?

[User_2024]

1. I am trying to run a simulation for everyday from Monday to Friday and I also need to run another simulation for June month. 
2. in the proc sql table I want to select the acct_num, date, start date should be today-3 and end date should be start_date +2. I was trying to use the macro variables here. 
3. the logic included weekends , which I am trying to avoid by introducing macro variables.

auth_dt / day	start date= auth_dt-3weekdays	end date= start_date +2
Monday	Wednesday	Friday
Tuesday	Thursday	Monday
Wednesday	Friday	Tuesday
Thursday	Monday	Wednesday
Friday	Tuesday	Thursday

[Tom]

I don't follow exactly what you are trying to do, but I think you are not breaking the task into the right sub parts to make it easier.

Sounds like you want to do some calculation based on date multiple times (your simulation?).  So construct code that does that which takes the part the varies (I assume one or perhaps two date values) as input.

%macro run_one(date);
%syslput date=&date ;
rsubmit;
... remote code that uses &date ...
endrsubmit;
%mend run_one;

Then construct code that calls that once for each date you want to simulate.  Not clear to me what that is but perhaps you just want to loop over the days in June?

data _null_;
  do date=mdy(6,1,2024) to mdy(6,30,2024);
     call execute(cats('%nrstr(%run_one)(',date,')'));
  end;
run;

If you don't want to run it for every day then change the loop.  Perhaps using INTNX() function and the WEEKDAY interval?

[User_2024]

Remote session - the remote session is to submit my code in a controlled environment with access to Oracle lib

[SASKiwi]

I've corrected your macro so it prints the start and end date values:

%macro weekdays;
%global start_date end_date;
%let current_day = %sysfunc(today());
%let weekday = %sysfunc(weekday(&current_day.));

%if &weekday. = 2 %then %do;
    /* If it's Monday, fetch wednesday & Friday */
    %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -3), date9.);
%end;
%else %if &weekday. = 3 %then %do;
 /* If it's Tuesday, fetch Thursday & Monday */
    %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;	
%else %if &weekday. = 4 %then %do;
 /* If it's Wednesday, fetch Friday & Monday */
    %let start_date = %sysfunc(intnx(day, &current_day., -5), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;	
%else %if &weekday. = 5 %then %do;
 /* If it's Thursday, fetch Monday & Wednesday */
    %let start_date = %sysfunc(intnx(day, &current_day., -3), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;
%else %if &weekday. = 5 %then %do;
 /* If it's Friday, fetch Tuesday & Thursday */
    %let start_date = %sysfunc(intnx(day, &current_day., -3), date9.);
	%let end_date   = %sysfunc(intnx(day, &current_day., -1), date9.);
%end;
%mend weekdays;

/*calculating last 3 weekdays based on logic*/
%weekdays;

%put &start_date.;
%put &end_date.;

[Kurt_Bremser · Accepted solution]

Take a BIG step back and review your code, and do it in a DATA step first:

data _null_;
wd = weekday(today());
if wd = 2
then end_date = today() - 3;
else end_date = today() - 1;
if wd in (2,3,4)
then start_date = today() - 5;
else start_date = today() - 3;
call symputx("start_date",put(start_date,date9.),"g");
call symputx("end_date",put(end_date,date9.),"g");
run;

Since you do not intend to call your code in the middle of a PROC or DATA step, there's no need to convert it to pure macro language, so you can avoid the %SYSFUNC avalanche.

PS your macro as posted has a mistake, the "friday" calculation overrules the "thursday" calculation, as it is also triggered by weekday 5.

[User_2024]

Thank you, this worked. I used the data step rather than explicit macro.