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(¤t_day.));
%if &weekday. = 2 %then %do;
/* If it's Monday, fetch wednesday & Friday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -5), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -3), date9.);
%end;
%else %if &weekday. = 3 %then %do;
/* If it's Tuesday, fetch Thursday & Monday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -5), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%else %if &weekday. = 4 %then %do;
/* If it's Wednesday, fetch Friday & Monday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -5), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%else %if &weekday. = 5 %then %do;
/* If it's Thursday, fetch Monday & Wednesday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -3), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%else %if &weekday. = 5 %then %do;
/* If it's Friday, fetch Tuesday & Thursday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -3), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_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
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.
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.
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?
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 |
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?
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(¤t_day.));
%if &weekday. = 2 %then %do;
/* If it's Monday, fetch wednesday & Friday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -5), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -3), date9.);
%end;
%else %if &weekday. = 3 %then %do;
/* If it's Tuesday, fetch Thursday & Monday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -5), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%else %if &weekday. = 4 %then %do;
/* If it's Wednesday, fetch Friday & Monday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -5), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%else %if &weekday. = 5 %then %do;
/* If it's Thursday, fetch Monday & Wednesday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -3), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%else %if &weekday. = 5 %then %do;
/* If it's Friday, fetch Tuesday & Thursday */
%let start_date = %sysfunc(intnx(day, ¤t_day., -3), date9.);
%let end_date = %sysfunc(intnx(day, ¤t_day., -1), date9.);
%end;
%mend weekdays;
/*calculating last 3 weekdays based on logic*/
%weekdays;
%put &start_date.;
%put &end_date.;
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.
Thank you, this worked. I used the data step rather than explicit macro.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: