topic Re: Convert the datatype of a variable in SAS Programming

Convert the datatype of a variable

GunnerEP — Wed, 21 Jun 2017 12:01:49 GMT

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

Re: Convert the datatype of a variable

RW9 — Wed, 21 Jun 2017 12:19:01 GMT

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.

Re: Convert the datatype of a variable

PaigeMiller — Wed, 21 Jun 2017 12:23:12 GMT

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)

Re: Convert the datatype of a variable

Shmuel — Wed, 21 Jun 2017 12:26:52 GMT

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;

Re: Convert the datatype of a variable

PeterClemmensen — Wed, 21 Jun 2017 12:38:24 GMT

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.

Re: Convert the datatype of a variable

GunnerEP — Wed, 21 Jun 2017 12:39:37 GMT

Thank you! make sense now.

Re: Convert the datatype of a variable

Oligolas — Wed, 21 Jun 2017 12:45:43 GMT

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