SAS中文论坛

标题: sas合并问题 [打印本页]

作者: shiyiming 时间: 2010-7-15 09:35
标题: sas合并问题
各位前辈大家好，我在执行sas sql合并时一直出现问题，导致无法合并其讯息如下:
78 PROC SQL;
79 CREATE TABLE book3 AS
80 SELECT both.no, both.time, both.date, both.execution, both.buy, both.sell, both.p1, both.q1, both.p2, both.q2, both.p3,
80 ! both.q3, both.p4, both.q4, both.p5, both.q5, both.p6, both.q6, both.p7, both.q7, both.p8, both.q8, both.p9, both.q9,
80 ! both.p10, both.q10, both.p11, both.q11, book2.sn, book2.oq, book2.op, book2.type, book2.odate, book2.otime, book2.ep,
80 ! book2.eq, book2.edate, book2.etime, book2.bs, book2.or, book2.six, book2.bir
81 FROM both full join book on (both.no=book2.sn) and (both.date=book2.odate) and (both.time=book2.otime) ;
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Unresolved reference to table/correlation name book2.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
请问这是代表什么意思呢，该如何解决呢? <img src="{SMILIES_PATH}/icon_confused.gif" alt=":?" title="Confused" />  <img src="{SMILIES_PATH}/icon_confused.gif" alt=":?" title="Confused" />

目前语法为
data ep1;
infile 'e:\aaa\t200905186.txt' missover;
input a $1-2 b $3-4 c $5-6 sno 7-14 no $15-20 time 21-26 execution 27 buy 28 sell 29 d 30 e 31 f 32 g 33 p1 34-39 q1 40-47 p2 48-53 q2 54-61 p3 62-67 q3 68-75 p4 76-81 q4 82-89 p5 90-95 q5 96-103 p6 104-109 q6 110-117 p7 118-123 q7 124-131 p8 132-137 q8 138-145 p9 146-151 q9 152-159 p10 160-165 q10 166-173 p11 174-179 q11 180-187;
data ep2;
infile 'e:\aaa\t200905196.txt' missover;
input a $1-2 b $3-4 c $5-6 sno 7-14 no $15-20 time 21-26 execution 27 buy 28 sell 29 d 30 e 31 f 32 g 33 p1 34-39 q1 40-47 p2 48-53 q2 54-61 p3 62-67 q3 68-75 p4 76-81 q4 82-89 p5 90-95 q5 96-103 p6 104-109 q6 110-117 p7 118-123 q7 124-131 p8 132-137 q8 138-145 p9 146-151 q9 152-159 p10 160-165 q10 166-173 p11 174-179 q11 180-187;

data a1101;
set ep1;
if no='1101'; date= 20090518;
run;
data b1101;
set ep2;
if no='1101'; date= 20090519;
run;

data both;
set a1101  b1101;
run;
data book;
infile 'e:\abc\333.txt' missover dlm=",";
input sn $ oq op type $ odate $ otime $ eq ep edate $ etime $  bs $ or $ six $ bir $;
run;
data book2;
set book;
if sn='1101';
run;

PROC SQL;
CREATE TABLE book3 AS
SELECT both.no,  both.time, both.date, both.execution,  both.buy, both.sell, both.p1,  both.q1,  both.p2,  both.q2,  both.p3,  both.q3,  both.p4,  both.q4,  both.p5,  both.q5,  both.p6,  both.q6, both.p7,  both.q7,  both.p8,  both.q8,  both.p9,  both.q9,  both.p10,  both.q10,  both.p11,  both.q11, book2.sn,  book2.oq, book2.op, book2.type,  book2.odate,  book2.otime,  book2.ep, book2.eq, book2.edate,  book2.etime, book2.bs,  book2.or,  book2.six,  book2.bir
FROM both full join book on (both.no=book2.sn) and (both.date=book2.odate) and (both.time=book2.otime)  ;
QUIT;

data book4;
set book3;
run;

作者: shiyiming 时间: 2010-7-15 10:49
标题: Re: sas合并问题
首先,错误信息说等号两边变量的类型不同
[quote:1yer6xvk]Expression using equals (=) has components that are of different data types.[/quote:1yer6xvk]
其次,找SQL语句,连接条件涉及both表和book2表的3个变量
[quote:1yer6xvk]FROM both full join book on (both.no=book2.sn) and ([color=#FF0000:1yer6xvk]both.date=book2.odate[/color:1yer6xvk]) and (both.time=book2.otime) ;[/quote:1yer6xvk]
再次,看这3变量是不是相同类型的.both来自a1101/b1101,data是numeric的,book2来自book,odate是character的
[quote:1yer6xvk]data a1101;
set ep1;
if no='1101'; [color=#FF0000:1yer6xvk]date= 20090518;[/color:1yer6xvk]
run;
data b1101;
set ep2;
if no='1101'; [color=#FF0000:1yer6xvk]date= 20090519;[/color:1yer6xvk]
run; [/quote:1yer6xvk]
[quote:1yer6xvk]data book;
infile 'e:\abc\333.txt' missover dlm=",";
input sn $ oq op type $ [color=#FF0000:1yer6xvk]odate $[/color:1yer6xvk] otime $ eq ep edate $ etime $ bs $ or $ six $ bir $;
run; [/quote:1yer6xvk]

作者: shiyiming 时间: 2010-7-15 20:46
标题: Re: sas合并问题
谢谢高手的帮助，终于合并成功了，果然是格式的问题!!

欢迎光临 SAS中文论坛 (https://mysas.net/forum/)