Desktop productivity for business analysts and programmers

How to use a %DO loop to process across all the items in the macro list.

Reply
Occasional Contributor
Posts: 9

How to use a %DO loop to process across all the items in the macro list.

I have created a macro varible list. But I have problem when I tried to  use a %DO loop to process across all the items in the list.

%macro SalesRpt;
%local r;
proc sql noprint;
 select distinct year
 into :YEAR1 - :YEAR7
 from slm_NAVI_dflt_alL
WHERE YEAR NOT IN (2008,2016,2017);
 quit;
%let YEARCNT = &sqlobs;
%put &YEARCNT;
%do r = 1 %to &YEARCNT;

 PROC SQL;
 CREATE TABLE mir_report AS
		SELECT
			year,sum(act_balance) as sum_act format 13.2, sum(LOAN_DISB_TOT_PRIN_AMT) as sum_disb, 
			calculated sum_act/calculated sum_disb as percent format 13.4 ,mir_pi, count(*) as count
		from performance
		where year=&&YEAR&r and 1<=mir_pi<=(2017-&&YEAR&r-1)*12  
		group by year, mir_pi;
quit;
%end;
%mend salesrpt;
%salerpt

 The log shows 

WARNING: Apparent symbolic reference YEAR not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

 

Thanks!

Super User
Posts: 6,543

Re: How to use a %DO loop to process across all the items in the macro list.

Are you certain that this is the actual code?  It doesn't look like this code should cause that error.  &&YEAR&r looks like it's correct, although either of these could cause the problem you noted:

 

&YEAR&R

&&YEAR.&R

Super User
Posts: 13,084

Re: How to use a %DO loop to process across all the items in the macro list.

You may also want to consider modifying this line of code:

CREATE TABLE mir_report AS

 

to something like:

CREATE TABLE mir_report&&year&r AS

 

otherwise at the end of the loop the only output retained will be for the last year.

Occasional Contributor
Posts: 9

Re: How to use a %DO loop to process across all the items in the macro list.

Yes, I revised this problem. Thanks!

Super User
Posts: 22,874

Re: How to use a %DO loop to process across all the items in the macro list.

Use MPRINT and SYMBOLGEN and post your full log.

 

 

Occasional Contributor
Posts: 9

Re: How to use a %DO loop to process across all the items in the macro list.

I just figure out that the code is correct, except the last line... I called the wrong name.... Should be %salesrpt instead of %salerpt...

Thanks to everyone for your helpSmiley Very Happy

Ask a Question
Discussion stats
  • 5 replies
  • 139 views
  • 0 likes
  • 4 in conversation