Date:         Fri, 4 Jan 2008 14:25:15 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SQL merging issues
Comments: To: Edgar I Sanchez <Suppression@mail.utexas.edu>
In-Reply-To:  <000001c84ef9$e10714a0$0201a8c0@phantom>
Content-Type: text/plain; charset="us-ascii"

Edgar:
I am reflecting your question back to the list as I believe that you
intended. Others may contribute more to this thread than I can.

In this situation I'd simple expand the number of parentheses to guide
the SAS SQL compiler:

proc sql;
        create table mergeda as
        select * from (select * from (select * from (select * from
        rep1 natural full join rep2)
                natural full join rep3)
                           natural full join rep4)
    ;
quit;

Expanding to ten subqueries will produce an ungainly program, but should
yield the result that you seek. Of course by posting a question on the
'L you risk critiques that go beyond the scope of your questions. For
example, you are creating a dataset that complicates summary in SQL or
other database programming languages. Unless you are restructuring data
to fit as input to another program, I'd look at a different structure:

Theta Bias   val
-3.25   4  -0.92870

Any number of SAS procedures using BY groups and SAS SQL grouping and
summary functions operate easily and effectively on this data structure.
You may be making life difficult by forcing data into different datasets
and trying to join them into one awkward table.
S

-----Original Message-----
From: Edgar I Sanchez [mailto:Suppression@mail.utexas.edu]
Sent: Friday, January 04, 2008 12:47 PM
To: Sigurd Hermansen
Subject: RE: SQL merging issues


Sigurd,

        I appreciate your help with my problem. I tried applying your
code to my real datasets and it worked when I was joining three datasets
but when I try to join 4 or more (I have ten in all I need to join) I
have the original problem. I guess ill have to go ahead with ten
separate sql commands to be sure I have the right dataset. Im working on
my dissertation so I need to be sure this code will work properly. I'd
really appreciate your (or anyone elses) thoughts on this. Below I have
included another illustration using the first 4 of my real datasets so
you can see what I mean. Since these are my real datasets it a bit
longer (hope no one minds).

data rep1;
input Theta Bias1;
cards;
-3.25    -0.92870
-2.75    -0.08525
-2.25     0.08892
-1.75     0.15921
-1.25     0.00601
-0.75    -0.00686
-0.25     0.03734
0.25    -0.01007
0.75    -0.02367
1.25    -0.02210
1.75    -0.10416
2.25    -0.00366
2.75    -0.08480
;
data rep2;
input Theta Bias2;
cards;
-3.75     0.30040
-3.25    -0.20090
-2.75    -0.14756
-2.25     0.11015
-1.75     0.00195
-1.25     0.02624
-0.75     0.03411
-0.25    -0.00027
0.25    -0.01230
0.75    -0.01940
1.25    -0.03417
1.75    -0.05949
2.25    -0.16137
2.75     0.00589
3.75     0.7176
;
data rep3;
input Theta Bias3;
cards;
-2.75    -0.31653
-2.25     0.15769
-1.75     0.05432
-1.25    -0.01802
-0.75    -0.00229
-0.25    -0.01702
0.25     0.04661
0.75    -0.00986
1.25    -0.08448
1.75    -0.04782
2.25    -0.04361
2.75     0.01480
;
data rep4;
input Theta Bias4;
cards;
-3.25     0.48485
-2.75     0.05964
-2.25    -0.01091
-1.75     0.05090
-1.25     0.04512
-0.75    -0.00396
-0.25    -0.02344
0.25    -0.03503
0.75     0.01300
1.25    -0.04843
1.75    -0.10783
2.25    -0.20341
2.75     0.14100
3.25    -0.55510
;

proc sql;
        create table mergeda as
        select * from
        (select * from rep1 natural full join rep2)
        natural full join rep3
                natural full join rep4; /* If I remove this line the
three previous dataset are merged correctly */ quit;