BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kimdukes77
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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.

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

@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
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 849 views
  • 0 likes
  • 3 in conversation