May I know how to convert my comments (inside /* */) into a code?
/*if max(date) of FINAL_TABLE ne today()-2, do the code below*/
proc sql;
create table test as
select * from FINAL_TABLE
having date=max(date);
quit;
data TEST2;
set TEST;
do
date=date+1 to today()-2;
output;
end;
run;
proc append
base=FINAL_TABLE
data=TEST2 force;
run;
/*Else, do not do anything*/
Switch from using %EVAL() to using %SYSEVALF().
1911 %let fdate=17JUN2014;
1912 %put diff1 =%eval(%sysfunc(date()) - "&fdate"d) ;
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 19893 - "17JUN2014"d
diff1 =
1913 %put diff2 =%sysevalf(%sysfunc(date()) - "&fdate"d) ;
diff2 =2
proc sql;
select max(date) into: fdate from final_table;
run;quit;
%macro do_it;
%if %eval(%sysfunc(date())-&fdate) ne 2 %then %do;
/* your code here */
%end;
%mend;
%do_it;
Hello. Thank you for your response.
I receive an error upon executing the suggested code:
SYMBOLGEN: Macro variable FDATE resolves to 17JUN2014
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: 19892-17JUN2014
ERROR: The macro DO_IT will stop executing.
May I know how to fix this?
I assumed that "date" is a proper SAS date variable (numeric with a date format assigned).
This works here:
data final_table;
format date date9.;
date = date()-4;
output;
date = date()-10;
output;
date = date()-3;
output;
run;
proc sql;
select max(date) into: fdate from final_table;
run;quit;
%macro do_it;
%if %eval(%sysfunc(date())-&fdate) ne 2 %then %do;
%put "code running";/* your code here */
%end;
%mend;
%do_it;
resulting in this log:
18 | data final_table; |
19 | format date date9.; |
20 | date = date()-4; |
21 | output; |
22 | date = date()-10; |
23 | output; |
24 | date = date()-3; |
25 | output; |
26 | run; |
NOTE: The data set WORK.FINAL_TABLE has 3 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time | 0.04 seconds | |
cpu time | 0.01 seconds | |
27 | |
28 | proc sql; |
29 | select max(date) into: fdate from final_table; |
30 | run;quit; |
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time | 0.04 seconds | |
cpu time | 0.00 seconds | |
31 | |
32 | |
33 | %macro do_it; |
34 | %if %eval(%sysfunc(date())-&fdate) ne 2 %then %do; |
35 | %put "code running";/* your code here */ |
36 | %end; |
37 | %mend; |
38 | %do_it; |
"code running"
39 |
If your macro variable has a value like 17JUN2014 then you can use it in a date literal.
%if %eval(%sysfunc(date())-"&fdate"d) ne 2 %then %do;
The problem I see is how are going to find the max(date) if it is stored as a character string in ddMONyyyy format inside your data?
SAS will use lexical ordering for character strings so '20JAN1990' > '10JUN2014' since '2' > '1'.
Hello. Thank you for your response. I'm still receiving an error after performing the suggested code:
602 proc sql;
603 select max(date) into: fdate from RCBC_TB.CIV_APPENDED2;
604 run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.20 seconds
cpu time 0.09 seconds
605
606 %macro do_it;
607 %if %eval(%sysfunc(date())-"&fdate"d) ne 2 %then %do;
608 proc sql;
609 create table test as
610 select * from RCBC_TB.CIV_APPENDED2
611 having date=max(date);
612 quit;
613
614 data TEST2;
615 set TEST;
616 do
617 date=date+1 to today()-2;
618 output;
619 end;
620 run;
621
622 proc append
623 base=RCBC_TB.CIV_APPENDED2
624 data=TEST2 force;
625 run;
626 %end;
627 %mend;
628 %do_it;
SYMBOLGEN: Macro variable FDATE resolves to 17JUN2014
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: 19893-"17JUN2014"d
ERROR: The macro DO_IT will stop executing.
" The condition was: 19893-"17JUN2014"d " --> is there a way to revise the program that will make "17JUN2014"d into numeric format (eg. for 17JUN2014 it should be 19891)
The parameters of DATE column are indicated below:
Hi,
You could move the logic to the SQL statement:
data have;
attrib date format=date9.;
date='01Jan2014'd; output;
date='01May2014'd; output;
run;
proc sql;
select date() - MAX(DATE)
into :FDATE
from HAVE;
quit;
%macro do_it();
%if &FDATE. ne 2 %then %do;
data x;
run;
%end;
%mend do_it;
%do_it;
Switch from using %EVAL() to using %SYSEVALF().
1911 %let fdate=17JUN2014;
1912 %put diff1 =%eval(%sysfunc(date()) - "&fdate"d) ;
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 19893 - "17JUN2014"d
diff1 =
1913 %put diff2 =%sysevalf(%sysfunc(date()) - "&fdate"d) ;
diff2 =2
Hi Tom! I am now completely baffled by the fact that in my example I created date as numeric with date9. and got the unformatted number into my fdate macro variable, while 01SASUser received the formatted string. I can see no logical difference between his situation and mine. Do you have any idea what causes that different behaviour?
I did it in 9.2 on UNIX(AIX).
Most likely you applied a aggregate function, and so created a new "variable" that did not have the format attached. Here is an example.
data test ;
date1=today();
date2=today();
format date1 date9.;
run;
proc sql noprint ;
select date1,date2,max(date1)
into :date1,:date2,:date3
from test
;
quit;
Well then it is probably best practice to force the correct format by adding format=5. before the into clause.
Computers are dumb.
It is now working! Thank you for the support.
Dates are numeric. Macro is character strings. Now, you can coax macro to do what you want with %sysfunc, %eval, %sysevalf, etc, but why make your life hard?
Move the logic decision "should I execute this code" completely into your data step (or SQL). Your macro then becomes a simple check of a boolean (0 or 1) value.
See below code. Change the "-0" to "-1", "-2", "-3", etc and re-run. Your macro should kick in on "-3".
data test;
do date="01JAN2014"d to today()-0;
random=ranuni(0);
output;
end;
format date date9.;
run;
proc sort;
by random; * mix it up a bit ;
run;
%let flag=0;
data _null_;
retain max today;
set test end=eof;
if _n_=1 then today=today();
max=max(max,date);
if eof then call symputx("flag",today - max > 2);
run;
%put &=flag;
%macro doit;
%if (&flag) %then %put YES;
%mend;
%doit;
* alternative approach if you must use SQL for everything 🙂 ;
%let flag=0;
%let today=%sysfunc(today()); * for better performance, since today() never changes ;
proc sql noprint;
select (&today - max(date) > 2) into :flag trimmed from test;
quit;
%put &=flag;
%doit;
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.