The SAS Output Delivery System and reporting techniques

Behaviour difference Datastep vs Proc SQL Input function

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Behaviour difference Datastep vs Proc SQL Input function

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;


Accepted Solutions
Solution
‎03-01-2012 07:44 AM
Regular Contributor
Posts: 151

Behaviour difference Datastep vs Proc SQL Input function

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

View solution in original post


All Replies
Solution
‎03-01-2012 07:44 AM
Regular Contributor
Posts: 151

Behaviour difference Datastep vs Proc SQL Input function

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

PROC Star
Posts: 7,363

Behaviour difference Datastep vs Proc SQL Input function

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

Contributor
Posts: 71

Behaviour difference Datastep vs Proc SQL Input function

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

Respected Advisor
Posts: 3,777

Re: Behaviour difference Datastep vs Proc SQL Input function

ceil(age/10)*10

Respected Advisor
Posts: 4,651

Re: Behaviour difference Datastep vs Proc SQL Input function

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

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 771 views
  • 0 likes
  • 5 in conversation