Macro Error?

[ybz12003]

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.

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;

-----

79

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;

[ballardw · Accepted solution]

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.

[ybz12003]

Thank you so much!

[SASKiwi]

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;

[ybz12003]

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

[SASKiwi]

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

[ed_sas_member]

Hi @ybz12003 

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

That's why you get an error.

Best,