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 2003, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 24 Jan 2003 17:17:38 -0500
Reply-To:     Shane Hornibrook <shane_sasl_nospam1@SHANEHORNIBROOK.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Shane Hornibrook <shane_sasl_nospam1@SHANEHORNIBROOK.COM>
Subject:      Re: PROC SQL Question
Comments: To: Garland <quandagarland@YAHOO.COM>
In-Reply-To:  <2fb667c8.0301240854.218ec442@posting.google.com>
Content-Type: TEXT/PLAIN; charset=US-ASCII

Garland,

You've gotten great responses from Sigurd Hermansen, David L. Cassell, and JP. I have a bit to add to their code. If you run some of the example code using "select * from" and joining varibles with the same name you will notice WARNINGs in the log.

Using David L. Cassell's code as the base;

proc sql noprint; create table dm as select * from ds1 as a left join ds2 as b on a.subjid=b.subjid and a.lbtst=b.lbtst; quit;

If you run the code you will get warnings similar to

"WARNING: Variable subjid already exists on file WORK.DM." "WARNING: Variable lbtst already exists on file WORK.DM."

You can avoid these warnings by adding a RENAME to one set of variables, and dropping the renamed variables on the created table. It is not a required part of the SQL join, but it keeps your log file uncluttered.

The modified code looks like;

proc sql noprint; create table dm(drop=subjid_temp lbtst_temp) as select * from ds1 as a left join ds2 (rename=(subjid=subjid_temp lbtst=lbtst_temp)) as b on a.subjid=b.subjid_temp and a.lbtst=b.lbtst_temp; quit; run;

It looks a little 'crufty', but it works, and may save you a bit of time when reviewing your log files. On the downside, your code will run perhaps 30% slower than the non-renamed-variable SQL. This performance hit is in addition to the overhead of using PROC SQL vs the comparable datastep. This may become an issue if you are joining more than 500,000 records (As written and tested on an IBM AIX RS/6000 SAS v8 .... your mileage may vary).

In this case it seems faster (in all regards) to code and run the equivalent data step.

--Shane Shane Hornibrook Mobile: (902)441-4158 shane_sasl_nospam1@shanehornibrook.com http://www.shanehornibrook.com/

On Fri, 24 Jan 2003, Garland wrote:

> Hi, > > I have the following program. Can anybody give me the PROC SQL query > for the following datastep. I am learing proc sql just started to > convert the previously written programs to proc sql as an exercise. > > Thanks in advance, > Garland > > Data ds1; > Input subjid lbtst$ lowhigh $; > Datalines; > 1001 ALBUMIN NH > 1001 BUN NL > 1002 CALCIUM NH > 1002 BUN NL > 1002 LDH NH > ; > > Data ds2; > Input subjid studydy lbtst$ ; > Datalines; > 1001 0 ALBUMIN > 1001 1 ALBUMIN > 1001 7 ALBUMIN > 1001 14 ALBUMIN > 1001 0 BUN > 1001 1 BUN > 1001 1 URIC_ACID > 1001 2 GLUCOSE > 1002 1 CALCIUM > 1002 1 BUN > 1002 0 LDH > 1003 2 SODIUM > 1003 2 LDH > ; > > run; > > proc sort data=ds1; > by subjid lbtst; > run; > > proc sort data=ds2; > by subjid lbtst; > run; > > data dm; > merge ds1(in=aa) ds2; > by subjid lbtst; > if aa; > run; >


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