Output
var1 var2 var3
a i 1
a i 2
a i 3
a j 1
a j 2
a j 3
b k 4
b k 5
c h 6作者: shiyiming 时间: 2003-10-30 18:17
proc sql;
create table output as
select a.var1,a.var2,b.var2 as var3 from a cross join b
on a.var1=b.var1
order by var1,var2,var3;
quit;作者: shiyiming 时间: 2003-10-30 19:43
小弟对SQL不熟悉，运行程序时发现有错，不知该如何？也不知有没有DATA步的程序能解决这个问题！
166 proc sql;
167 create table output as
168 select a.var1,a.var2,b.var2 as var3 from a cross join b
NOTE: SCL source line.
169 on a.var1=b.var1
--
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN,
LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.
ERROR 76-322: Syntax error, statement will be ignored.
170 order by var1,var2,var3;
171 quit;作者: shiyiming 时间: 2003-10-31 01:37
proc sql;
create table output as
select a.var1,a.var2,b.var2 as var3 from a cross join b [color=darkred:2c095][b:2c095]where [/b:2c095][/color:2c095]a.var1=b.var1
order by var1,var2,var3;
quit;作者: shiyiming 时间: 2003-10-31 09:52
感谢JERRY，问题解决了！作者: shiyiming 时间: 2003-10-31 10:08
用where？作者: shiyiming 时间: 2003-10-31 10:20
对，用where，但我也不明白为什么用它！作者: shiyiming 时间: 2003-10-31 10:26
Data a;
input var1 $ var2 $;
cards;
a i
a j
b k
c h
;run;
Data b;
input var1 $ var2;
cards;
a 1
a 2
a 3
b 4
b 5
c 6
;run;
proc sql;
create table output as
select a.var1,a.var2,b.var2 as var3 from a cross join b
on a.var1=b.var1
order by var1,var2,var3;
quit;
proc print;run;
这个程序我运行了很多遍都没有问题吗？作者: shiyiming 时间: 2003-10-31 11:24
哈哈，在SAS6.12上没问题，但SAS8.2就提示出错！作者: shiyiming 时间: 2003-10-31 12:01
还有一个小问题，运行程序后发现记录数和别人的不一样，经检查数据库是这样的，数据库a中的有些记录在b上没有，而b上的有些在a上也没有，如：
Data a;
input var1 $ var2 $;
cards;
a i
a j
b k
c h
w o
;
run;
Data b;
input var1 $ var2;
cards;
a 1
a 2
a 3
b 4
b 5
c 6
d 7
e 8
;
run;
最后要求数据库象这样子：
var1 var2 var3
a i 1
a i 2
a i 3
a j 1
a j 2
a j 3
b k 4
b k 5
c h 6
d 7
e 8
w o .
高手再帮小弟看看怎么办！谢谢作者: shiyiming 时间: 2003-10-31 12:04
上面的output 数据库中记录：
d 7
e 8
中的7和8应是var3的数据，可能这里发帖子只有一个空格所致，大家不要误会！
再次谢谢willon,jerry!作者: shiyiming 时间: 2003-10-31 13:03 标题: HOW ABOUT THIS? proc sql;
create table c as
select coalesce(a.var1,b.var1) as var1,a.var2 ,b.var2 as var3
from a
full join b on a.var1=b.var1;
quit;作者: shiyiming 时间: 2003-10-31 13:13 标题: Perfect! Thanks!!! Excellent work!作者: shiyiming 时间: 2003-11-11 02:35
proc sql;
create table output as
select coalesce(a.var1,b.var1)as var1, a.var2,b.var2 as var3
from a full join b
on a.var1=b.var1
order by var1,var2,var3;
quit;作者: shiyiming 时间: 2003-11-11 13:16 标题: hi 其实这样写更简单,效率也更高:
data temp;
merge a b;
by var1;
run;作者: shiyiming 时间: 2003-11-11 18:12
merge 和sql full join的作用原理不一样
merge不会产生笛卡尔乘积，而sql会，所以应根据不同情况选择相应的语句
merge合并时虽不产生笛卡尔乘积，但也不是一一对应的，所以用merge需要比较谨慎作者: shiyiming 时间: 2003-11-13 00:38
Result may not right when you merge many to many. Be careful !