Need help with running this macro code

[ph8252]

proc sort data=cot2;
by Region Region1 year_confirm ln fn;
run;

proc sql;
create table Regional_COT as 
select distinct Region
from cot2
where Region is not null
;
quit;

data _null_;
	set Regional_COT end=last;
	if last then call symput("regcmb", compress(put(_n_,12.)));
run;
%put &regcmb;
proc sql noprint;
	select distinct Region
	into :S1 - :S&regcmb
	from Regional_COT
;
quit;

%MACRO REGION;
proc tabulate data=cot2  s=[just=c];
	where eligible_cot=1 & year_confirm=2018 & Region=&&S&I;
	class complete Region Region1 /order=formatted;
	class Pub / style=[indent=2in] order=unformatted;
table Region*Region1*(Pub=' ' all='TOTAL (REGION)'),  complete =' '*(rowpctn={label='%' style=[textdecoration=underline]}*f=4.2
n={label='Num' style=[textdecoration=underline]}*f=2.) ALL='TOTAL'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
run;
%END;
%mend;

%REGION;

I am a beginner SAS user and just start learning macro language. I am trying to run this macro code but got the following error messages after %REGION. Could someone point out the problems I have with my code? Much appreciate!

ERROR: Syntax error while parsing WHERE clause.
NOTE: Line generated by the macro variable "S1".
49 Region 1
_
22
76

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

[PaigeMiller]

When you encounter problems running a macro, you need to do the following.

Make the first line of your program

options mprint symbolgen mlogic;

and run it again.

Then show us the entire LOG for your PROC TABULATE ... the ENTIRE LOG for PROC TABULATE ... do not just show us the error messages. And when you show us the log, you need to copy as text this entire PROC TABULATE log, and then paste it into the window that appears when you click on the {i} icon.  Both of these are necessary so we can see the log as you see it, formatted properly and in its entirety. Please help us help you by following these instructions. I cannot emphasize this enough, the importance of showing us the log properly.

[fdsaaaa]

looks like you are using & to mean AND in some places, and also you have an %end without a %do . 

heres some untested code which should get you closer:

proc sort data=cot2;
by Region Region1 year_confirm ln fn;
run;

proc sql;
create table Regional_COT as 
select distinct Region
from cot2
where Region is not null
;
quit;

data _null_;
	set Regional_COT end=last;
	if last then call symput("regcmb", compress(put(_n_,12.)));
run;
%put &regcmb;
proc sql noprint;
	select distinct Region
	into :S1 - :S&regcmb
	from Regional_COT
;
quit;

%MACRO REGION;

%do I = 1 %to &regcmb ; 

proc tabulate data=cot2  s=[just=c];
	where eligible_cot=1 and year_confirm=2018 and Region=&&S&I;
	class complete Region Region1 /order=formatted;
	class Pub / style=[indent=2in] order=unformatted;
table Region*Region1*(Pub=' ' all='TOTAL (REGION)'),  complete =' '*(rowpctn={label='%' style=[textdecoration=underline]}*f=4.2
n={label='Num' style=[textdecoration=underline]}*f=2.) ALL='TOTAL'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
run;
%END;
%mend;

%REGION;

[ballardw]

This code is inefficient

data _null_;
	set Regional_COT end=last;
	if last then call symput("regcmb", compress(put(_n_,12.)));
run;
%put &regcmb;
proc sql noprint;
	select distinct Region
	into :S1 - :S&regcmb
	from Regional_COT
;
quit;

as you can get the count at the same time you generate the macro counter value at the same time you generate the macro variables:

Consider:

data example;
   input x $;
datalines;
1
3
1
2
1
Z
;

proc sql noprint;;
   select distinct x into :mys1 -:mys9999
   from example;
quit;
%let myscount=&sqlobs.;

%put _user_;

The macro variables MYS1 MYS2 … will only be created for the values to create them. In this case 4.

The SQLOBS is an automatic variable created anytime you run a proc SQL step that indicates how may records were selected, 4 in this case. You want to grab it immediately after the Proc step before it gets reset.

The %put _user_ will show the list of all currently assigned user created macro variables and the values just so you can see them.

I suspect that as far as creating the macro count driver and the macro variables the proc sort, data step and two proc sql steps can be replaced with:

proc sql noprint;
	select distinct Region
	into :S1 - :S9999
	from cot2
   where region is not null
   order by region
;
quit;
%let regcmb=&sqlobs;   

And you need the %do loop around the proc tabulate.

OR skip that entirely and look at the result for :

proc tabulate data=cot2  s=[just=c];
	where eligible_cot=1 & year_confirm=2018;
	class complete Region Region1 /order=formatted;
	class Pub / style=[indent=2in] order=unformatted;
table Region,
      Region1*(Pub=' ' all='TOTAL (REGION)'),  
      complete =' '*(rowpctn={label='%' style=[textdecoration=underline]}*f=4.2
      n={label='Num' style=[textdecoration=underline]}*f=2.) ALL='TOTAL'*(N={label='Den' style=[textdecoration=underline]})/misstext='0';
run;

Which makes Region a PAGE dimension. Which will create one table per value of region with a table heading of "Region=<some value>"

[ph8252]

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 &regcmb ;
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         

[Tom]

This line in your macro is the problem. 

Region=&&S&I

It is generating code like:

Region=Region 1

Your macro code was probably written assuming the values the REGION variable where numbers and not strings. So either add quotes into the values of the macro variables

%let s1="Region 1";

or add the quotes into the code the macro is generating.

Region="&&S&I"