08-30-2017 09:57 AM
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.
08-30-2017 10:07 AM
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:
08-30-2017 10:24 AM
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.
08-30-2017 03:37 PM
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