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