Make the INPUT function behave the same way in the DATA step and PROC SQL

[DaveShea]

I don't know why the INPUT function behaves differently in the DATA step compared to PROC SQL, but it does. I know I can work around it, but I don't think we should need to create a fudge.

I think PROC SQL should be changed to handle the INPUT function in the same way as the DATA step.

The following code hopefully shows what I mean. The expression in the DATA step performs perfectly, but PROC SQL does not like the same expression and throws and ERROR.

**************************************************************;
*Create a Numeric Informat.                                   ;
**************************************************************;
Proc Format Library=WORK;
 InValue PartNumber 1      =8526961 
                    2,5,18 =6926656 
                    3      =3181908 
                    Other  =-9999999
                    ;
Run;

**************************************************************;
*Create the list of items ordered.                            ;
**************************************************************;
Data ORDERS;
 Input  OrderID
        ItemOrdered 
        ;

DATALINES;
555 3
847 1
;
Run;

**************************************************************;
*Use a DATA step to test out our Informat.                    ;
**************************************************************;
Data ORDERS_DATASTEP;
 Set ORDERS;

 Part_To_Pick=Input(ItemOrdered, PartNumber.);
Run;

**************************************************************;
*Use PROC SQL to test out our Informat.                       ;
**************************************************************;
Proc SQL;
 Create Table ORDERS_PROCSQL As
 Select OrderID,
        ItemOrdered,
        Input(ItemOrdered, PartNumber.) As Part_To_Pick
 From   ORDERS
;
Quit;

When the PROC SQL step runs, we get the message:

ERROR: INPUT function requires a character argument.

To get the expression with the INPUT function to work in PROC SQL it could be coerced into a character value with a nested PUT function, but I think PROC SQL should be amended so that we do not have to do this extra step.

Input(Put(ItemOrdered, 12.), PartNumber.) As Part_To_Pick

Cheers,

Downunder Dave
New Zealand

[Kurt_Bremser]

@DaveShea  What you propose should be named differently: what you actually want is an option for PROC SQL that causes it to do automatic typecasts, similar to the data step.

options sql_autocast=yes;

But keep in mind that this would cause a major rework of the SQL procedure (and other places where SQL is used, like the WHERE=dataset option or the WHERE statement).

While I am tempted to support such an idea, I seriously doubt that it would ever be implemented.