topic Behaviour difference Datastep vs Proc SQL Input function in ODS and Base Reporting

Behaviour difference Datastep vs Proc SQL Input function

DaveShea — Thu, 01 Mar 2012 08:55:57 GMT

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;

Behaviour difference Datastep vs Proc SQL Input function

Keith — Thu, 01 Mar 2012 12:44:26 GMT

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

Behaviour difference Datastep vs Proc SQL Input function

art297 — Thu, 01 Mar 2012 13:26:42 GMT

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

Behaviour difference Datastep vs Proc SQL Input function

DaveShea — Thu, 01 Mar 2012 19:19:55 GMT

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

Re: Behaviour difference Datastep vs Proc SQL Input function

data_null__ — Thu, 01 Mar 2012 19:55:25 GMT

ceil(age/10)*10

Re: Behaviour difference Datastep vs Proc SQL Input function

PGStats — Thu, 01 Mar 2012 21:04:48 GMT

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!