BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DaveShea
Lapis Lazuli | Level 10

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Keith
Obsidian | Level 7

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

5 REPLIES 5
Keith
Obsidian | Level 7

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
Opal | Level 21

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
Lapis Lazuli | Level 10

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

PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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