DATA Step, Macro, Functions and more

IF-ELSE Condition to Perform a Loop

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

IF-ELSE Condition to Perform a Loop

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


Accepted Solutions
Solution
‎06-19-2014 04:32 AM
Super User
Super User
Posts: 7,054

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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


All Replies
Super User
Posts: 7,808

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to KurtBremser

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?

Super User
Posts: 7,808

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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   
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,054

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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

Contributor
Posts: 22

Re: IF-ELSE Condition to Perform a Loop

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

Super User
Super User
Posts: 7,970

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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;

Solution
‎06-19-2014 04:32 AM
Super User
Super User
Posts: 7,054

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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

Super User
Posts: 7,808

Re: IF-ELSE Condition to Perform a Loop

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,054

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to KurtBremser

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;

Super User
Posts: 7,808

Re: IF-ELSE Condition to Perform a Loop

Well then it is probably best practice to force the correct format by adding format=5. before the into clause.

Computers are dumb.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

Re: IF-ELSE Condition to Perform a Loop

It is now working! Thank you for the support.

Super Contributor
Posts: 387

Re: IF-ELSE Condition to Perform a Loop

Posted in reply to 01SASUser

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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