SAS macro not resolving properly when value is numeric

[kimdukes77]

I have the following code which doesn't run correctly when I run it through once.  If resubmitting the code, it works as expected.

%global level;

%let level = 2;

%macro reports (tb=, exp=);

proc export data = &tb.
outfile = "c:\test.xlsb"
dbms = excelcs replace label;
sheet = "Table &tb.";
run;

% if &level. in (2.3) then %do;

  proc export data = data2
  outfile = "c:\test.xlsb"
  dbms = excelcs replace label;
  sheet = "Table2";
  run;

%end;

%mend reports;

%reports (tb=data1);

On the first run, macro variable Level resolves to 2 but the %if condition is evaluated as False.  Running the code through a second time in the same session resolves it to True.

Can anyone help?

[Patrick]

There are multiple issues with the code you've posted (like a blank between the % and the IF, or a dot between 2 and 3). If I just copy/past your code into my environment and then run it I'm getting a syntax error and not what you describe. Below a code example of how the macro syntax for the logic needs to look like.

options MINOPERATOR;
%global level;
%let level = 2;

%macro reports (tb=, exp=);
  %if &level. in (2 3) %then
    %do;
      %put TRUE;
    %end;
  %else %put FALSE;
%mend reports;

%reports ();

[RW9]

Hi,

Yes, there are a few things wrong here straight off.  First, you don't want to be exporting to XLSB which is a XLS file with VBA enabled - i.e. it has a binary component.  I am not sure SAS can even export to that file format.  Next, this line of code is all wrong:

% if &level. in (2.3) then %do;

First, there is a space before the if which shouldn't be there, second in needs parameters separated by comma's not period's.  Thirdly macro variables are text, and hence the in should have quoted values, e.g. ("2","3"), also note, you may need to trim the macro variable, e.g. 

%if %trim("&LEVEL.") in ("2","3") %then %do;

Note the %then rather than just then.

Perhaps it would just be easier to explain what you are trying to do, use sashelp.cars as an example file.

[Patrick · Accepted solution]

@RW9

On macro level everything is text so you don't need to quote the values.

On macro level the separator between a value list is a blank and not a comma.

[kimdukes77]

Thanks to all who have responded.  Apologies for the many errors littering my posted code.  I am now using options MINOPERATOR and have removed the comma separating the values and all is working as exepcted.

Thanks!