ERROR 180-322: Statement is not valid or it is used out of proper order

[AJ_Brien]

hello,

i'm working on a macro which will query 10 observations of a column from a table and then if the entire table results in all missing values, then it will send email to the user and abort the entire code.

this is the code that I'm using, it gives me the error: ERROR 180-322: Statement is not valid or it is used out of proper order.

I can't figure what am  I doing wrong? Appreciate the help!

%macro chck(Table,Col);
proc sql noprint;
        create table wow as select top 10 &Col. from &Table.;
	quit;

	data wow;
	set wow;
	%let flag=1;
	if not missing(&Col.) then do; 
	&flag.=0;
		stop;
	end;
	run;


	x "setenv SHELL /bin/bash";

	%if &flag.=1 %then %do;

	filename fref pipe "wapsrch %sysget(user)";

	data _null_;

		infile fref;
		input;
		mail=_infile_;
		call symputx ('mail_list',mail);
		stop;
	run;
	
	%put &=mail_list;

	filename sendnote email to = (&mail_list.)
        subject = "missing table";

	data _null_;
                file sendnote;
                abort cancel;
	run;

	%end;

%mend;

 Log:

MLOGIC(ACCESSCHECK): Beginning execution.
MLOGIC(ACCESSCHECK): Parameter TABLE has value table1
MLOGIC(ACCESSCHECK): Parameter COL has value col1
SYMBOLGEN: Macro variable COL resolves to col1
SYMBOLGEN: Macro variable TABLE resolves to table1
NOTE: Table WORK.WOW created, with 10 rows and 1 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 2.56 seconds
cpu time 0.03 seconds
MLOGIC(ACCESSCHECK): %LET (variable name is FLAG)
SYMBOLGEN: Macro variable COL resolves to COL1
SYMBOLGEN: Macro variable FLAG resolves to 1
NOTE: Line generated by the macro variable "FLAG".
851 1
-
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WOW may be incomplete. When this step was stopped there were 0 observations and 1 variables.
WARNING: Data set WORK.WOW was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

SYMBOLGEN: Macro variable FLAG resolves to 1
MLOGIC(ACCESSCHECK): %IF condition &flag.=1 is TRUE
SYMBOLGEN: Macro variable USERID resolves to ID

NOTE: The infile FREF is:
Pipe command="wapsrch ID"

NOTE: 1 record was read from the infile FREF.

[ballardw · Accepted solution]

This bit of code:

      %let flag=1;
	if not missing(&Col.) then do; 
	&flag.=0;

will generate:

	if not missing(&Col.) then do; 
	1=0;

as part of the data step. The statement 1=0; is not valid. Test it yourself. You should get something like:

1820  data junk;
1821     1=0;
         -
         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

1822  run;

NOTE: The SAS System stopped processing this step because of errors.

Note that posting the LOG entry using the forum's {I} maintains formatting and the underscore character shows under the 1, indicating the position SAS found something it didn't like.

It is generally a poor idea to place macro assignments like %let inside a data step as they only execute at compile. Are you attempting to set a macro variable to zero or a data step variable?

You use the functions Call Symputx or Call Symput to assign values to a macro variable inside a data step.

Possibly you intended:

call symputx('flag',0);

instead of the problem code.

[AJ_Brien]

you're right, just adding call symputx made it work. Thank you 🙂

[Reeza]

 create table wow as select top 10 &Col. from &Table.;

That doesn't generate an error? I didn't think SAS supported top 10 - I believe that's a T-SQL command.

[Kurt_Bremser]

It DOES generate an ERROR:

24         proc sql;
25         create table test as
26         select top 10 agge from sashelp.class;
                      __
                      22
                      200
ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: eine Zeichenkette in Hochkommata, !, !!, &, (, *, **, +, ',', -, 

The whole initial post of this thread is misleading, as the log is from executing another macro than the one defined:

%macro chck(Table,Col);

MLOGIC(ACCESSCHECK): Beginning execution.

To the OP: Please post code and log from the same program if you want reasonable answers.

[AJ_Brien]

The log is copied as-is, but I do have to change the names for confidentiality purposes, hope that's okay. seems like I missed changing it on that line. Thank you for your help 🙂

[AJ_Brien]

It works well for me without errors.

[Kurt_Bremser]

Please post the whole log of the code that works in this way. libname definition, macro definition, and macro execution.

I seriously doubt that "select top 10" works in SAS, see my previous post with proof. It might be that you ran that in an explicit passthrough, where it will work with the right DBMS.

[Kurt_Bremser]

PS that's no critique, if you got that to work, I want to know how, as I am always ready to learn (Maxim 13).

[Reeza]

Then you're not showing us your full code because that's either using SQL Pass through or something else.

 71         proc sql;
 72         select top 5 age
                       _
                       22
                       200
 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, 
               >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, 
               LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 73         from sashelp.class;

---

@AJ_Brien wrote:
It works well for me without errors.

---

[Reeza]

Then you're not showing us your full code because that's either using SQL Pass through or something else.

 71         proc sql;
 72         select top 5 age
                       _
                       22
                       200
 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, 
               >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, 
               LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 73         from sashelp.class;

---

@AJ_Brien wrote:
It works well for me without errors.

---

[AJ_Brien]

you're right, I didn't know this worked only on pass through and not on general sas datasets. I'll make sure to mention that from next time. Appreciate the feedback 🙂

[ChrisNZ]

Also, to add to the comments highlighting how far this code is from being sensible.

This:

proc sql noprint;
        create table wow as select top 10 &Col. from &Table.;
	quit;

data wow;
	set wow;
	%let flag=1;

is the same as that:

%let flag=1;
proc sql noprint;
        create table wow as select top 10 &Col. from &Table.;
	quit;

data wow;
	set wow;	

which I doubt is what you intend.

In summary:

1. You need to understand much better how macros work before using them

2. You need to check the syntax of the code you use

3. You need to check your programs with hard-coded values before attempting to introduce parameters and macros

[Patrick]

@AJ_Brien 

To give you a start here some sample code which solves the first part of your problem - determine if for a given number of rows a specific variable is always missing.

/* create sample data */
data demo_miss_all;
  set sashelp.class;
  call missing(age);
run;
data demo_miss_9;
  set sashelp.class;
  if _n_<=9 then call missing(age);
run;

/* macro definition */
%macro demo(source_table, n_rows, col);
  %global missflg;
  %let missflg=0;
  data _null_;
    set &source_table(keep=&col);
    array _cnt {1} _temporary_;
    _cnt[1]+missing(&col);
    if _n_=&n_rows then
      do;
        call symputx('missflg',_n_=_cnt[1],'g');
        stop;
      end;
  run;
  %put &=missflg;
%mend;

/* execute macro */
%demo(demo_miss_all, 10, age)
%demo(demo_miss_9, 10, age)

The code creates and populates global macro variable &missflg. The value in this variable is 0 if not all values are missing for the number of rows investigated, and is set to 1 if all values missing.

53         %demo(demo_miss_all, 10, age)

NOTE: There were 19 observations read from the data set WORK.DEMO_MISS_ALL.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MISSFLG=1
7                                                        The SAS System                         20:42 Wednesday, August 28, 2019

54         %demo(demo_miss_9, 10, age)

NOTE: There were 19 observations read from the data set WORK.DEMO_MISS_9.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MISSFLG=0 

[AJ_Brien]

Thank you, I'll use this as a reference in future. I appreciate this 🙂