Convert the datatype of a variable

[GunnerEP]

I am connecting to oracle to create a SAS work table
temp using

create table temp as
select distinct id,emp_id  from DATA_EXCL;

the id field in data_excl oracle table is varchar. So SAS is assigning this column 'char' value.

When i then merge it with my main SAS table i have the rec_id field as numeric. This is creating an error while merging.

How do i convert the datatype of a variable fetched using proc sql ?

proc contents output:

# Variable Type Len Format Informat Label

2 ID              Num 8      10.  10.   ID
1 EMP_ID    Char 15  $15.  $15. EMP_ID

[RW9]

Same as any other timme.  If you want to do it i SQL then:

select input(char_var,best.) as num_var

In dataset:

set have;

num_var=input(char_var,best.)

If your doing passthrough, then you would need to use the cast function, though thats outside the scope here.

[PaigeMiller · Accepted solution]

You can't really change the type of a variable in either Oracle or SAS. Numerics cannot be converted to character, and vice versa. (You could create a new variable that has the different type ...)

You can compare the IDs without explicitly converting, for example, something like this

id = input(emp_id,12.)

will compare the numeric ID to the character EMP_ID (but beware that a character variable can have leading zeros which would cause this comparision to fail)

[GunnerEP]

Thank you! make sense now. 

[Shmuel]

Try next code to covert the char into numeric varaible:

create table temp as
select distinct id,
          input(emp_id,best15.) as emp_id from DATA_EXCL;

[Oligolas]

Hi,

if you need to compare the char emp_id with the numeric id then you can:

-convert emp_id to num

-or convert id to char

The type change can be done directly in the ON clause:

PROC SQL;
   SELECT b.*
   FROM DATA_EXCL a
   INNER JOIN mainSASTable b
   ON strip(a.emp_id) eq strip(put(b.id,best32.))
   ;
QUIT;

Sample 24590: Convert variable values from character to numeric or from numeric to character

[PeterClemmensen]

You can not change the type of a variable once it has been created, but you can use the INPUT/PUT function to create a new variable from the original one with a different type.