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 (February 2000, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Mon, 7 Feb 2000 23:26:53 GMT
Reply-To:   sashole@mediaone.net
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Paul Dorfman <paul_dorfman@HOTMAIL.COM>
Subject:   Re: many to many match merge
Comments:   To: dward@SASHELP.COM
Comments:   cc: sashole@mediaone.net
Content-Type:   text/plain; format=flowed

David,

Let us make sure your 'no way' assertion is accurate. How about this, just off the top of my head:

data all (drop=bgr); set tablea (rename=(group=bgr)); do i=1 to nb; set tableb point=i nobs=nb; if bgr = group then output; end; run;

Or this:

data _null_; call symput('nb',left(put(nb,best.))); stop; set tableb nobs=nb; run; data all (drop=i); array p (&nb) $09 _temporary_; array g (&nb) $06 _temporary_; if _n_=1 then do i=1 to &nb; set tableb; g(i) = group; p(i) = profile; end; set tablea; do i=1 to &nb; if g(i) ne group then continue; profile = p(i); output; end; run;

Or even this:

data all (drop=i r); array g ( 0:255) _temporary_; array p (9,0:255) $2 _temporary_; if _n_=1 then do i=1 to &nb; set tableb; r = rank(substr(group,6)); g(r) ++ 1; p(g(r),r) = substr(profile,8,2); end; set tablea; r = rank(substr(group,6)); if g(r) then do i=1 to g(r); profile = 'profile' || p(i,r); output; end; run;

It appears to me that the 'no way' assertion is wrong in a general sense. The SAS DATA step is a Turing complete language in its own right; therefore, there is _nothing_ that can be coded in SQL and cannot be coded in a DATA step (but _not_ at all the other way around). Note that I mean a naked DATA step without the aid of procs of any kind, even SORT, since ultimately, the latter can be coded in a DATA step, too (whether it may be obtuse or not, depending on the circumstances, is a different issue). Sometimes DATA step solutions are even simpler in terms of coding effort, programming logic, and are less contrived; more often, SQL solutions achieve in several lines what in a DATA step would be esoteric, complex, and much less maintainable (see code above). However, if performance is the goal (and frequently, contrary to what many folks believe, it is paramount), a _properly_ coded DATA step will, depending on the task, run slightly faster, times faster, or orders of magnitude faster, than SQL at its present level of optimization.

Kind regards, ======================= Paul M. Dorfman Jacksonville, Fl =======================

David L. Ward, in part, wrote: > >There's no way to do this in a data step, only proc SQL: >proc sql; > select tablea.userid, tablea.group, tableb.profile from tablea, tableb >where tablea.group=tableb.group; >quit; > >-----Original Message----- >From: Mike Swift <mswift@PHEAA.ORG> > > Hello all. I'm new to SAS so please bear with me. I want to merge two >SAS > >tables using BY processing, in which both tables have multiple occurances >of a > >variable. I've tried using the MERGE statement, but I'm not getting the >results > >that I want. I know that this can be done with PROC TRANSPOSE, as long >as I > >know what the highest COL# will be, but this will not work because the >COL# > >could, and probably will, change nightly. I've included a brief example >below.

> >tablea tableb > >userid9 groupa profile99 groupa > >userid4 groupa profile99 groupb > >userid8 groupb profile97 groupa > >userid2 groupb profile97 groupb > > > >results(hopeful) > >userid9 groupa profile99 > >userid9 groupa profile97 > >userid4 groupa profile99 > >userid4 groupa profile97 > >userid8 groupb profile99 > >userid8 groupb profile97 > >userid2 groupb profile99 > >userid2 groupb profile97 > >

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com


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