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
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)
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.
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)
Thank you! make sense now.
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;
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 -
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.