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;
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
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
How about just changing your input functions to be put functions. That is the function one would normally use in converting numeric to character.
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
ceil(age/10)*10
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.