Thanks! The proc sql code you modified doesn't seem to work on my program and I couldn't produce a list of values in macro variable "regcmb" just like what my original code did. I had no problem running my proc sql and I think the macro variable is being assigned too. So I think the problems are in the %loop for proc tabulate. I have inserted my log for Proc tabulate. 49 %MACRO REGION;
50 *add loop to the ODS Code;
51 %do I=1 %to ®cmb ;
52 /*ods excel
53 OPTIONS ( Orientation = 'landscape'
54 sheet_name = "NTIP COT Table &&E&I"
55 embedded_titles='yes'
56 embedded_footnotes='yes'
57 embed_footnotes_once='yes'
58 embed_titles_once='on'
59 tab_color='#cce5ff'); */
60 proc tabulate data=cot2 s=[just=c];
61 where eligible_cot=1 and year_confirm=2018 and Region=&&S&I;
62 class complete Region Region1 /order=formatted;
63 class Pub_Hlth_Ctr / style=[indent=2in] order=unformatted;
64 table Region*Region1='SPA'*(Pub_Hlth_Ctr=' ' all='TOTAL
64 ! (REGION)'), complete =' '*(rowpctn={label='%'
64 ! style=[textdecoration=underline]}*f=4.2
65 n={label='Num' style=[textdecoration=underline]}*f=2.)
65 ! ALL='TOTAL (HEALTH CENTER)'*(N={label='Den'
65 ! style=[textdecoration=underline]})/misstext='0';
66 run;
67 %END;
68 %mend;
69
70 %REGION;
MLOGIC(REGION): Beginning execution.
MPRINT(REGION): *add loop to the ODS Code;
SYMBOLGEN: Macro variable REGCMB resolves to 5
3 The SAS System
09:57 Thursday, January 30, 2020
MLOGIC(REGION): %DO loop beginning; index variable I; start value is 1;
stop value is 5; by value is 1.
MPRINT(REGION): proc tabulate data=cot2 s=[just=c];
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable S1 resolves to Region 1
NOTE: Line generated by the macro variable "S1".
70 Region 1
_
22
76
MPRINT(REGION): where eligible_cot=1 and year_confirm=2018 and
Region=Region 1;
ERROR: Syntax error while parsing WHERE clause.
MPRINT(REGION): class complete Region Region1 /order=formatted;
MPRINT(REGION): class Pub_Hlth_Ctr / style=[indent=2in]
order=unformatted;
MPRINT(REGION): table Region*Region1='SPA'*(Pub_Hlth_Ctr=' ' all='TOTAL
(REGION)'), complete =' '*(rowpctn={label='%'
style=[textdecoration=underline]}*f=4.2 n={label='Num'
style=[textdecoration=underline]}*f=2.) ALL='TOTAL (HEALTH
CENTER)'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
MPRINT(REGION): run;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
**, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MLOGIC(REGION): %DO loop index variable I is now 2; loop will iterate
again.
MPRINT(REGION): proc tabulate data=cot2 s=[just=c];
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable S2 resolves to Region 2
NOTE: Line generated by the macro variable "S2".
70 Region 2
_
22
76
MPRINT(REGION): where eligible_cot=1 and year_confirm=2018 and
Region=Region 2;
ERROR: Syntax error while parsing WHERE clause.
MPRINT(REGION): class complete Region Region1 /order=formatted;
MPRINT(REGION): class Pub_Hlth_Ctr / style=[indent=2in]
order=unformatted;
MPRINT(REGION): table Region*Region1='SPA'*(Pub_Hlth_Ctr=' ' all='TOTAL
(REGION)'), complete =' '*(rowpctn={label='%'
style=[textdecoration=underline]}*f=4.2 n={label='Num'
4 The SAS System
09:57 Thursday, January 30, 2020
style=[textdecoration=underline]}*f=2.) ALL='TOTAL (HEALTH
CENTER)'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
MPRINT(REGION): run;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
**, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MLOGIC(REGION): %DO loop index variable I is now 3; loop will iterate
again.
MPRINT(REGION): proc tabulate data=cot2 s=[just=c];
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 3
SYMBOLGEN: Macro variable S3 resolves to Region 3
NOTE: Line generated by the macro variable "S3".
70 Region 3
_
22
76
MPRINT(REGION): where eligible_cot=1 and year_confirm=2018 and
Region=Region 3;
ERROR: Syntax error while parsing WHERE clause.
MPRINT(REGION): class complete Region Region1 /order=formatted;
MPRINT(REGION): class Pub_Hlth_Ctr / style=[indent=2in]
order=unformatted;
MPRINT(REGION): table Region*Region1='SPA'*(Pub_Hlth_Ctr=' ' all='TOTAL
(REGION)'), complete =' '*(rowpctn={label='%'
style=[textdecoration=underline]}*f=4.2 n={label='Num'
style=[textdecoration=underline]}*f=2.) ALL='TOTAL (HEALTH
CENTER)'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
MPRINT(REGION): run;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
**, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.03 seconds
MLOGIC(REGION): %DO loop index variable I is now 4; loop will iterate
again.
MPRINT(REGION): proc tabulate data=cot2 s=[just=c];
5 The SAS System
09:57 Thursday, January 30, 2020
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 4
SYMBOLGEN: Macro variable S4 resolves to Region 4
NOTE: Line generated by the macro variable "S4".
70 Region 4
_
22
76
MPRINT(REGION): where eligible_cot=1 and year_confirm=2018 and
Region=Region 4;
ERROR: Syntax error while parsing WHERE clause.
MPRINT(REGION): class complete Region Region1 /order=formatted;
MPRINT(REGION): class Pub_Hlth_Ctr / style=[indent=2in]
order=unformatted;
MPRINT(REGION): table Region*Region1='SPA'*(Pub_Hlth_Ctr=' ' all='TOTAL
(REGION)'), complete =' '*(rowpctn={label='%'
style=[textdecoration=underline]}*f=4.2 n={label='Num'
style=[textdecoration=underline]}*f=2.) ALL='TOTAL (HEALTH
CENTER)'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
MPRINT(REGION): run;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
**, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MLOGIC(REGION): %DO loop index variable I is now 5; loop will iterate
again.
MPRINT(REGION): proc tabulate data=cot2 s=[just=c];
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 5
SYMBOLGEN: Macro variable S5 resolves to Region 5
NOTE: Line generated by the macro variable "S5".
70 Region 5
_
22
76
MPRINT(REGION): where eligible_cot=1 and year_confirm=2018 and
Region=Region 5;
ERROR: Syntax error while parsing WHERE clause.
MPRINT(REGION): class complete Region Region1 /order=formatted;
MPRINT(REGION): class Pub_Hlth_Ctr / style=[indent=2in]
order=unformatted;
MPRINT(REGION): table Region*Region1='SPA'*(Pub_Hlth_Ctr=' ' all='TOTAL
(REGION)'), complete =' '*(rowpctn={label='%'
style=[textdecoration=underline]}*f=4.2 n={label='Num'
style=[textdecoration=underline]}*f=2.) ALL='TOTAL (HEALTH
CENTER)'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
MPRINT(REGION): run;
6 The SAS System
09:57 Thursday, January 30, 2020
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
**, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT,
NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE TABULATE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MLOGIC(REGION): %DO loop index variable I is now 6; loop will not iterate
again.
MLOGIC(REGION): Ending execution.
71
72 GOPTIONS NOACCESSIBLE;
73 %LET _CLIENTTASKLABEL=;
74 %LET _CLIENTPROJECTPATH=;
75 %LET _CLIENTPROJECTNAME=;
76 %LET _SASPROGRAMFILE=;
77
78 ;*';*";*/;quit;run;
79 ODS _ALL_ CLOSE;
80
81
82 QUIT; RUN;
83
... View more