- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ®cmb;
proc sql noprint;
select distinct Region
into :S1 - :S®cmb
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ®cmb;
proc sql noprint;
select distinct Region
into :S1 - :S®cmb
from Regional_COT
;
quit;
%MACRO REGION;
%do I = 1 %to ®cmb ;
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
This code is inefficient
data _null_; set Regional_COT end=last; if last then call symput("regcmb", compress(put(_n_,12.))); run; %put ®cmb; proc sql noprint; select distinct Region into :S1 - :S®cmb 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>"
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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"