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

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

zzzyyy — Wed, 30 Aug 2017 13:57:30 GMT

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!

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

Astounding — Wed, 30 Aug 2017 14:07:46 GMT

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

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

ballardw — Wed, 30 Aug 2017 14:24:46 GMT

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.

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

Reeza — Wed, 30 Aug 2017 14:38:37 GMT

Use MPRINT and SYMBOLGEN and post your full log.

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

zzzyyy — Wed, 30 Aug 2017 19:37:45 GMT

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 help

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

zzzyyy — Wed, 30 Aug 2017 19:39:06 GMT

Yes, I revised this problem. Thanks!