topic Re: about proc with outer union in SAS Procedures

about proc with outer union

deleted_user — Sat, 17 Jul 2010 19:30:49 GMT

hi guys ,
I have question about using proc sql with outer union :
This is my code :

data a;
input x y $ ;
cards;
1 one
2 two
2 two
3 three
;
data b;
input x z $ ;
cards;
1 one
2 two
4 four

proc sql;
create table both1 as
select * from a outer union
select * from b;
quit;

According to the link :
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001361224.htm

column x would appear twice in dataset both1 , however when I run the code it only appears once. So there are 3 columns only instead of 4 columns. Message was edited by: jack050

Re: about proc with outer union

Cynthia_sas — Sat, 17 Jul 2010 19:47:09 GMT

Hi:
There is a difference between outer union and outer union corr. If I run the code on the documentation page, I get the same results as those shown in the doc.

cynthia

[pre]
data a;
infile datalines;
input x y $;
return;
datalines;
1 one
2 two
2 two
3 three
;
run;

data b;
infile datalines;
input x z $;
return;
datalines;
1 one
2 two
4 four
;
run;

ods listing;
options nocenter;
proc sql;
title '1) A OUTER UNION B';
select * from work.a
outer union
select * from work.b;
quit;

proc sql;
title '2) A OUTER UNION CORR B';
select * from work.a
outer union corresponding
select * from work.b;
quit;
[/pre]

Output (note that I DO get two X columns for OUTER UNION method -- versus only one X column for OUTER UNION CORR):
[pre]
1) A OUTER UNION B

x y x z
--------------------------------------
1 one .
2 two .
2 two .
3 three .
. 1 one
. 2 two
. 4 four

2) A OUTER UNION CORR B

x y z
----------------------------
1 one
2 two
2 two
3 three
1 one
2 two
4 four
[/pre]

Re: about proc with outer union

sbb — Sat, 17 Jul 2010 19:51:50 GMT

SAS is not going to create a new variable for you. Each SAS table/member can have only one occurence of a named column/variable.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001361224.htm#a001361227

Re: about proc with outer union

Cynthia_sas — Sat, 17 Jul 2010 21:10:34 GMT

Scott is right, I deliberately didn't mention what would happen if you used CREATE TABLE syntax with your PROC SQL query. A TABLE created from your query would net slightly different results than just the printed display from running a query. Depending on what you want to do, you might really want a join instead of a union.

cynthia

Re: about proc with outer union

deleted_user — Sun, 18 Jul 2010 03:34:02 GMT

thanks for pointing that out guys , I totally overlooked that the data set and query results are different