Date:         Wed, 2 Apr 1997 14:52:48 -0800
Reply-To:     Lund Peter <Peter.Lund@OFM.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Lund Peter <Peter.Lund@OFM.WA.GOV>
Subject:      Re: Combining Records
Content-Type: text/plain; charset="us-ascii"

Katherine -
   Although I've posted here many times with SQL solutions, this one is
quite easy to do in a datastep.  Try something like the following
(obviously, variable lengths will have to be changed to
accommodate real life data, this code is set up to run with your sample
data):
 
data names;
  input @1 fname $8.
        @9 lname $8.
        @17 address $9.;
  cards;
Joe     Smith   1 Main St
Jody    Smith   1 Main St
Sue     Smith   1 Main St
G       Jones   2 Main St
Frank   Johnson 3 Main St
Jane    Johnson 3 Main St
;
run;
 
proc sort;
  by address lname;
run;
 
data newnames(keep=NewName Address);
  set names;
  by address lname;
 
  length NewName $26;
 
  retain Names 0 NewName;
 
  *---------------------------------------------------*;
  * if there are two names, we need to know what the  *
  * previous first name was to make "XXX and YYY", so *
  * get the value of the previous first name          *
  *---------------------------------------------------*;
 
  FirstF = lag(fname);
 
  *------------------------------------------------------------*;
  * keep track of how many names per address/lname combination *
  *------------------------------------------------------------*;
 
  Names + 1;
 
  *-----------------------------------------------------------*;
  * as mulitple records for an address/lname combination come *
  * through, just overwrite the value of NewName              *
  * for 1 name, set NewName = fname and lname                 *
  * for 2 names, set NewName = previous fname (see above lag) *
  *   and current fname and lname                             *
  * for 3 names, set NewName = The lname Family               *
  * any names past the third can be ignored because they will *
  * have the same NewName value as 3 names will               *
  *-----------------------------------------------------------*;
 
  if Names eq 1 then NewName = trim(fname)||' '||lname;
  else if Names eq 2 then
    NewName = trim(FirstF)||' and '||trim(fname)||' '||lname;
  else if Names eq 3 then
    NewName = 'The '||trim(lname)||' Family';
 
  *-------------------------------------------------------------*;
  * when through with a address/lname group, write out a record *
  * and reset the Names counter                                 *
  *-------------------------------------------------------------*;
 
  if last.lname then
    do;
      output;
      Names = 0;
    end;
run;
 
Hope this helps-
Pete Lund
WA State Office of Financial Management
peter.lund@ofm.wa.gov
 
 
----------
From:  Ricci, Katherine[SMTP:RicciK@AETNA.COM]
Sent:  Wednesday, April 02, 1997 9:46 AM
To:  Multiple recipients of list SAS-L
Subject:  Combining Records
 
Hello All,
 
I have a data file which is :
 
FNAME   LNAME   ADDRESS         etc.etc.
Joe     Smith   1 Main St
Jody    Smith   1 Main St
Sue     Smith   1 Main St
G       Jones   2 Main St
Frank   Johnson 3 Main St
Jane    Johnson3 Main St
.
.
.
 
I need to combine records by ADDRESS and LNAME to create
new records as follows:
 
If only one record LNAME with ADDRESS,
        NEWNAME=FNAME&" "&LNAME
if two records LNAME with ADDRESS,
        NEWNAME=FNAME{Record 1}&" and "&FNAME{Record 2}&" "LNAME
if more than two records LNAME with ADDRESS
        NEWNAME="THE "&LNAME&" FAMILY"
 
so my output would be:
 
NEWNAME                 ADDRESS         etc.etc.
The Smith Family        1 Main St
G Jones                 2 Main St
Frank and Jane Johnson  3 Main St
.
.
.
 
I messed with some SQL, but I'm afraid I'm no expert. Any suggestions
would be appreciated, and I will summarize.
 
Kate Ricci