Search code examples
sasquoting

SAS: Preserve leading space in macro variable resolution

I am using the SAS DDE functionality to call Excel commands. The command I'm using takes in the worksheet name. Unfortunately, the worksheet name I am working with contains a preceding space. Spaces can usually be handled with %str( ). In this case, the quoting gets tricky: 

%let sheet = %str( )Sheet X;
filename cmds DDE 'excel|system';

data _null_;
  file cmds;
  put '[WORKBOOK.SELECT("&sheet.")]';
run;

This causes an error with the DDE session. The sheet name is not resolving as intended. I know this because the command works when the sheet is given explicitly:

put '[WORKBOOK.SELECT(" Sheet X")]';

How can I write the put statement such that %let sheet = %str( )Sheet X; resolves properly?

Solution

  • The issue isn't with the space, it's with the single quotes.

    This works fine for me:

      put "[WORKBOOK.SELECT(""&sheet."")]";