Defining a macro and then using it in a Proc export statement

[AJChamberlain]

Hi all

I am trying to define a macro and then combine it with a Proc Edit statement.

I have a macro which in this example is called 'Filepath' which defines the file path where I want to export my file, and I want to use data from a dataset called 'mydata' to create an excel spreadsheet in the directory specified by the macro, the spreadsheet needs to be called 'Compare_RL'. My code looks something like this:

%let Filepath = \\My path name\folder;

proc export data= mydata
outfile= "&Filepath.\Compare_RL.xlsx"
dbms=xlsx replace;
run;

I am getting an error which tells me SAS is expecting an arithmetic operator.

Any ideas how to fix this?

Thanks

A

[Kurt_Bremser]

Please post the whole log, using the {i} button. From the %let to the run; of the proc export, including all NOTEs, ERRORs and WARNINGs up to the beginning of the next step (if there was one).

[AJChamberlain]

Sure

Here it is with my original macro and code from the log

thx

A

[VDD]

do your - need to be =

[AJChamberlain]

Hi VDD

I am not sure what you mean by 'do your - need to be =' my code doesn't have dashes in it. Have I misinterpreted what you meant here?

Thx

A

[Kurt_Bremser · Accepted solution]

Your problem is caused by improper usage of double quotes which causes a string to end prematurely and places the following text in the code instead of in the string.

Use this assignment instead:

string=compbl(cats("
  proc export
    data=final(where=(Version='", Version, "'))
    outfile= '&Comp_RL.\Compare_RL.xlsx'
    dbms=xlsx replace;
  sheet='", Version, "';
  run;
"));

Since the outermost quotes are double, all macro references inside will be resolved, the single quotes are just part of the string. Only when the result is put into the execution queue by call execute() will the single quotes have meaning, but then the macro variables have already been replaced by their content.

[AJChamberlain]

Worked perfectly Kurt, thanks for your help

Andy

[ballardw]

---

@AJChamberlain wrote:

Sure

Here it is with my original macro and code from the log

thx

A

---

For future reference please post code and log into a code box opened using the forum's {I} icon. Just copy from the editor or log and paste.

Placing such into a word processor file format (docx) has resulted in code changing such as programming quotes from " to curly quotes and other sometimes invisible characters getting inserted that will prevent the code from running properly as well as forcing word wrap that may make some of the diagnostics in the error messages harder to interpret. The code box does not do any reformatting.