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

4

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!!

Share

About Author

Charu Shankar

Technical Training Specialist

Charu Shankar has been a Technical Training Specialist with SAS since 2007. She started as a programmer, and has taught computer languages, business and English Language skills. At SAS, Charu teaches the SAS language, SQL, SAS Enterprise guide and Business Intelligence. She interviews clients to recommend the right SAS training to help them meet their needs. She is helping build a center for special needs kids in this project. http://www.handicareintl.org/pankaja/pankaja.swf

4 Comments

  1. Prashant Chegoor on

    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 Shankar

      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!!

  2. Rick Wicklin

    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 Shankar

      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:)

Leave A Reply

Back to Top