Behaviour difference Datastep vs Proc SQL Input function

[DaveShea]

Hi,

Using SAS 9.2 under Windows XP Pro.

I was hoping to use a user-defined Informat (numeric) to act as a look-up to avoid what will be a grim Proc SQL join. I notice that when I use a Datastep and offer a numeric variable to an Input function, SAS does an on-the-fly numeric to character conversion so that the Input does not complain about being offered a numeric value. The same does not seem to be so for Proc SQL, which, I appreciate needs to adhere to standards outside of the SAS world.

Aside from coding a nested Put(<numeric variable>, BEST12.) as the first argument to the Input function, does anyone know a way around this without changing the variables to character permanantly?

For interest, I have included some code showing this behaviour below.

Any thoughts would be welcome.

DownUnderDave.

***********************************************************************;
*Create an Informat to convert a Student Age into an Upper Age Band    ;
***********************************************************************;
Proc Format;
InValue Age_Band
  7  = 10
  8  = 10
  9  = 10
  10 = 10
  13 = 20
  28 = 30
  ;

Run;

***********************************************************************;
*Create a sample dataset.                                              ;
***********************************************************************;
Data Test;
Student_Age=13;
Run;

***********************************************************************;
*Create Student_Age_Band from Student_Age (Numeric) via our Informat   ;
*SAS does OK, converting Student_Age momentarily for the Input function;
***********************************************************************;
Data Test1;
Set Test;
Student_Age_Band=InPut(Student_Age, Age_Band.);
Run;

***********************************************************************;
*Try the same thing via Proc SQL                                       ;
*Proc SQL complains that INPUT wants a character value as argument 1.  ;
***********************************************************************;
Proc SQL;
Create Table Test2 As
Select Student_Age,
     Input(Student_Age, Age_Band.) As Student_Age_Band
From Test
;
Quit;

[Keith · Accepted solution]

Hi Dave,

I came across this exact same problem only a few weeks ago.  With the datastep you can see from the note in the log that SAS is automatically converting the number (in your case Student_Age) to a character before applying the INPUT function. With the PROC SQL code, I couldn't find any way around the problem except the one you mentioned, namely INPUT(PUT(etc...)).  I've had a few occassions where I've wanted to convert one number to another, using an informat, it seems a bit silly to have to use 2 functions to do this.

Sorry I can't help more.

Regards,

Keith

[art297]

How about just changing your input functions to be put functions.  That is the function one would normally use in converting numeric to character.

[DaveShea]

Hi Keith and Art,

Thank you for your respective replies. In my actual problem I need the output variable to be numeric and, so far as I know, a PUT function will always return a character value, so even if I used a PUT in place of an INPUT, I would still be left with converting the result back into numeric.

I think I will consider this problem closed.

Cheers,

DownUnderDave

[data_null__]

ceil(age/10)*10

[PGStats]

And if data_null_'s solution is too simple :

/* To convert an integer into another one, in any arbitrary manner, simply make
a translation table such as */

data age_band;
input age band @@;
datalines;
7  10 8 10 9 10 10 10
13 20
28 30
;

Data Test;
do Age=13, 19; output; end;
Run;

/* and then join the table within your query, Use a left join to cover the cases absent
from your translation table */

proc sql;
create table test2 as
select t.age, a.band
from test as t left join age_band as a on t.age=a.age;

Cheers!

PG