LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2008, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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;


Back to: Top of message | Previous page | Main SAS-L page