Help using Base SAS procedures

ERROR: WHERE clause operator requires compatible variables.

Reply
New Contributor
Posts: 3

ERROR: WHERE clause operator requires compatible variables.

Data ran perfectly just the other day, now I am receiving this error. Not very well versed in SAS 9.4 , just using it for a summe project.

Anyways, the log description looks like this, down below.. It used to produce a EWMA chart with upper and lower limits. Not sure how to fix this problem, I did try the "proc contents data" code but was unable to decipher what I was really looking at. Any help would be greatly appreciated!

Thanks.

 

 title 'EWMA chart for farrowing rate per week, Farm_ID=CRC';
 ods graphics on;
 symbol v=dot;
 proc MACONTROL data=work.a limits=c;
 where (Farm_Name="RVL") &
 (weeks ge 1) & (interventions="MLV1")
 /* & (status ne 'extra')*/
 ;
ERROR: WHERE clause operator requires compatible variables.
 ewmachart Aborts * weeks/
 /* ewmachart Wean_ * week/*/
 WEIGHT = 0.4
 meansymbol = square
 /* lcllabel = 'LCL for EWMA' ucllabel = 'UCL for EWMA'*/
 vreflabpos = 3 Asymptotic
 outhistory = outEWMA;
 /*label status = "Status infeccao"*/
 /* week = "weeks post infection";*/

 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.OUTEWMA may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.OUTEWMA was not replaced because this step was stopped.

Super User
Posts: 5,085

Re: ERROR: WHERE clause operator requires compatible variables.

Somehow, the structure of the data set A is different this time, compared to last time.  The major things to check will probably violate at least one of these requirements:

 

Farm_Name should be a character variable

 

Weeks should be a numeric variable

 

Interventions should be a character variable

 

PROC CONTENTS, despite being difficult to decipher, should tell you that much pretty clearly.

 

A secondary possibility:  the spellings of the variable names changed slightly after the first working run.

New Contributor
Posts: 3

Re: ERROR: WHERE clause operator requires compatible variables.

Thanks for the speedy reply! So according to the list of variables and attributes produced by the "proc contents data", Farm_name is a character, weeks is a numeric value, and interventions is a numeric value, not a character like you said it should be. So my next question is, how do I change this? In original excel doc? Thanks.

Super User
Posts: 5,085

Re: ERROR: WHERE clause operator requires compatible variables.

You don't have to change the data.  It's probably simpler to change the program.  Here's the part that needs to change:

 

(interventions="MLV1")

 

You have to look at your data, and understand which value of interventions should be used to select observations.  Then change the program.  For example:

 

(interventions=3)

 

Lose the quotes and specify the value that belongs there.  if there is more than one value needed, use (for example):

 

(interventions in (1, 3, 5))

New Contributor
Posts: 3

Re: ERROR: WHERE clause operator requires compatible variables.

I tried two solutions and I'll give you the outcome to see what you think. I appreciate you helping by the way.

First, I ditched the quotations around MLV1. Ran the code and got this result:

 

title 'EWMA chart for farrowing rate per week, Farm_ID=CRC';
graphics on;
symbol v=dot;
proc MACONTROL data=work.a limits=c;
where (Farm_Name="RVL") &
(weeks ge 1) & (interventions=MLV1)
/* & (status ne 'extra')*/

 ;
ERROR: Variable MLV1 is not on file WORK.A.
 ewmachart Aborts * weeks/
 /* ewmachart Wean_ * week/*/
 WEIGHT = 0.4
 meansymbol = square
 /* lcllabel = 'LCL for EWMA' ucllabel = 'UCL for EWMA'*/
 vreflabpos = 3 Asymptotic
 outhistory = outEWMA;
 /*label status = "Status infeccao"*/
 /* week = "weeks post infection";*/
 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.OUTEWMA may be incomplete. When this step was stopped there were 0
observations and 0 variables.
WARNING: Data set WORK.OUTEWMA was not replaced because this step was stopped.

 

Then I tried changing it to interventions=1, and got this result:

 

title 'EWMA chart for farrowing rate per week, Farm_ID=CRC';
ods graphics on;
symbol v=dot;
proc MACONTROL data=work.a limits=c;
where (Farm_Name="RVL") &
(weeks ge 1) & (interventions=1)
 /* & (status ne 'extra')*/
 ;
 ewmachart Aborts * weeks/
 /* ewmachart Wean_ * week/*/
 WEIGHT = 0.4
 meansymbol = square
 /* lcllabel = 'LCL for EWMA' ucllabel = 'UCL for EWMA'*/
 vreflabpos = 3 Asymptotic
 outhistory = outEWMA;
 /*label status = "Status infeccao"*/
 /* week = "weeks post infection";*/
 run;

NOTE: No observations were selected from data set WORK.A.
NOTE: There were 0 observations read from the data set WORK.A.
WHERE (Farm_Name='RVL') and (weeks>=1) and (interventions=1);
NOTE: The data set WORK.OUTEWMA has 0 observations and 0 variables.
WARNING: Data set WORK.OUTEWMA was not replaced because new file is incomplete.

 

Hopefully this helps, not sure what it all really means.

Super User
Posts: 5,085

Re: ERROR: WHERE clause operator requires compatible variables.

You're moving in the right direction.  Removing the quotes from MLV1 had no chance of working, but switching to interventions=1 was closer.

 

You have to actually look at your data set.  What values does INTERVENTIONS take on?  Which is the right value (or set of values) to be used in your analysis?  That's a step you have to take on your end, to understand what is in your data. 

 

Once you have the answers, changing the program to match will be relatively easy.  The WHERE statement is subsetting the rows, and  you need to know which subset to include.

Super User
Posts: 10,516

Re: ERROR: WHERE clause operator requires compatible variables.


sam19b wrote:

Thanks for the speedy reply! So according to the list of variables and attributes produced by the "proc contents data", Farm_name is a character, weeks is a numeric value, and interventions is a numeric value, not a character like you said it should be. So my next question is, how do I change this? In original excel doc? Thanks.


I am assuming since you mention Excel that you are getting the data somewhat directly from Excel either with Proc Import or possibly a Libname statement. Any read from Excel is subject to changing variable types based on content of the first few records by default. So be prepared to make similar adjustments with each file.

 

If you expect to be doing this multiple times in the future you may consider 1) saving the Excel to CSV files and 2) writing a data step to read the files with the same Variable names, types and specifications. I suspect a search on this forum will turn up multiple approaches to this including using Proc Import one time to get the core of the program created by SAS.

 

If you are going to combine any of these datasets you will need to ensure that the variable types match before the combine AND should make sure that the lengths are the same to prevent potential truncation of variable values.

Ask a Question
Discussion stats
  • 6 replies
  • 200 views
  • 3 likes
  • 3 in conversation