topic Re: Defining a macro and then using it in a Proc export statement in SAS Programming

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

AJChamberlain — Wed, 12 Dec 2018 12:26:38 GMT

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

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

Kurt_Bremser — Wed, 12 Dec 2018 12:33:13 GMT

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

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

AJChamberlain — Wed, 12 Dec 2018 13:04:28 GMT

Sure

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

thx

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

VDD — Wed, 12 Dec 2018 13:13:04 GMT

do your - need to be =

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

AJChamberlain — Wed, 12 Dec 2018 13:18:30 GMT

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

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

Kurt_Bremser — Wed, 12 Dec 2018 13:27:47 GMT

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.

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

AJChamberlain — Wed, 12 Dec 2018 13:45:10 GMT

Worked perfectly Kurt, thanks for your help

Andy

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

ballardw — Wed, 12 Dec 2018 16:22:05 GMT

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