dolphinfish 发表于 2010-3-7 21:20
以下这个程序，按照SAS document应该是打印出四个column的：X, A, X, B. 但实际中只有3个：X, A, B.

Log 中error message:
515  on one.x=two.x;
WARNING: Variable X already exists on file WORK.MERGE.

我如果把第二个X改叫Y后就没事了。打印X, A, Y, B.  


这个程序如果换成outer union的话，也是一样的结果。看来我的SAS不接受一个table中同时存在两个X column.  但不知道为什么书上会给出XAXB形式的答案。

难道是有些版本的SAS开始不允许重名的Variable了？请问有没有人有相关经验。PDV允许有两个X存在么？谢谢！

--------------------------------------------
data one;

input X A $;

datalines;

1 a

2 b

4 d

;
run;
data two;

input X B $;

datalines;

2 x

3 y

4 v

;
run;
proc sql;
   create table work.merge as
   select *
   from one
   full join
   two
   on one.x=two.x;
run;
proc print data=merge;
   title "merge";
run;

bobguy 发表于 2010-3-7 22:14

dolphinfish 发表于 2010-3-7 21:20
以下这个程序，按照SAS document应该是打印出四个column的：X, A, X, B. 但实际中只有3个：X, A, B.

Log 中error message:
515  on one.x=two.x;
WARNING: Variable X already exists on file WORK.MERGE.

我如果把第二个X改叫Y后就没事了。打印X, A, Y, B.  


这个程序如果换成outer union的话，也是一样的结果。看来我的SAS不接受一个table中同时存在两个X column.  但不知道为什么书上会给出XAXB形式的答案。

难道是有些版本的SAS开始不允许重名的Variable了？请问有没有人有相关经验。PDV允许有两个X存在么？谢谢！

--------------------------------------------
data one;

input X A $;

datalines;

1 a

2 b

4 d

;
run;
data two;

input X B $;

datalines;

2 x

3 y

4 v

;
run;
proc sql;
   create table work.merge as
   select *
   from one
   full join
   two
   on one.x=two.x;
run;
proc print data=merge;
   title "merge";
run;

A variable name MUST be unique in any sas table/dataset in any time. This is nothing to do with SQL.

When you ask to create a table in sql with same name for two variables, it issues a warning. But if you just have no  create table statement, then there is no problem. In this case, no table is created.

You can select the same variable many times in a select statement. In this case, it is like a 'proc print'.

Add feedback option to see what the standard sql codes are. I simplify your problem by taking away irrelevant codes below.

data one;
input X A $;
datalines;
1 a
2 b
4 d
;

proc sql feedback;
  select x , a, x, 1
  from one
  ;
  create table two as
  select x , a, x
  from one
  ;
  create table two as
  select x , a, 1
  from one
  ;
quit;