BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
01SASUser
Fluorite | Level 6

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*/

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

12 REPLIES 12
Kurt_Bremser
Super User

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;

01SASUser
Fluorite | Level 6

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?

Kurt_Bremser
Super User

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   
Tom
Super User Tom
Super User

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

01SASUser
Fluorite | Level 6

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:

Date.JPG

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Tom
Super User Tom
Super User

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

Kurt_Bremser
Super User

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

Tom
Super User Tom
Super User

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;

01SASUser
Fluorite | Level 6

It is now working! Thank you for the support.

ScottBass
Rhodochrosite | Level 12

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;


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2067 views
  • 10 likes
  • 5 in conversation