BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GunnerEP
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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)

--
Paige Miller

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26

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)

--
Paige Miller
GunnerEP
Obsidian | Level 7

Thank you! make sense now. 

Shmuel
Garnet | Level 18

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
Barite | Level 11

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

 

________________________

- Cheers -

PeterClemmensen
Tourmaline | Level 20

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.

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
  • 6 replies
  • 30316 views
  • 0 likes
  • 6 in conversation