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.
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.
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.