Sas macro if then else based on input parameter

Question

I am writing a SAS macro that takes two params, the first is the name of a dataset, the second param is a string that will actually determine one of the output columns:

%macro test(data, input_mth);
%if &comp_mth.=October %then %do;
cmp_basis=Last Fiscal End;
%end;
proc sql;
create table final as select &cmp_basis. as col1, data.col2 from data;
quit;
%mend;

%test(data, October);

basically, I pass in a dataset and if I pass the string 'October', then the output will show 'Last Fiscal End; as the first column. If I pass in January, it will show 'Calendar beginning' etc etc.

The %if block gives me error:

Statement is not valid or it is used out of proper order.

Answer 1

Just reading through your code in order let's identify some of the issues.

First your %IF statement is referencing a macro variable COMP_MTH that is not defined anywhere in your program. I assume you meant to refer to one of your input parameters instead.

%if &input_mth.=October %then %do;

Second you have data step statements inside your %DO/%END block but you never started a data step. I assume that you mean to create a macro variable there. So use a %let statement.

%let cmp_basis=Last Fiscal End; 

But you also need to define that macro variable as local or else your macro will overwrite any macro variable with the same name in the calling program's environment.

You also need to make sure that your macro is generating valid SAS code. So in your SQL code you have

select &cmp_basis. as col1

But if we just replace the macro variable with the value you are assigning above we this gibberish statement:

select Last Fiscal End as col1

I assume you meant to create a character variable there, so add quotes so that you are generating a character constant.

select "&cmp_basis." as col1

You also have a logic problem. What value do you want for COL1 when it is not October? One way to deal with that is to set a default value to the macro variable before your logic. But perhaps you meant to use the input month? So perhaps you just need to add a %else clause?

You are also never using your other input parameter. Let's assume that you mean to pass in the name of the dataset that the SQL should query. So you want to use

from &data

But then your SQL table alias in DATA.col2 is never defined. So make sure to either assign an alias to your input table, or for this simple one table query just drop the alias when referencing the column name.

So we end up with something like this:

%macro test(data, input_mth);
%local cmp_basis;
%if &input_mth.=October %then %do;
  %let cmp_basis=Last Fiscal End;
%end;
%else %let cmp_basis=&input_mth;
proc sql;
create table final as
  select "&cmp_basis." as col1
       , x.col2
  from &data x
;
quit;
%mend test;

Of for such simple logic we could dispense with the extra macro variable and just use the macro logic to conditionally generate the constant value that you want to use as the value of COL1.

%macro test(data, input_mth);
proc sql;
create table final as
  select 
%if &input_mth.=October %then "Last Fiscal End";
%else "&input_mth";
    as col1
  , x.col2
  from &data x
;
quit;
%mend test;

Answer 2

Your code have numerous errors of statement concept. If you more give me more details about you need, I help you better. But I try understanded you problem, and I sugested two soluctions.

option I

%macro test(data, input_mth);

    %if &comp_mth. = "October" %then %do;
        cmp_basis = Last /*Fiscal End*/;
    %end;

    proc sql;
        create table final as select 
            "&cmp_basis." as col1, 
             col2
        from &data.;
    quit;

%mend;

%test(data, October);

option II

%macro test(data, input_mth);

    %if &comp_mth.=prv %then %do;
        %let cmp_basis = Last Fiscal End; /* this is a  Vector that contains string at Last, Fiscal and End*/
    %end;

    proc sql;
        create table final as select /* You create a table call final       */
        "&cmp_basis." as col1,       /* column call October                 */
        /* data.col2 */              /* this no have sense - what is this ? */
          col2                      /*Correct way to call col2 if it exists on data*/
        from &data.;                 /* Your data set, you call in macro    */
    quit;
%mend;

%test(data, October);

Answer 3

I figure it out, correct syntax is:

%macro test(data, input_mth);
%if &comp_mth.=prv %then %do;
%let cmp_basis=Last Fiscal End;
%end;
proc sql;
create table final as select "&cmp_basis." as col1, data.col2 from data;
quit;
%mend;

%test(data, October);