Working with characters in a macro

[mgrasmussen]

Dear SAS experts

I am relatively new to SAS. I am trying to build a macro in which I generate an Excel table using ods and proc tabulate. The code written in a general way and using a sashelp dataset is as follows;

%macro table(specify=);

ods excel close;
ods excel file='pathspecification/sometext_&specify.xlsx';

proc tabulate data=sashelp.baseball missing;
class Position League;
table (all='All persons in the &specify division')
position,
League ALL/misstext='0';
where Division=&specify;
run;

ods excel close;

%mend;

%table(specify='EAST');

As shown I would like to insert 'EAST' in the title of the new excel file, in some text to the left in the table and I would also like to use it to specify a subset of the dataset for the table. It appears that I can only get the last of these three to work. My issue is probably related to the fact that it is necessary to write the macro code in a certain way within fields including ' '. I have tried a few things but I have not yet had real success with getting the code to work. 

Can anyone help?

Note that the "code itself" works when not specied within a macro.

Best regards

Martin

[PaigeMiller]

The values of a macro variable should not be in quotes. There is almost never a reason for the value of a macro variable to begin with a quote and end with a quote.

Aside from the quotes (as mentioned above) SPECIFY='EAST' is always wrong for this data set. There is no observation in SASHELP.BASEBALL where DIVISION='EAST', please look carefully.

To resolve macro variables in your SAS code, you need to include the macro variable name in double-quotes.

%macro table(specify=);

ods excel file="pathspecification/sometext_&specify..xlsx";
proc tabulate data=sashelp.baseball missing;
class Position League;
table (all="All persons in the &specify division")
position,
League ALL/misstext='0';
where Division="&specify";
run;
ods excel close;
%mend;

%table(specify=East)

Please also understand that macro variables only perform text substitution. The macro variable is replaced by its value when the code is run, and this must result in legal, working, valid SAS code, and you have not written a macro that produces legal valid working SAS code when the macro variables are replaced by their value.

[Reeza · Accepted solution]

• Macro variables only resolve in double quotes not single quotes so change your code to use double quotes.
• Note that it will also resolve with the quotation marks and you likely don't want that so I'd remove the quotation marks from your macro variable.
• The WHERE clause needs to include the double quotes.
• Periods indicate the end of a macro variable so you also need to add two periods, one for the extension and one for the end of the variable.

%macro table(specify=);

ods excel file="pathspecification/sometext_&specify..xlsx";

 

proc tabulate data=sashelp.baseball missing;
class Position League;
table (all='All persons in the &specify division')
position,
League ALL/misstext='0';
where Division="&specify";
run;

 

ods excel close;

 

%mend;

 

%table(specify=EAST);

---

@mgrasmussen wrote:

Dear SAS experts

I am relatively new to SAS. I am trying to build a macro in which I generate an Excel table using ods and proc tabulate. The code written in a general way and using a sashelp dataset is as follows;

%macro table(specify=);

ods excel close;
ods excel file='pathspecification/sometext_&specify.xlsx';

proc tabulate data=sashelp.baseball missing;
class Position League;
table (all='All persons in the &specify division')
position,
League ALL/misstext='0';
where Division=&specify;
run;

ods excel close;

%mend;

%table(specify='EAST');

As shown I would like to insert 'EAST' in the title of the new excel file, in some text to the left in the table and I would also like to use it to specify a subset of the dataset for the table. It appears that I can only get the last of these three to work. My issue is probably related to the fact that it is necessary to write the macro code in a certain way within fields including ' '. I have tried a few things but I have not yet had real success with getting the code to work. 

Can anyone help?

Note that the "code itself" works when not specied within a macro.

Best regards

Martin

---