Why does the WHERE clause choke on the INPUT function?

A student brought in this coding problem after her manager was struggling with this issue for a while. They played guessing games, but to no avail. Here’s what happened when they submitted data step and proc sql code using a WHERE clause with an INPUT function?

 data aileen;
length hcn $10.;
input prov $ hcn $;
datalines;
BC 9999999698 
AB 612345800 
99 1 
CA V79999915 
QC NIGS999996 
ON 0 
ON 9876543210 
;
run;
 
 
NOTE: The data set WORK.AILEEN has 7 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.09 seconds
 
 
data dswarn;
set aileen;
where input(hcn,10.)>=1000000000; 
run;
 
WARNING: INPUT function reported 'WARNING: Illegal first argument to function' while processing
         WHERE clause.
WARNING: INPUT function reported 'WARNING: Illegal first argument to function' while processing
         WHERE clause.
NOTE: There were 2 observations read from the data set WORK.AILEEN.
      WHERE INPUT(hcn, 10.)>=1000000000;
NOTE: The data set WORK.DSWARN has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
 
 
proc sql;
create table sqlwarn as
select * from aileen
where input(hcn,10.)>=1000000000;
quit;
 
WARNING: INPUT function reported 'WARNING: Illegal first argument to function' while processing
         WHERE clause.
WARNING: INPUT function reported 'WARNING: Illegal first argument to function' while processing
         WHERE clause.
NOTE: Table WORK.SQLWARN created, with 2 rows and 2 columns.
 
172  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Their take:

The data step and sql procedure both generated this warning twice. (I think it is the 2 HCNs with leading characters that generated the warnings)

The Solution:

They are on the right track. The WHERE clause errors out when it finds an invalid value being passed through the INPUT function. Two records have leading character data and are being passed into the INPUT function to get converted to a numeric value. The INPUT function fails to convert the leading character data to numeric & hence the error. I fixed it by using the “?” format modifier on the INPUT or PUT function. This time SAS doesn’t articulate the choke on the INPUT function resulting in a clean log. The output dataset results are still the same. The 2 leading character observations will be stored as a missing value. The big takeaway? Using the “?” format modifier just ensures a clean log.

data warn;
set aileen;
where input(hcn,?10.)>=1000000000; 
run;
 
 
NOTE: There were 2 observations read from the data set WORK.AILEEN.
      WHERE INPUT(hcn, 10., '?')>=1000000000;
NOTE: The data set WORK.DSWARN has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.06 seconds
 
 
proc sql;
create table sqlwarn as
select * from aileen
where input(hcn,?10.)>=1000000000;
quit;
 
NOTE: Table WORK.SQLWARN created, with 2 rows and 2 columns.
 
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Resources:

I’d love to claim I came up with the solution. However, it was the encyclopedic support.sas.com to the rescue. Here’s where I learned about this error and how to fix it.

Isn’t that pretty amazing? I’m always pleasantly surprised by how much knowledge is available on support.sas.com and it’s all free!!

4 Comments

Prashant Chegoor on September 26, 2014 4:21 pm

Also one other situation the ? modifier is useful is as shown below

Data Sample;
x= -100;
Run;

Data Test1;
Set Sample;
Var=Put(x,z3.);
Run;

Proc Sql;
Create Table Test2 as
Select x,Put(x, ? z3.) as Var
from Sample;
Quit;

If the above Code is run and the SAS log is examined , there is No Error produced by the Second Datastep in the above code but just the following NOTE , which is expected as the width of format z3. format is small.

NOTE : At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

Also the value of the Variabe Var is not null .

Now if you look at the PROC SQL step in the log there is an ERROR plus the NOTE,

ERROR: Invalid value for width specified - width out of range
NOTE : At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.

The value of the Variable Var is Null in this case.

To avoid the ERROR we could use the ? modifier.

Proc Sql;
Create Table Test2 as
Select x,Put(x, ? z3.) as Var
from Sample;
Quit;

Now Just the NOTE gets printed in the Log and again the variable Var is Null.
- charu on October 8, 2014 9:43 am
  
  thanks Prashant for the handy tip.. its great to hear other SAS users like yourself weigh in with unique ways to use SAS.. keep them coming!!
Rick Wicklin on September 26, 2014 9:07 am
Wow, I never knew about the ? operator. Great tip!

In spite of the title, I don't think this behavior has anything to do with the WHERE clause. It is the INPUT statement that is giving the warning, as shown by the following example which uses a subsetting IF statement:
data warn; set aileen; y = input(hcn,10.); if y>=1000000000; run;
I think a better title for this article is "How to suppress warnings from the INPUT function when it encounters bad data." In spite of this minor complaint, thanks for the useful tip. I always learn something new from Charu!
- charu on October 8, 2014 9:41 am
  
  thanks for the handy title tip Rick! it was such a challenge coming up with a catchy name that would do justice to the content. I do like yours..and a special treat to hear a SAS expert like you say you learnt something new from my blog. Made my day:)

Blogs

Blogs

Help! Why does the WHERE clause choke on the INPUT function?

About Author

4 Comments