Sas macro if then else based on input parameter

sas macro if multiple conditions
sas if-then do else example
if-then statements in sas to create new variables
sas conditional macro variable assignment
sas macro if-%then %let
sas if-then/else output
sas case if then else
sas if without then

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.

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;

[PDF] Beyond IF THEN ELSE: Techniques for Conditional , You can now use %IF-%THEN-%ELSE constructs in open code. capture conditional logic in macro */ %macro SummarizeIfExists(); %if Check a system environment variable before running code Are we so excited about this simply because the SAS base language has work",name); set.input=catx(". So if we continue with this OLD/NEW analogy you could make a macro that takes a parameter with two choices OLD or NEW and based on the users choice when calling the macro it will generate different SAS code. For exmaple you have it generate different WHERE clauses on an SQL query. A trivial example:


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

Using %IF-%THEN-%ELSE in SAS programs, If the expression resolves to zero, then the expression is false and the %ELSE In general, %IF-%THEN/%ELSE statement, which is part of the SAS macro an IF-THEN/ELSE statement that uses the value of the DATA step variable SALE to  In general, %IF-%THEN/%ELSE statement, which is part of the SAS macro language, conditionally generates text. However, the IF-THEN/ELSE statement, which is part of the SAS language, conditionally executes SAS statements during DATA step execution.


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

Macro Language Dictionary : %IF-%THEN/%ELSE, 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  While you can't query at run time to get the parameters in the current macro, you could query to get the list of local macro variables in the current macro. And if you do that early enough (before creating any local macro variables other than the parameters), in effect you get the parameters.


Sas macro if then else based on input parameter, Beyond IF THEN ELSE: Techniques for Conditional Execution of SAS® Code macro statement. The ability to take different actions based on different inputs is essential in order to complete complex tasks. IF…THEN…ELSE logic. Both functions accept the same four arguments: a logical expression to be tested, a value  Macro parameters enable you to pass values into the macro at macro invocation, and set default values for macro variables within the macro definition. In this blog post, I also discuss how you can pass in a varying number of parameter values.


[PDF] Beyond IF THEN ELSE: Conditional Execution of SAS , %IF expression %THEN %DO;: text and/or macro language statements; %END; You can control text that is copied to the input stack with the %IF-%THEN while In this example, the value of the macro variable status determines which %​macro choice(status); data fees; set sasuser.all; %if &status=PAID %then %do;  1 Paper 326-2017 Beyond IF THEN ELSE: Techniques for Conditional Execution of SAS® Code Joshua M. Horstman, Nested Loop Consulting, Indianapolis, IN


Creating and Using Macro Programs - 39 of 61, An if-then statement can be used to create a new variable for a selected subset of the observations. SAS evaluates the expression in the else-if statement only when the previous expression is false. else-if statements are input name $ gpa;​. When the value of the macro variable TAXRATE is CHANGE, then the macro generates the following DATA step: DATA THISYEAR; SET LASTYEAR; IF SALE > 100 THEN TAX = .05; ELSE TAX = .08; RUN; When the value of the macro variable TAXRATE is SAME, then the macro generates the following DATA step: DATA THISYEAR; SET LASTYEAR; TAX = .03; RUN;