topic Passing variable to macro needed for if else in SAS Programming

Passing variable to macro needed for if else

LisaSAS — Fri, 07 Oct 2016 13:39:20 GMT

I have code that I am needing to run only if there has been a date change. I'm comparing the last entry date of two different tables and storing it to two variables. I then need to create a file only if the dates are different. This is what I have so far, but it is not working - it's actually not doing anything. I'm not sure if I'm passing the variables correctly. Any help would be greatly appreciated.

/*Get Last Date in file*/

PROC SQL;

select DISTINCT fldDate

INTO:VariableToStoreLastDateTbleA

from TbleA where fldDate = (select max(fldDate) from TbleA)

GROUP BY fldDate

ORDER BY fldDate

;QUIT;

PROC SQL;

select DISTINCT fldDate

INTO:VariableToStoreLastDateTbleB

from TbleB where fldDate = (select max(fldDate) from TbleB)

GROUP BY fldDate

ORDER BY fldDate

;QUIT;

/*If LastDate in WorkingTable > LastDate in Other

%Macro Validate;

%IF & VariableToStoreLastDateTbleA> VariableToStoreLastDateTbleB %then

%do;

/*Generate excel file */

ods msoffice2k

file="NameOfFile.xls"

style=sasweb;

proc report data= TbleA nowd;

TITLE ;

….rest of code to generate file

;run;

Ods all_ close;

%end;

%MEnd Validate;

Re: Passing variable to macro needed for if else

Kurt_Bremser — Fri, 07 Oct 2016 14:03:00 GMT

Your initial SQLs look much to complicated to me. Why not do

proc sql;
select max(fldDate)
into:VariableToStoreLastDateTbleA
from TbleA;
quit;

And your macro code won't work at all:

%IF & VariableToStoreLastDateTbleA> VariableToStoreLastDateTbleB %then

Note the blank after the ampersand, and the ampersand for the second macro variable is missing.

Re: Passing variable to macro needed for if else

Patrick — Fri, 07 Oct 2016 15:22:16 GMT

Untested but I believe something like below could do the job.

%macro Validate;

  %local exec_flg;
  PROC SQL noprint;
    select 
      case
        when A.max_fldDate>B.max_fldDate then '1'
        else '0'
        end 
      into :exec_flg
    from 
      ( 
        select max(fldDate) as max_fldDate          
        from TbleA
      ) as A,
      (
        select max(fldDate) as max_fldDate          
        from TbleB
      ) as B

    ;
  QUIT;
 
 
  %IF &exec_flg=1 %then
    %do;
      /*Generate excel file */
      ods msoffice2k
        file="NameOfFile.xls"
        style=sasweb;

      proc report data= TbleA nowd;
        TITLE;
        ….rest of code to generate file
        ;
      run;

      Ods all_ close;
    %end;

%mend Validate;

Re: Passing variable to macro needed for if else

Astounding — Fri, 07 Oct 2016 16:18:00 GMT

Certainly, let's rule out the obvious. You are defining a macro, but never executing the macro. You would need to add a %validate statement at the end.

If it still produces nothing, add to your macro definition:

%else %do;

%put your date variables so you can see what was calculated;

%end;

You can create your own diagnostics here.

Re: Passing variable to macro needed for if else

LisaSAS — Fri, 07 Oct 2016 18:28:56 GMT

I'm still not getting any of my values to come through? I comfirmed my variables were populated outside of the macro,

SYMBOLGEN: Macro variable VariableToStoreLastDateTbleA resolves to 20160930

SYMBOLGEN: Macro variable VariableToStoreLastDateTbleB resolves to 20160831

So, how to I take the information that is stored in these variables and use them in the macro?

/*If LastDate in WorkingTable > LastDate in Other*/

%Macro Validate ( VariableToStoreLastDateTbleA , VariableToStoreLastDateTbleB );

%IF &VariableToStoreLastDateTbleB > &VariableToStoreLastDateTbleA %then

%do;

%put 'Last Date in Macola table is ' &VariableToStoreLastDateTbleB;

%put 'Last Date in db2 table is '&VariableToStoreLastDateTbleA;

/*Generate excel file */

ods msoffice2k

file="filepath.xls"

style=sasweb;

proc report data=Macola_Work nowd;

TITLE ;

run;

ods _all_ close;

%end;

%else;

%do;

%put &VariableToStoreLastDateTbleB;

%put &VariableToStoreLastDateTbleA;

%end;

%mend;

%Validate

MLOGIC(VALIDATE): Parameter VariableToStoreLastDateTbleA has value

MLOGIC(VALIDATE): Parameter VariableToStoreLastDateTbleB has value

SYMBOLGEN: Macro variable VariableToStoreLastDateTbleA resolves to

SYMBOLGEN: Macro variable VariableToStoreLastDateTbleB resolves to

Re: Passing variable to macro needed for if else

Astounding — Fri, 07 Oct 2016 19:07:44 GMT

It's a little difficult to interpret what the final structure is to your macro(s), but here is a likely scenario.

When SQL creates new macro variables, it creates them in the local symbol table for the currently executing macro. If you have separated this program so that the SQL steps run in a separate macro from %VALIDATE, those macro variables will be gone before %VALIDATE executes. You will need to define them as global before SQL runs.

On a separate a smaller note, %ELSE DO; should be one statement, not two. It happens to work the way you coded it, however.

Re: Passing variable to macro needed for if else

Kurt_Bremser — Sat, 08 Oct 2016 08:18:25 GMT

Be careful with your macro parameter names. If you use a name for a macro parameter that is also used as the name of a global macro variable, you implicitly create a local (to the macro) macro variable that takes precedence over the global one. When you then call the macro without explicitly supplying parameters, those local macrovars stay empty.

For the sake of less typing, I would use shorter names for macro parameters, and maybe prefix them with the macro name.

%macro validate(validate_date_a, validate_date_b);

As it is, your macro with the given values would do nothing, as VariableToStoreLastDateTbleB (20160831) is smaller than VariableToStoreLastDateTbleA (20160930)