SAS中关联表字段类型不同时的解决办法

项目里出了个奇葩问题，小组里建表人员把两个表要关联的字段建成了不同的类型，一个整成了数字型，一个又整成了字符串型.两个表结果如下:

create table mdlst_batch(
model_id varchar2(20) ,
batch_no varchar2(10)
);
create table mdl_inf(
id number(20),
model_code varchar2(40)
); 

要关联的两个字段是mdlst_batch.model_id与mdl_inf.id

两表数据如下:

id              model_code
12345678    AEIDASD_AFIELSD_AE
12345679    AEIDASD_AFIELSD_AE01
12345690    AEIDASD_AFIELSD_AE02

model_id        batch_no
12345678    B12341234

如果直接关联model_id与id:

proc sql;
select a.model_id,a.batch_no,b.model_code
from mdlst_batch a,mdl_inf b
where a.model_id = b.id;
quit;

执行代码会报如下错误 ：

正确处理方法:在data步中衍生一个变量，再关联

data mdl_inf;
set mdl_inf;
model_id=input(id,$20.);
run;
proc sql;
select a.model_id, a.batch_no, b.model_code
from mdlst_batch a,
mdl_inf b
where a.model_id=b.model_id;
quit;

运行结果如下:

补充：

还可以采用以下方法

方法一:

proc sql;

select a.model_id, a.batch_no, b.model_code

from mdlst_batch a,
mdl_inf b

where input(a.model_id,20.)=b.id;

quit;

需要注意的是input函数中的第一个参数要是字符型的才行

方法二:

proc sql;

select a.model_id, a.batch_no, b.model_code

from mdlst_batch a,
mdl_inf b

where a.model_id=put(b.id,20.);

quit;

注意put函数的参数b.id是数字型
并且put中的格式不能用$9.而应该用put(b.id,9.);
用$9.会报错:

 参考:SAS帮助;
Use the PUT function to convert a numeric value to a character value.The PUT function has no effect on which
formats are used in PUT statements or which formats are assigned to variables in data sets.
You cannot use the PUT function to directly change the type of variable in a data set from numeric to character.However, you can create a new character variable as the result of the PUT function.Then,if needed,use the DROP statement to drop the original numeric variable,followed by the RENAME statement to rename the new variable back to the original variable name.

The INPUT function enables you to convert the value of source by using a specified informat.The informat
 determines whether the result is numeric or character.Use INPUT to convert character values to numeric values or other character values.