DATA Step, Macro, Functions and more

Convert the datatype of a variable

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Convert the datatype of a variable

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


Accepted Solutions
Solution
‎06-21-2017 08:39 AM
Trusted Advisor
Posts: 1,630

Re: Convert the datatype of a variable

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)

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

Re: Convert the datatype of a variable

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.

Solution
‎06-21-2017 08:39 AM
Trusted Advisor
Posts: 1,630

Re: Convert the datatype of a variable

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)

Contributor
Posts: 32

Re: Convert the datatype of a variable

Thank you! make sense now. 

Trusted Advisor
Posts: 1,400

Re: Convert the datatype of a variable

[ Edited ]

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;

 

 

Frequent Contributor
Posts: 104

Re: Convert the datatype of a variable

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

 

________________________

- That still only counts as one -

PROC Star
Posts: 552

Re: Convert the datatype of a variable

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.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 186 views
  • 0 likes
  • 6 in conversation