topic Re: IF-ELSE Condition to Perform a Loop in SAS Programming

IF-ELSE Condition to Perform a Loop

01SASUser — Wed, 18 Jun 2014 04:15:38 GMT

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;

date=date+1 to today()-2;

output;

end;

run;

proc append

base=FINAL_TABLE

data=TEST2 force;

run;

/*Else, do not do anything*/

Re: IF-ELSE Condition to Perform a Loop

Kurt_Bremser — Wed, 18 Jun 2014 06:00:28 GMT

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;

Re: IF-ELSE Condition to Perform a Loop

01SASUser — Wed, 18 Jun 2014 09:31:47 GMT

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?

Re: IF-ELSE Condition to Perform a Loop

Kurt_Bremser — Wed, 18 Jun 2014 10:29:32 GMT

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"

Re: IF-ELSE Condition to Perform a Loop

Tom — Wed, 18 Jun 2014 13:08:41 GMT

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

Re: IF-ELSE Condition to Perform a Loop

01SASUser — Thu, 19 Jun 2014 03:45:04 GMT

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:

Re: IF-ELSE Condition to Perform a Loop

RW9 — Thu, 19 Jun 2014 08:17:07 GMT

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;

Re: IF-ELSE Condition to Perform a Loop

Tom — Thu, 19 Jun 2014 08:32:10 GMT

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

Re: IF-ELSE Condition to Perform a Loop

ScottBass — Fri, 20 Jun 2014 05:49:19 GMT

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;

Re: IF-ELSE Condition to Perform a Loop

Kurt_Bremser — Fri, 20 Jun 2014 06:03:57 GMT

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

Re: IF-ELSE Condition to Perform a Loop

Tom — Fri, 20 Jun 2014 11:26:04 GMT

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;

Re: IF-ELSE Condition to Perform a Loop

Kurt_Bremser — Fri, 20 Jun 2014 11:47:08 GMT

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

Computers are dumb.

Re: IF-ELSE Condition to Perform a Loop

01SASUser — Mon, 23 Jun 2014 07:44:36 GMT

It is now working! Thank you for the support.