if else do marco

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

if else do marco

I have sas program which stores data date wise.

If date value in today’s dataset  is greater than date value in yesterday’s dataset , I need to perform further transformations on the dataset and generate a report,else send an email advising that no data is present.

I have started the code as below:

%macro Bilk;

PROC SQL NOPRINT;

SELECT DISTINCT MAX(bill_st_Ef_Dt) INTO : &MAX_DATE

FROM _jas.LATEST;   /* this is yesterdays dataset */

QUIT;

Here the new dataset latest is created which has values as of today.

PROC SQL NOPRINT;

SELECT DISTINCT MAX(bill_st_Ef_Dt) INTO : &MAX_DATE2

FROM LATEST;

QUIT;

%if %sysfunc(&MAX_DATE2 ge &MAX_DATE ) %then

                    %do;

Here I have several transformations data and proc sql statements.

%end;

%else  %do;

                                          %PUT "EMAIL";

FILENAME EML EMAIL  TO = ("shy_@yahoo.com " )

   SUBJECT =  "SUBJECT";

DATA _NULL_;

FILE EML;

PUT "No data for today";

RUN;

  %END;

%mend bilk;

%bilk;

I am having trouble running this program, any ideas will be great.


Accepted Solutions
Solution
‎08-10-2014 09:51 PM
Super User
Super User
Posts: 6,338

Re: if else do marco

%SYSFUNC() is a wrapper to put around a function call, but you did not include any function calls.

Try %EVAL() or %SYSEVALF() instead.

View solution in original post


All Replies
New User
Posts: 1

Re: if else do marco

Hello the sql code to store date into macro var doesn't need to include '&' for macro var definition. So I suggest to test it with this kind of code.

PROC SQL NOPRINT;

SELECT DISTINCT MAX(bill_st_Ef_Dt) INTO :MAX_DATE

FROM _jas.LATEST;   /* this is yesterdays dataset */

QUIT;

You can also add %put statement to see which value are stored in the macro var and the different macro options can give you info on the macro processing.

But as we don't have any log or more info on trouble it is difficult to hep you more.

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: if else do marco

You could also write this as:

data prev;

  attrib biffl_st_ef_dt format=date9.;

  biffl_st_ef_dt='01jan2014'd; output;

  biffl_st_ef_dt='07jan2014'd; output; 

  biffl_st_ef_dt='09jan2014'd; output;

run;

data latest;

  attrib biffl_st_ef_dt format=date9.;

  biffl_st_ef_dt='01jan2014'd; output;

  biffl_st_ef_dt='07jan2014'd; output; 

  biffl_st_ef_dt='11jan2014'd; output;

run;

proc sql noprint;

  select  distinct case when B.DT >= A.DT then "Y" else "N" end

  into    :CONT

  from    (select max(BIFFL_ST_EF_DT) as DT from WORK.PREV) A,

          (select max(BIFFL_ST_EF_DT) as DT from WORK.LATEST) B;

quit;

data _null_;

  if "&CONT."="N" then call execute('%email_macro()');  /* Or just put the email code here */

  else call execute('%transfomations()');  /* Or just put your transformation code here */

run;

Contributor
Posts: 32

Re: if else do marco

In addition to correcting the into :&macro_var, you should correct the %sysfunc(), which requires a SAS function as its argument, e.g. %sysfunc(datdif(a,b,c)).

Contributor
Posts: 44

Re: if else do marco

ERROR: Argument 1 to function DATDIF referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Argument 2 to function DATDIF referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC

       or %QSYSFUNC function reference is terminated.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

       %sysfunc(datdif(MAX_DATE2,MAX_DATE,act/act ))

ERROR: The macro BILLSHOCK will stop executing.

This is the error message i have recevied.

Super User
Super User
Posts: 6,338

Re: if else do marco

That's an easy one to diagnose.

You asked the DATDIF function to find the difference between two strings of characters.

%sysfunc(datdif(MAX_DATE2,MAX_DATE,act/act ))

Perhaps have numbers stored in some macro variables with those names? If so then you need to use & to tell SAS to expand the macro variables.

%sysfunc(datdif(&MAX_DATE2,&MAX_DATE,act/act ))

Contributor
Posts: 44

Re: if else do marco

Thanks Tom ..

I have a new error message now:

ERROR: Expected open parenthesis after macro function name not found.

ERROR: Required operator not found in expression: %sysfunc(MAX_DATE2 ge MAX_DATE )

What i am trying here is :

%if %sysfunc(MAX_DATE2 ge MAX_DATE ) %then

                    %do;

Few joins and transformations

Solution
‎08-10-2014 09:51 PM
Super User
Super User
Posts: 6,338

Re: if else do marco

%SYSFUNC() is a wrapper to put around a function call, but you did not include any function calls.

Try %EVAL() or %SYSEVALF() instead.

Contributor
Posts: 44

Re: if else do marco

thanks  Tom ....

I am getting the below error , when converting the SAS date to Teradata date:

PROC SQL NOPRINT;

SELECT DISTINCT MAX(bit_ff_Dt) INTO : MAX_DATE

FROM _jes.LATEST;

QUIT;

  data _null_;
call symput( 'MAX_DATE', put(MAX_DATE, date9.) );
run;

NOTE: Variable MAX_DATE is uninitialized.

please advise any ideas...

Super User
Super User
Posts: 6,338

Re: if else do marco

The error message is saying that the data set variable MAX_DATE that you referenced in the PUT() function call has not been given a value.

I assume that you want to reformat the value of the macro variable MAX_DATE that you just created in the previous PROC SQL step.

You reference a macro variable by prefixing the name with an ampersand.

data _null_;

  call symputx('MAX_DATE',put(&max_date,date9.));

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 692 views
  • 0 likes
  • 5 in conversation