topic Re: Macro Error? in SAS Programming

Macro Error?

ybz12003 — Fri, 31 Jan 2020 23:01:40 GMT

Hello,

I have an error message shown in my macro coding, any idea where went wrong?

%let FormatList = A B C; 
 
%let FormatCNT=%sysfunc(countw(&FormatList));                                                                                                       
                                                                                                                                        
	%do JJ = 1 %to &FormatCNT;                                                                                                                    
	                                                                                                                                        
	%let Format=%scan(&FormatList,&JJ); 
    
	proc sql;
	     select distinct &Format. from test;
	quit;

%end;

350 %let FormatList = A B C;

351

352 %let FormatlistCNT=%sysfunc(countw(&Formatlist));

353

354 %do JJ = 1 %to &FormatlistCNT;

ERROR: The %DO statement is not valid in open code.

355

356 %let Format=%scan(&Formatlist,&JJ);

WARNING: Apparent symbolic reference JJ not resolved.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric

operand is required. The condition was: &JJ

ERROR: Argument 2 to macro function %SCAN is not a number.

357

358 proc sql;

359 select distinct &Format. from test;

-----

ERROR 79-322: Expecting a FROM.

360 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

ERROR: The %END statement is not valid in open code.

361

362 %end;

Re: Macro Error?

ballardw — Fri, 31 Jan 2020 23:20:16 GMT

You don't mention which version of SAS your using.

the %do / %end structure likely needs to be used in an actual %macro / %mend definition and then called:

%macro mymacro(formatlist =); 
%let FormatCNT=%sysfunc(countw(&FormatList));                                                                                                       
                                                                                                                                        
%do JJ = 1 %to &FormatCNT;                                                                                                                    
	%let Format=%scan(&FormatList,&JJ); 
	proc sql;
	     select distinct &Format. from test;
	quit;
%end;
%mend;
%mymacro(formatlist= A B C)

I prefer to have explicit parameters in Macros instead of letting something outside the macro just "happen" to have values used inside.

Obviously not tested as I don't have your test dataset.

Re: Macro Error?

SASKiwi — Fri, 31 Jan 2020 23:20:37 GMT

Your macro code must go inside a macro:

%let FormatList = A B C; 

%macro test;
 
%let FormatCNT=%sysfunc(countw(&FormatList));                                                                                                       
                                                                                                                                        
	%do JJ = 1 %to &FormatCNT;                                                                                                                    
	                                                                                                                                        
	%let Format=%scan(&FormatList,&JJ); 
    
	proc sql;
	     select distinct &Format. from test;
	quit;

%end;

%mend test;

Re: Macro Error?

ed_sas_member — Sat, 01 Feb 2020 08:36:05 GMT

Hi @ybz12003

The iterative %DO and %END statements are valid only inside a macro definition.

That's why you get an error.

Best,

Re: Macro Error?

ybz12003 — Sat, 01 Feb 2020 15:16:01 GMT

Thank you so much!

Re: Macro Error?

ybz12003 — Sat, 01 Feb 2020 15:17:07 GMT

Thanks for your help, SASKiwi. But your code was not working.

Re: Macro Error?

SASKiwi — Thu, 06 Feb 2020 01:07:09 GMT

@ybz12003 - Please provide more details. Post your SAS log with any notes and errors. I doubt you would give your car to a mechanic for repair and just say "its not working"